Solved Iterate through dates (1 Viewer)

Krayna

Member
Local time
Today, 13:35
Joined
Sep 24, 2020
Messages
516
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?
Code:
'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

You've got your good things, and you've got mine.
Local time
Today, 08:35
Joined
May 21, 2018
Messages
5,707
Why converting to string? Use real date datatypes.
 

Krayna

Member
Local time
Today, 13:35
Joined
Sep 24, 2020
Messages
516
I need to ensure dates are always in dd/mm/yyyy format for consistency. How else would I do so?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:35
Joined
Oct 29, 2018
Messages
17,199
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...
 

Krayna

Member
Local time
Today, 13:35
Joined
Sep 24, 2020
Messages
516
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’m here to help
Staff member
Local time
Today, 05:35
Joined
Oct 29, 2018
Messages
17,199
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?
Code:
'Some actions
If we know what those actions are, we can tell you whether you need to format the date or not.
 

Krayna

Member
Local time
Today, 13:35
Joined
Sep 24, 2020
Messages
516
I will be opening DAO recordset to check if date is entered and if not creating a record for this date.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:35
Joined
Oct 29, 2018
Messages
17,199
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:
Code:
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

Super Moderator
Staff member
Local time
Today, 08:35
Joined
Feb 19, 2002
Messages
33,798
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

Lifelong Learner
Local time
Today, 05:35
Joined
Mar 14, 2017
Messages
5,933
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

..forever waiting... waiting for jellybean!
Local time
Today, 21:35
Joined
May 7, 2009
Messages
14,663
you Declare your variable as Date, therefore no need for you to do conversion:
Code:
'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
 

Krayna

Member
Local time
Today, 13:35
Joined
Sep 24, 2020
Messages
516
Worked perfectly when formatted as date.
Thanks enjoyed the discussion!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:35
Joined
Oct 29, 2018
Messages
17,199
Worked perfectly when formatted as date.
Thanks enjoyed the discussion!
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom