Update Statement VBA Access (1 Viewer)

[hazeleyre23]

Hi,

I have an update statement which works but only with dates 1st - 12th and I can't understand why.

 Currentdb.Execute "UPDATE dbo_Agent_Data SET ToDate = #" & Me.txtUpdateToDate & "# , LogTime =  '" & LogTime & "' WHERE id_NEW = " & Me.txtUpdateID & "", dbSeeChanges

The code updates the ToDate field with the new selected date. I have gone through all my code to see if I have put a block on it allowing dates between 13th -30th/31st but can find anything.

Am I missing something from my code?

Thanks

 

[Ranman256]

try
ssql = "UPDATE dbo_Agent_Data SET ToDate = #" & Me.txtUpdateToDate & "# , LogTime = '" & LogTime & "' WHERE id_NEW =" & Me.txtUpdateID

docmd.runsql sSql

and put a stop (f9) on the docmd line.
see if the sql is valid. (paste in a query)
or
just make a query using this sql and run by
docmd.openquery "qMyQuery"

 

[MarkK]

Sounds like a date format problem, like the difference between mm/dd/yy and dd/mm/yy. 1st - 12th is likely the month, not the day.

 

[hazeleyre23]

Thank you both for your replies.

Is the format "YYYY-MM-dd" in VBA access?

thank you

 

[MarkK]

There are many formats that can be accurately read by Access, and some are dependent on your Windows regional settings. You need to look at the results you are getting as evidence of what formats are being accurately read.

If you are pulling user input into your system, you can use the vba.IsDate() function to determine if a date is valid before inserting it and having it rejected by the table, but with any numeric format there is a risk that the days and months will be reversed.

The simplest and least ambiguous format is "medium date", which is dd-mmm-yy.

 

[hazeleyre23]

thank you for pointing me in the right direction. I have now fixed it