Solved Iterate through dates

[Kayleigh]

Hi,
Have a simple piece of code which doesn't seem to be working. It should be iterating through all the dates from specified start date until end date. However when I try running, I don't seem to be entering the loop. Where have I gone wrong?

'Iterate through dates
       stDate = Format(CDate(Me.txtDateStart), "mm/dd/yyyy")
       enDate = Format(CDate(Me.txtDateEnd), "mm/dd/yyyy")
        i = 1
        d = stDate
        Do While d <= enDate
                Debug.Print "Iteration " & i & ":" & vbTab & d
              
                'Some actions
              
                d = DateAdd("d", 1, d)
                If d > enDate Then
                    Exit Do
                End If
                i = i + 1
            Loop

 

[MajP]

Why converting to string? Use real date datatypes.

 

[Kayleigh]

I need to ensure dates are always in dd/mm/yyyy format for consistency. How else would I do so?

 

[theDBguy]

I need to ensure dates are always in dd/mm/yyyy format for consistency. How else would I do so?

But the code you posted is using the format "mm/dd/yyyy." Besides, you should format only to "display" the date, not when using it for calculations. Pretty much...

 

[Kayleigh]

I was having trouble with VBA recognising dates from SQL and vice versa so thought that would be best way to resolve.
How else would I do this?

 

[theDBguy]

I was having trouble with VBA recognising dates from SQL and vice versa so thought that would be best way to resolve.
How else would I do this?

It depends... In your code, what does this mean?

'Some actions

If we know what those actions are, we can tell you whether you need to format the date or not.

 

[Kayleigh]

I will be opening DAO recordset to check if date is entered and if not creating a record for this date.

 

[theDBguy]

I will be opening DAO recordset to check if date is entered and if not creating a record for this date.

You might not even need a recordset for that. You should be able to use DCoun(). For example:

Dim dte As Date

For dte = Me.txtDateStart To Me.txtDateEnd
    If DCount("*","TableName","DateField=#" & Format(dte,"yyyy-mm-dd") & "#")=0 Then
        Debug.Print dte
    End If
Next

(untested)
Hope that helps...

 

[Pat Hartman]

I need to ensure dates are always in dd/mm/yyyy format for consistency. How else would I do so?

NO, you don't. As long as the dates are defined as datetime data types, Access/VBA/SQL Server will process them correctly. DO NOT format dates unless you need to. Formatting is for humans. Internally dates are stored as double precision numbers. The integer part is the number of days since Dec 30, 1899 and the decimal is the elapsed time since midnight. Negative numbers are dates prior to Dec30, 1899. So
0.0 = Midnight on Dec 30, 1899
1.5 = Noon on Dec 31, 1899
-1.75 = 6 PM on Dec 29, 1899

You always want to leave dates as their internal numeric value. When you compare two "dates" that are strings, Access/VBA/SQL Server don't know that the strings are dates and so they apply string logic to the compare rather than numeric logic so -

"01/05/2021" will be less than "01/06/2020" because the characters are compared one at a time left to right and 5 is less then 6.

There is one gotcha' though and that is that SQL Server interprets string dates as mm/dd/yyyy unless there is no ambiguity. So 01/02/2021 = Jan, 2 but 30/01/2021 will be interpreted as Jan 30. So, where you have to intervene with formatting is when you are creating STRINGS that will be sent to SQL Server.
strSQL = " Select ... From ... Where TestDT = Forms!myform!mytestDT" --- this will work fine as long as the control named mytestDT is either defined specifically as a date or is bound to a field defined as datetime.
However, if the control is defined as a string, the value will be treated as a string and will be assumed to be mm/dd/yyyy format. In that case, you would need to format the control:
strSQL = " Select ... From ... Where TestDT = #" & Format(Me.mytestDT, "mm/dd/yyyy") & "#"

Extending that to using a variable, the code would need to be:
strSQL = " Select ... From ... Where TestDT = #" & Format(testDT, "mm/dd/yyyy) & "#"

Additionally, Excel and SQL Server and other RDBMS' use the same concept of an origin date so they can store dates as a number but they don't use the same origin date that Access does. I think SQL Server uses 01/01/1900.

 

[Isaac]

I was having trouble with VBA recognising dates from SQL and vice versa so thought that would be best way to resolve.
How else would I do this?

I think I know what you are getting at - but the sequence would be more like this:

1. Your input has some string, in any recognizable date format.
2. You convert it to format(input,"mm/dd/yyyy") << Note, that just produced a String value
3. You then convert what you got in Step #3 to a real date - which then allows you to write further code/loop that actually treats it as a date

So your original thought process was a good start, but maybe would have been:
dim varDate as Date
varDate = cdate(format(me.ControlName.value,"mm/dd/yyyy"))

Or, mentally working backwards:
4. Your loop code MUST have a date variable to work properly and to perform evaluations on that date variable
3. You want that date variable to have a value, essentially, equal to a form control value
2. You're worried about directly coding DateVariable = Control Value, because of the format (and possibly because Control value is a string, too, which I'm not sure of).
1. So you convert the control value to a string, formatted as a date...but then need to set the date variable to the DATE datatype, so convert it back to a date.

(Sure, you could try just saying: DateVariable = Me.Controlname.Value
....but then you'd be relying on the implicit conversion, and depending on what is sourcing the control value, Access may actually convert it to a different date than you want.)

Not sure if that helped at all.

 

[arnelgp]

you Declare your variable as Date, therefore no need for you to do conversion:

'Iterate through dates
    dim stDate As Date, enDate As Date, d As Date
       stDate = Me.txtDateStart
       enDate = Me.txtDateEnd
        i = 1
        d = stDate
        Do While d <= enDate
                Debug.Print "Iteration " & i & ":" & vbTab & d
              
                'Some actions
              
                d = DateAdd("d", 1, d)
                If d > enDate Then
                    Exit Do
                End If
                i = i + 1
            Loop

 

[Kayleigh]

Worked perfectly when formatted as date.
Thanks enjoyed the discussion!

 

[theDBguy]

Worked perfectly when formatted as date.
Thanks enjoyed the discussion!

Hi. Glad to hear you got it sorted out. Good luck with your project.