Do While Loop with Dates

Question

I'm making a code in VBA, that can loop through dates and add some data to some tables in Access. I feel like i've tried everything and browsed the web and it still dosen't seem to work. I think my problem is the formatting of dates, because i use an european date input, instead of an US input. I've tried to format the dates, as seen in the code below.

Dim TODAY as string, TimeReg as string, UserReg as string, Init as string, _ 
SendFrom as string, SendTo as string, DateFrom as Date, DateTo as date, _
Comment as string

'Me.txtDateF.value Input f.ex. = 01-01-2020
'Me.txtDateT.value Input f.ex. = 16-01-2020

'Values'
TODAY = Me.txtDD.Value
TimeReg = Me.txtTime.value
UserReg = Left(Me.txtUser.value, 3)
Init = Me.cmbInit.value
If IsNull(Me.txtFrom.value) Then
SendFrom = ""
Else
SendFrom = Me.txtFra.value
End if

SendTo = Me.cmbTo.Column(1)
DateFrom = CDate(Format(Me.txtDateF.value, "dd-mm-yyyy"))
DateTo = CDate(Format(Me.txtDateT.value, "dd-mm-yyyy"))
Comment = Me.txtComment.value

'Define team on which it will use in SQL-Query
Select Case Me.txtTeam.value
Case "Team 1"
Team = "Team1"
TeamCom = "Team1Com"
Case "Team 2" etc...
End select

DoCmd.SetWarnings False

Do While DateFrom < DateTo

strSQL = "UPDATE [" & Team & "] SET [" & Init & "] = '" & SendTo & "' WHERE Date = #" & DateFrom "#"

DoCmd.RunSQL strSQL

ChangeSQL = "INSERT INTO tblChanges " _
& "(Date, Time, InitChange, TimeR, ShiftB, ShiftA, DateR, DateT, Comment)" _
& "VALUES (#" & TODAY & "#, #" & TimeReg & "#, '" & Init & "', '" & UserReg & "', '" & SendFrom & "', '" & SendTo & "', #" & DateFrom "# '" & Comment & "');"

DoCmd.RunSQL ChangeSQL

DateFrom = DateAdd("d", 1, DateFrom)
Loop

DoCmd.SetWarnings True

If the Input-dates is from f.ex. 01-01-2020 to 24-01-2020, the following dates change.

01-01-2020
13-01-2020
14-01-2020
15-01-2020
16-01-2020
17-01-2020
18-01-2020
19-01-2020
20-01-2020
21-01-2020
22-01-2020
23-01-2020
24-01-2020
01-02-2020
01-03-2020

It's missing the dates between 01-01-2020 to 12-01-2020. It seems to me like the format is off.

Dates are just numbers behind the scenes. You don't need to format them to run the loop, only for presentation. Are `txtDateF` and `txtDateT` real dates or strings? — Kostas K., Oct 14 '20 at 15:58
This code will iterate through the dates excluding the last day. Use `DateFrom <= DateTo` if you want to include the last date. If the loop is functioning properly the problem is in your SQL statements which you have omitted. Often time when working with dates the where is the issue. You will need to create a custom function to handle the If the dates if they are stored as strings. — TinMan, Oct 14 '20 at 15:59
@KostasK. The user picks the txtDateF and txtDateT via the a datepicker on the textbox. And the input are stored in the table as a date. — Claus Thaulov Skeel Kristensen, Oct 14 '20 at 16:01
@TinMan I've posted the SQL-Statements as well. Do you think is stored as a string? — Claus Thaulov Skeel Kristensen, Oct 14 '20 at 16:12
If this is an Access database you should be able to goto the table designer and check the datatype. — TinMan, Oct 14 '20 at 16:22
Those are actual dates. By the way `Date` and `Time` are keywords, you better wrap them in brackets `[Date]` and `[Time]`. — Kostas K., Oct 14 '20 at 16:25
There are missing ampersands in your statements. I added them but found there are missing commas in your SQL Statements. — TinMan, Oct 14 '20 at 16:27
Here is the SQL printed to the immediate window with no values: `INSERT INTO tblChanges (Date, Time, InitChange, TimeR, ShiftB, ShiftA, DateR, DateT, Comment)VALUES (##, ##, '', '', '', '', ## '');` Notice the missing comma and that there are only three data values. Are `TimeR` abd `DateR` dates? — TinMan, Oct 14 '20 at 16:41
@KostasK. I know sorry, but i translated it from danish, that's why :-) — Claus Thaulov Skeel Kristensen, Oct 14 '20 at 16:44
@TinMan No TimeR stands for "The time which the user made the change" and DateR stands for "The date which the user made the change". — Claus Thaulov Skeel Kristensen, Oct 14 '20 at 16:51
They are being passed as string values. Shouldn't they be wrapped in hashes (e.g. `"#" & TimeR & "#"), — TinMan, Oct 14 '20 at 16:55
@TinMan I've added almost all the code, to give you a better understanding. They are wrapped in hashes, aren't they? — Claus Thaulov Skeel Kristensen, Oct 14 '20 at 17:01

score 2 · Accepted Answer · answered Oct 14 '20 at 17:35

Many errors here, but this should bring you closer:

Dim Today As Date, TimeReg As Date, UserReg As String, Init As String, _ 
SendFrom As string, SendTo As String, DateFrom As Date, DateTo As Date, _
Comment As String

'Me.txtDateF.value Input f.ex. = 01-01-2020
'Me.txtDateT.value Input f.ex. = 16-01-2020

'Values'
Today = TimeValue(Me!txtDD.Value)
TimeReg = Me!txtTime.Value
UserReg = Left(Me!txtUser.Value, 3)
Init = Me!cmbInit.Value
If IsNull(Me!txtFrom.Value) Then
    SendFrom = ""
Else
    SendFrom = Me!txtFra.Value
End if

SendTo = Me!cmbTo.Column(1)
DateFrom = DateValue(Me!txtDateF.Value)
DateTo = DateValue(Me!txtDateT.Value)
Comment = Me!txtComment.Value

'Define team on which it will use in SQL-Query
Select Case Me!txtTeam.Value
    Case "Team 1"
        Team = "Team1"
        TeamCom = "Team1Com"
    Case "Team 2" etc...
End select

DoCmd.SetWarnings False

Do While DateFrom < DateTo

    strSQL = "UPDATE [" & Team & "] SET [" & Init & "] = '" & SendTo & "' WHERE [Date] = #" & Format(DateFrom, "yyyy\/mm\/dd") & "#"
    DoCmd.RunSQL strSQL

    ' Correct here. Range of fields doesn't match range of values!
    ChangeSQL = "INSERT INTO tblChanges " & _
        "([Date], [Time], InitChange, TimeR, ShiftB, ShiftA, DateR, DateT, Comment)" & _
        "VALUES (#" & Format(Today, "yyyy\/mm\/dd") & "#, #" & Format(TimeReg, "hh\:nn\:ss") & "#, '" & Init & "', '" & UserReg & "', '" & SendFrom & "', '" & SendTo & "', #" & Format(DateFrom, "yyyy\/mm\/dd") & "#, #" & Format(DateTo, "yyyy\/mm\/dd") & "#, '" & Comment & "');"
    DoCmd.RunSQL ChangeSQL

    DateFrom = DateAdd("d", 1, DateFrom)
Loop

DoCmd.SetWarnings True

Or rewrite/simplify the SQL using my function CSql.

score 0 · Answer 2 · answered Oct 14 '20 at 16:22

How I came around that : Up top I set the value

 Date0 = DateAdd("d", 0, CDate(Format(Me.txtDateF.value, "dd/mm/yyyy")))

and after that, down in the sql statement I would write it like that

 strSQL = "UPDATE [" & Team & "] SET [" & Init & "] = '" & SendTo & "' WHERE Date = #" & Format(Date0, "yyyy/mm/dd") & "# "

Hope it helped. Truth is dates in access confused me too in the beggining!

score 0 · Answer 3 · answered Oct 14 '20 at 17:06

Using a helper function to create SQL your statements will allow you to test the queries independently from the rest of the code.

Function InsertIntotblChangesSQL(DateOf As Date, _
                                                                                TimeOf As Date, _
                                                                                InitChange As String, _
                                                                                TimeR As Date, _
                                                                                ShiftB As String, _
                                                                                ShiftA As String, _
                                                                                DateR As Date, _
                                                                                DateT As Date, _
                                                                                Comment As String) As String
    Const BaseSQL As String = "INSERT INTO tblChanges([Date], [Time], InitChange, TimeR, ShiftB, ShiftA, DateR, DateT, Comment)"
    Const HashMark As String = "#"
    Const Astrophe As String = "'"
    
    Dim Data(8) As String
    Data(0) = HashMark & DateValue(DateOf) & HashMark
    Data(1) = HashMark & TimeValue(TimeOf) & HashMark
    Data(2) = Astrophe & InitChange & Astrophe
    Data(3) = HashMark & TimeValue(TimeR) & HashMark
    Data(4) = Astrophe & ShiftB & Astrophe
    Data(5) = Astrophe & ShiftA & Astrophe
    Data(6) = HashMark & DateValue(DateValue(DateOf)) & HashMark
    Data(7) = HashMark & DateValue(DateT) & HashMark
    Data(8) = Astrophe & Comment & Astrophe
    
    Dim Values As String
    Values = "VALUES (" & Join(Data, ",") & ")"
    
    InsertIntotblChangesSQL = BaseSQL & vbNewLine & Values
End Function

Immediate Window Results

Using the helper function, I can print the SQL Statement to the immediate window. I will then use the Access Query Designer to test the SQL.

Access Query Design Window

Use DateValue() to remove time values from Date fields.

WHERE DateValue([Date]) = #" & DateFrom "#"

I've edited the SQL-Statement, thank you. But it is still not working with the dates. — Claus Thaulov Skeel Kristensen, Oct 14 '20 at 17:17

Do While Loop with Dates

3 Answers3