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...

 

[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.