DateADD problem

[garyd1234]

Hi,
I am doing a scheduling program. The user puts a starting date in one text box (formatted as short date 99/99/9999) and an end date in another formatted the same way. I test the two boxes to ensure data integrity then do the following code to get the number of days in the range and start a loop that writes the record to a table which has all the dates of the year in the first column. So it is suppose to loop through and update the table based on the event date. Well it only does the first date. Something happens when I use DateAdd to add a day. I tried many different formats but to no avail. What am I doing wrong? Why does it only update the first date and not the others in the range even though it goes through the loop ok??? All the TextXX boxes have what are supposed to have in them. It's just that darned DATE!!!
nxtday = CDate(Me.Text84)
If Not IsNull(Me.Text92) Then
dtdiff = DateDiff("d", CDate(Me.Text84), CDate(Me.Text92)) + 1
Else
dtdiff = 1
End If
' nxtday = Format(Me.Text84, "mm/dd/yyyy")


' db.Execute "update " & room & " set [DateBooked] = #" & Text82 & "# , [EventName] = ' " & Text90 & "', [Number] = '" & Text88 & "' where [EventDate] = #" & Text84 & "'"
'update contract
For x = 1 To dtdiff
db.Execute "update " & Text96 & " set [DateBooked] = #" & Text82 & "#, [Number] = '" & Text88 & "' , [EventName] = '" & Text90 & "' where trim([EventDate]) = #" & nxtday & "#"
nxtday = DateAdd("d", 1, nxtday)
Next x

 

[mdmilner]

Can you put the two date values into a For...Next loop?
And use that to iterate thru the dates? Don't use calculated differences, use the dates themselves as they really are stored as a number.

I've never even thought about doing it this way until reading your message, but the below test sub that I wrote worked. Use better variable names.

Public Sub datetest()
Dim x, y, z As Date
y = #1/16/2004#
z = #1/10/2004#

For x = z To y
 MsgBox x
Next x

End Sub