I have an Access 2013 form which has two unbound date fields, FromDate and ToDate. I insert these into a table (TblGuestBooking) which has an autonumber key field, so this doesn't feature in the SQL statement to follow.
If the FromDate and ToDate are in the same month, the dates are entered as dd/mm/yy, the format of the form field. If, however, the From date is in one month and the to date is in the next month or later month, the format changes to mm/dd/yy for the subsequent months.
for example 26/2/14 to 3/3/14 results in the following table entries:
26/02/14,
27/02/14,
28/02/14,
03/01/14,
03/02/14,
03/03/14
This is the code snippet I am using to put the dates into the table (BookingID is obtained from the form.)
Dim BookingDate As Date
Dim SQLString As String
....
BookingDate = FromDate
Do
SQLString = "INSERT INTO TblGuestBooking ([BookingDate], [BookingID]) VALUES (#" & BookingDate & "#" & "," & Me.GuestSuiteBookingID & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL SQLString
DoCmd.SetWarnings True
BookingDate = BookingDate + 1
Loop Until BookingDate = ToDate + 1
If you've read this far, thank you for your time. If you can help me out, many, many thanks.
When processing date literals (text values enclosed by "hash marks" #) Access SQL will always interpret ambiguous xx-yy-zzzz dates as mm-dd-yyyy, regardless of the regional setting in place on the computer. So, if your machine is configured to display short dates as dd-mm-yyyy and you create an Access query that uses #04-02-2014# it will always be interpreted as April 2, not February 4.
The solution is to always format date literals as unambiguous yyyy-mm-dd values. In your case, instead of
... VALUES (#" & BookingDate & "#" ...
you would use something like
... VALUES (#" & Format(BookingDate, "yyyy-mm-dd") & "#" ...
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments