Looping through a date range
Looping through a date range
Looping through a date range
Looping through a date range
Looping through a date range
Looping through a date range Looping through a date range Looping through a date range Looping through a date range Looping through a date range Looping through a date range Looping through a date range Looping through a date range
Looping through a date range Looping through a date range
Looping through a date range
Go Back  Xtreme Visual Basic Talk > > > Looping through a date range


Reply
 
Thread Tools Display Modes
  #1  
Old 09-25-2006, 09:04 AM
Chong420 Chong420 is offline
Freshman
 
Join Date: Jan 2006
Posts: 37
Default Looping through a date range


I am trying to figure out exactly how to go about this but have no clue. I have 2 text boxes start date and end date. I need to loop through each date and retrieve some info from a databae on the dates. I can already get the info i need with one date.

But if I have the dates say for instance 9/25/2006 to 10/4/2006, how would I go about looping through that??

If i went something like:

Code:
Do until txtStartDate = txtEndDate some code loop

what do I need to do to add up, to get to the next dates and include the last date. With that statement once you get to the end date the code terminates. I hope I am some what clear, if you need any more info please ask
Reply With Quote
  #2  
Old 09-25-2006, 09:21 AM
00100b's Avatar
00100bLooping through a date range 00100b is offline
Martian In Disguise

Retired Moderator
* Guru *
 
Join Date: May 2003
Location: Minneapolis, MN
Posts: 9,566
Default

Here is one approach.
Code:
Dim dblStart As Double ' Used to capture the value of the starting date. Dim dblEnd As Double ' Used to capture the value of the ending date. Dim dblDay As Double ' Used to increment from the starting date to the ending date. ' Capture the value for the starting date. dblStart = CDate(Me.Text1.Text) ' Capture the value for the ending date. dblEnd = CDate(Me.Text2.Text) ' Increment through each date, starting with the starting date and ending with the ending date. For dblDay = dblStart To dblEnd Step 1 ' Demonstrate the incrementation. Debug.Print CDate(dblDay) Next dblDay
This works because behind the scenes, Date values are stored as 8-byte double-precision floating point values.
__________________
The only stupid question is the one that goes un-asked.
Reply With Quote
  #3  
Old 09-25-2006, 09:33 AM
Peter_Aquino's Avatar
Peter_Aquino Peter_Aquino is offline
Contributor

* Expert *
 
Join Date: Sep 2004
Location: San Diego, CA, USA
Posts: 732
Default

Is there a need to pull the data one day at a time (using a loop)? Why not just run a single query to pull all data BETWEEN StartDate and EndDate?
Code:
SELECT [Field1], [Field2]
FROM   [Table]
WHERE  [DateField] BETWEEN '09/25/2006' AND '10/04/2006'
If you want each day (in the range) to be displayed as a separate row of data, you can then add a GROUP BY clause to do so. For instance, let's say I want to see a SUM of all data in Field1 and the sum of all data in Field2, by day, for each day in the requested date range:
Code:
SELECT [DateField],SUM([Field2]), SUM([Field3]
FROM   [Table]
WHERE  [DateField] BETWEEN '09/25/2006' AND '10/04/2006'
GROUP BY [DateField]
Reply With Quote
  #4  
Old 09-25-2006, 09:36 AM
Chong420 Chong420 is offline
Freshman
 
Join Date: Jan 2006
Posts: 37
Default

works beautifully thank you soo very much I appriciate your help
Reply With Quote
  #5  
Old 09-25-2006, 09:41 AM
Chong420 Chong420 is offline
Freshman
 
Join Date: Jan 2006
Posts: 37
Default

Quote:
Originally Posted by Peter_Aquino
Is there a need to pull the data one day at a time (using a loop)? Why not just run a single query to pull all data BETWEEN StartDate and EndDate?
Code:
SELECT [Field1], [Field2]
FROM   [Table]
WHERE  [DateField] BETWEEN '09/25/2006' AND '10/04/2006'
If you want each day (in the range) to be displayed as a separate row of data, you can then add a GROUP BY clause to do so. For instance, let's say I want to see a SUM of all data in Field1 and the sum of all data in Field2, by day, for each day in the requested date range:
Code:
SELECT [DateField],SUM([Field2]), SUM([Field3]
FROM   [Table]
WHERE  [DateField] BETWEEN '09/25/2006' AND '10/04/2006'
GROUP BY [DateField]

By doing this will it sort it out something like this?

9/12/2006 - 8
9/13/2006 - 10
9/14/2006 - 26

??
Reply With Quote
  #6  
Old 09-25-2006, 09:43 AM
Peter_Aquino's Avatar
Peter_Aquino Peter_Aquino is offline
Contributor

* Expert *
 
Join Date: Sep 2004
Location: San Diego, CA, USA
Posts: 732
Default

Quote:
Originally Posted by Chong420
By doing this will it sort it out something like this?

9/12/2006 - 8
9/13/2006 - 10
9/14/2006 - 26

??
Yes it will. You can ensure the sort order by using an ORDER BY clause as well:
Code:
SELECT [DateField],SUM([Field2]), SUM([Field3]
FROM   [Table]
WHERE  [DateField] BETWEEN '09/25/2006' AND '10/04/2006'
GROUP BY [DateField]
ORDER BY [DateField]
Reply With Quote
  #7  
Old 09-25-2006, 09:47 AM
Chong420 Chong420 is offline
Freshman
 
Join Date: Jan 2006
Posts: 37
Default

Quote:
Originally Posted by Peter_Aquino
Yes it will. You can ensure the sort order by using an ORDER BY clause as well:
Code:
SELECT [DateField],SUM([Field2]), SUM([Field3]
FROM   [Table]
WHERE  [DateField] BETWEEN '09/25/2006' AND '10/04/2006'
GROUP BY [DateField]
ORDER BY [DateField]

Ok now I know I can dynamically change the WHERE [DATEFIELD] BETWEEN '09/25/2006' AND '10/04/2006' by doing

WHERE [DATEFIELD] BETWEEN '" & txtStartDate & "' AND '" & txtEndDate & "'" in the vb code

Right now in vb my sql statement is:

Code:
sqlSearch = "Select * from mylist where dateinput = cvdate('" & dblDay & "') AND Category = '" & cmbTicketType & "'"
But for the SUM[Field1] SUM[Field2]......... how would I do that dynamically??
Maybe I'm not understanding exactly how that works
Reply With Quote
  #8  
Old 09-25-2006, 10:30 AM
Peter_Aquino's Avatar
Peter_Aquino Peter_Aquino is offline
Contributor

* Expert *
 
Join Date: Sep 2004
Location: San Diego, CA, USA
Posts: 732
Default

The first thing to think about is how many of the fields you actually want to retrieve. As is, the "SELECT *" returns all fields. If you're not going to use all of the fields returned, why bother selecting them?

Next, are you performing any calculations on the fields (i.e. do you actually need to sum, average, etc. any of the fields)? I used that in my example to show how to group by date if you were aggregating fields.

Perhaps if you post the fields from your table and explain how you want to present the data to the user (including items you need to calculate, if any), I can make further suggestions.
Reply With Quote
  #9  
Old 09-25-2006, 10:56 AM
Chong420 Chong420 is offline
Freshman
 
Join Date: Jan 2006
Posts: 37
Default

I need to count the amount of tickets that were put in on the day, depending on the ticket type.

So I need to go through the entire database for the selected dates, and add how many tickets were put in on those days. So really there will be 2 fields that I am working with: date in which they were put in and ticket type

TicketType just determines which days too choose for that type of ticket. And the date field will be used to count every time a ticket was put in on that day.

Am I clear on what I want??? If not please let me know and I will try to explain better.

With the code that was presented to me before you posted, it works great...except I now need to go through the list box and seperate the dates and count how many of each date is in it. To where if I can do all this through a sql statement, it sounds easier and will save me time


Your help is very much appriciated.
Reply With Quote
  #10  
Old 09-25-2006, 12:25 PM
Peter_Aquino's Avatar
Peter_Aquino Peter_Aquino is offline
Contributor

* Expert *
 
Join Date: Sep 2004
Location: San Diego, CA, USA
Posts: 732
Default

The following will return the count of records, by Date and TicketType, for all dates in the selected range:

Code:
SELECT   DateField, TicketType, Count(0)
FROM     Table
WHERE    DateField BETWEEN '09/25/2006' AND '10/04/2006'
GROUP BY DateField, TicketType
ORDER BY DateField, TicketType
Reply With Quote
  #11  
Old 09-25-2006, 12:58 PM
Chong420 Chong420 is offline
Freshman
 
Join Date: Jan 2006
Posts: 37
Default

The results from that is it counts the days so for instance I do 9/12/2006 to 9/15/2006 my result is 4. I need to count the number of tickets which were put in each of them days.

Is that what that statement is suppose to do?? Or am I just probably overlooking something?

Heres my code...I edited the sqlStatement some to try and get my results so if it don't look right then thats why

Code:
Option Explicit Dim MyConn As New ADODB.Connection 'Connection variable Dim RS As New ADODB.Recordset 'Recordset variable Dim sqlSearch As String 'Search string Dim dblStart As Double 'Start date used to loop through the dates Dim dblEnd As Double 'End date Dim dblDay As Double 'Variable used to pass each date through Private Sub cmdGetInfo_Click() On Error GoTo Error_Handler 'dblStart = CDate(txtStartDate) 'Put start date in variable 'dblEnd = CDate(txtEndDate) 'Put end date in variable 'Clear the data lstData.Clear lblTicketNumber(1) = 0 'Loop through each date begining with start date and ending with end date 'For dblDay = dblStart To dblEnd Step 1 'Query the database sqlSearch = "Select dateinput From mylist Where dateinput Between cvdate('" & txtStartDate & _ "') And cvdate('" & txtEndDate & "') And Category = '" & cmbTicketType & "' Group by dateinput," & _ "Category Order By dateinput" 'sqlSearch = "Select * from mylist where dateinput = cvdate('" & dblDay & "') AND Category = '" & cmbTicketType & "'" Set RS = MyConn.Execute(sqlSearch) 'Loop through database and retreive information and add it to the list box Do Until RS.EOF lstData.AddItem RS("dateinput") RS.MoveNext lblTicketNumber(1) = lstData.ListCount Loop 'Next dblDay Exit_Sub: Exit Sub Error_Handler: MsgBox "An error has occured, the following error is: " & Error GoTo Exit_Sub End Sub

The other way worked just as I wanted except I would have to loop through the lstBox and count each day
Reply With Quote
  #12  
Old 09-25-2006, 01:41 PM
Peter_Aquino's Avatar
Peter_Aquino Peter_Aquino is offline
Contributor

* Expert *
 
Join Date: Sep 2004
Location: San Diego, CA, USA
Posts: 732
Default

Since I don't know what your Categories are, I'm going to make some up just as an example. The last query I posted (modified to match your field/table names):
Code:
SELECT   DateInput, Category, Count(0) as Quantity
FROM     mylist
WHERE    DateInput BETWEEN '09/25/2006' AND '10/04/2006'
GROUP BY DateInput, Category
ORDER BY DateInput, Category
Would return data similar to this:
Code:
DateInput      Category            Quantity
----------------------------------------------
09/25/2006     Concert             5
09/25/2006     SportingEvent       4
09/26/2006     Concert             7
09/26/2006     SportingEvent       3
I notice that you allow the user to select a specific category to pull the data for, and it looks like you're adding that condition in to the WHERE clause correctly.

I also notice that for your quantity, it looks like you're trying to display it in lblTicketNumber. But, you're populating that Label with the count of items in your Listbox (lstData.ListCount). So ultimately what's happening is your retrieveing only the dates from the database, then placing those in a Listbox, then putting the number of items in the Listbox into the Label.

Here's one last example, adding in the ability to search for a specific Category. If you run the following query:
Code:
SELECT   DateInput, Category, Count(0) as Quantity
FROM     mylist
WHERE    DateInput BETWEEN '09/25/2006' AND '10/04/2006'
AND      Category = 'Concert'
GROUP BY DateInput, Category
ORDER BY DateInput, Category
You'll get results like this:
Code:
DateInput      Category            Quantity
----------------------------------------------
09/25/2006     Concert             5
09/26/2006     Concert             7
And in VB, you can access these pieces of data as follows:
Code:
rs.Fields("DateInput") 'Date field rs.Fields("Category") 'Category field rs.Fields("Quantity") 'Quantity field

Hopefully that'll clear things up a bit.
Reply With Quote
  #13  
Old 09-25-2006, 02:03 PM
Chong420 Chong420 is offline
Freshman
 
Join Date: Jan 2006
Posts: 37
Default

Nope that did clear everything up. I didn't have the Coun(0) as Quanity so thats why that didnt work properly. The reason I have the lstbox is because thats the only way I knew of storing data to be able to count it :P

One last thing When I put it in the loop like

Code:
Do until Rs.EOF text1 = rs.fields("dateinput") & rs.Fields("Category") & rs.fields("Quantity") Rs.moveNext loop

which I should still need to do right?
it will only give me the last date even though I set the text box for multiline
Reply With Quote
  #14  
Old 09-25-2006, 02:10 PM
Peter_Aquino's Avatar
Peter_Aquino Peter_Aquino is offline
Contributor

* Expert *
 
Join Date: Sep 2004
Location: San Diego, CA, USA
Posts: 732
Default

You need to add a Carriage Return / Line Feed to get each row of data on a separate line. You'll also need to make sure to keep the existing text, so as not to overwrite what's already in the box while you're looping. it might also be more pleasing to add something like a dash between the fields in order to add some sort of separation to the text. Otherwise, you'd end up with 09/26/2006Concert5 instead of something easier to read like 09/26/2006 - Concert - 5

The easiest way to do this:
Code:
Do While Not Rs.EOF Text1.Text = Text1.Text & rs.fields("dateinput") & " - " & _ rs.Fields("Category") & " - " & _ rs.fields("Quantity") & vbCrLf Rs.moveNext Loop
Reply With Quote
  #15  
Old 09-25-2006, 02:16 PM
Chong420 Chong420 is offline
Freshman
 
Join Date: Jan 2006
Posts: 37
Default

right I only did that way just to get a quick view of how it is going to work, then modify everything after I get it to work..

And that now works, so I was over writing the existing data...DUH! lol

I want to thank you very much for your help it is very appriciated...and 1 last question what is the differnece between doing a do while not and a do until?

Wouldn't they lead to the same result?
Reply With Quote
  #16  
Old 09-25-2006, 02:19 PM
Peter_Aquino's Avatar
Peter_Aquino Peter_Aquino is offline
Contributor

* Expert *
 
Join Date: Sep 2004
Location: San Diego, CA, USA
Posts: 732
Default

There's no difference.
Reply With Quote
  #17  
Old 12-14-2016, 12:15 PM
sal21 sal21 is offline
Junior Contributor
 
Join Date: Mar 2004
Posts: 260
Default

Quote:
Originally Posted by 00100b View Post
Here is one approach.
Code:
Dim dblStart As Double ' Used to capture the value of the starting date. Dim dblEnd As Double ' Used to capture the value of the ending date. Dim dblDay As Double ' Used to increment from the starting date to the ending date. ' Capture the value for the starting date. dblStart = CDate(Me.Text1.Text) ' Capture the value for the ending date. dblEnd = CDate(Me.Text2.Text) ' Increment through each date, starting with the starting date and ending with the ending date. For dblDay = dblStart To dblEnd Step 1 ' Demonstrate the incrementation. Debug.Print CDate(dblDay) Next dblDay
This works because behind the scenes, Date values are stored as 8-byte double-precision floating point values.
sorry if i post on old th3d...

i need the same code but without Saturday and Sunday...
how to?
Tks.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Looping through a date range
Looping through a date range
Looping through a date range Looping through a date range
Looping through a date range
Looping through a date range
Looping through a date range Looping through a date range Looping through a date range Looping through a date range Looping through a date range Looping through a date range Looping through a date range
Looping through a date range
Looping through a date range
 
Looping through a date range
Looping through a date range
 
-->