date advice needed (1 Viewer)

[esymmonds]

I have a booking system that was working all nicely. It done everything it was supposed to.

Today I was demonstrating the system I made (for my degree) to my supervisor and it suddenly dosent work! Was not amused!

I've figured out that something is changing the format of the date (from british to USA), between the sql statement my VBA excecutes and the data being put in the table.

It worked fine so I cannot figure out why it dosen't now when nothing has changed!

Here is the VBA code:

Private Sub add_booking_Click()

Dim dteBookDate As String
Dim dteLeaveDate As String
Dim nightnum As Integer

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

DoCmd.RunSQL "INSERT INTO booking VALUES (" & Forms("booking2").[room_no] _
                  & ", #" & dteBookDate & "#, " & 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] _
                  & ", #" & dteBookDate & "#, " & Forms("booking2").[Cust_no] & ", " & False & "," & 0 & ");"
   dteBookDate = DateAdd("d", 1, dteBookDate)
Loop
DoCmd.RunMacro "book_room"
End Sub

Can anyone solve the mystery?

Emma

 

[WayneRyan]

Emma,

I like the USA dates!

If I understand you correctly, you are just displaying the dates wrong. They
are being stored correctly internally.

Your code is OK (but I still vote for explicitly declaring the columns on the
Insert statement).

Windows controls the display of dates by your settings on its Control Panel.
There have been many posts here on this topic. Have you tried the Search
facility here.

You can always use the Format function to display your dates however you
want.

Wayne

 

[esymmonds]

I understand what your saying and I know about changing the date format but it's actually inserting the date into the table in the format mm/dd/yy when it's correct in the code (stepped thru to check the values).

I've also tried changing the date to a string but it still does the same.

It did work and nothing has been changed on my computer.

Emma

 

[Rich]

& ", #" & Format(dteBookDate,"dd/mm/yyyy") & etc

 

[Pat Hartman]

Dates are stored internally as double precision numbers with the integer representing the number of days since Dec 31, 1899 and the decimal representing milliseconds since midnight. As long as you work with fields defined as Date/Time data type you won't have any trouble. Once you convert a Date to a string as you have done is where you have the problem. Access was written by a US company so defaults would favor the US date format whenever there is any ambiguity. Rich's suggestion to explicitly format the string should resolve your problem.

 

[esymmonds]

Thanks Rich and Pat,

Have tried out your ideas but it didn't work

I have found tho that if i put today or tomorows date in (28/02/2004, 29/02/2004) it stores it fine, but anything after 01/03/2004 is stored mm/dd/yy.

Also if i put arrival today and departure 03/03/2004 it puts the first 2 days in correct and the others the wrong way.

Is it anything to do with this year being a leap year?

Emma

 

[Rich]

You've declared dteBookDate as a String, it's not, it should be Date

 

[esymmonds]

You've declared dteBookDate as a String, it's not, it should be Date

I changed it back after i wrote the first post i just thought id see if string instead of date changed anything. it didn't so i changed it back. should have said.

I'm sooo confused

Emma

 

[Rich]

Have you changed it back to date and used the Format function on all the & dteBookDate & "#, and dteLeaveDate as Date?