Date issue

[garyd1234]

Hi,
I am trying to update a table that has all the dates (formatted short date 99/99/9999). the idea is that I put in a start date in one text box formatted the same way and and enddate in another. I use a loop
dim z as date
for z= begdt to enddt
db.execute "update " & room & " set [EventName] = '" & Text 88 & "' where [EventDate] = #" & z & "#"
next z

It always puts the first date in the table (room is tablename). It will not put the next dates in the range in there. Something is changing the format of the date or something when it loops the second time. I cannot figure it out. I've tried format(z, "mm/dd/yyyy") , put it into another varialbe tried z=z+1 when z was not in a loop. Nothing works. Any ideas? Thanks

 

[Dugantrain]

I'm really not sure what your problem is as your SQL seems to look ok. Are the variables begdt and enddt declared as Date? If so, maybe you can't increment Dates in a For...Next loop (really not sure). I have done something similar, but I used a Do...Loop to increment the Date by one each time, if you can't get this working, then give that a shot.

 

[bat17]

I can't test this at the moment but some thoughts.
SQL use dates in the format MM/DD/yyyy so if you are using UK date format DD/MM/YYYY then you have to chop the date up and put it backtogether in the right order rather than just use the format command. SQL ignores regional settings.

Peter