Date format in SQL Statement (1 Viewer)

[esymmonds]

I have a booking system and it all works fantastically apart from the dates being stored in the form dd/mm/yyyy, except if the date is before the 13th, then it is stored mm/dd/yy.

I want all dates stored as dd/mm/yyyy but i have just read in the help files that any dates in SQL code must be usa format, but not in the query designer.

I have come up with a few solutions but I'm not sure if any are actually doable. I have tried a search but didn't find any answers.

So here's my ideas:

1. Pass the variables to a query in access

2. Insert the data without using sql?

3. Insert the dates number into the table and format it in my forms

If anyones got any other ideas, please let me know, I'm desperate!

Here's my code:

Private Sub add_booking_Click()

Dim dteBookDate As Date
Dim dteLeaveDate As Date
Dim nightnum As Integer

dteBookDate = CDate(Forms("booking2").date1)
dteLeaveDate = CDate(Forms("booking2").date2)
nightnum = Forms("booking2").nights

DoCmd.RunSQL "INSERT INTO booking VALUES (" & Forms("booking2").[room_no] _
                  & ", #" & Format(dteBookDate, "dd/mm/yyyy") & "#, " & Forms("booking2").[Cust_no] & ", " & False & ", " & nightnum & " );"
dteBookDate = DateAdd("d", 1, dteBookDate)

Do While dteBookDate < dteLeaveDate
   DoCmd.RunSQL "INSERT INTO booking VALUES (" & Forms("booking2").[room_no] _
                  & ", #" & Format(dteBookDate, "dd/mm/yyyy") & "#, " & Forms("booking2").[Cust_no] & ", " & False & "," & 0 & ");"
   dteBookDate = DateAdd("d", 1, dteBookDate)
Loop
DoCmd.RunMacro "book_room"
End Sub

Thanks
Emma

 

[Rich]

You really should continue with the original post, have you tried
& Format(dteBookDate, "mm/dd/yyyy") ?

or on second thoughts why not use the rsUpdate method?

 

[raskew]

I want all dates stored as dd/mm/yyyy but i have just read in the help files that any dates in SQL code must be usa format, but not in the query designer.

It might be helpful to read-up on how dates are stored. Regardless of format, they are all stored as double-precision, floating-point numbers. Here are a couple of MSKB sites to check-out:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;130514
http://support.microsoft.com/default.aspx?scid=kb;EN-US;88657

Example to try in the debug window:

x = date()

'with US short-date format
? x
3/1/04
'with your regional settings
1/3/04

'.... to see how 1-Mar-04 is stored
'regardless of regional settings,
? cdbl(date)

38047

.... to see how date and time are stored:
y = now()

? cdbl

38047.7676041667

So, in your SQL, format the dates as "mm/dd/yy". It'll
then store correctly. Your forms remain in your regional setting
so you'll see the dates in the format you're accustomed to.

Bob

 

[esymmonds]

Thanks for your help Raskew and Rich,

I still couldn't get it working but I have now in a long winded way, but it works so I'm happy!!

What I've done is put an invisable text box on my form and every time the code goes through the loop it puts the next date into the text box and then run an access query instead of an sql statement.

Thanks again for your help