Date Formatting

[David]

Hello

I have a weird thing happening in my data storage
I use the following statement to update the date field in the tabe 'pax'

sqlString = "UPDATE Pax SET Pax.FlightDate=#" & me.NewDate & "# WHERE
Pax.FlightNo=75 AND Pax.FlightDate=#" & me.CurrDate & "#;"
DoCmd.RunSQL sqlString

The box NewDate is where the updated date is entered. I have used different
formats (dd-mmm-yyyy, mmm-dd-yyyy, mm-dd-yyyy) and they all have the
following problem.

Dates in which the day is 12 or less get formatted int he table as
mm/dd/yyyy but dates in which the day is greater than 12 get formatted as
dd/mm/yyyy.

What is going on? I have changed data types all over the place and even used
defaults and I can't get it to work

David

 

[RoyVidar]

David laid this down on his screen :

Hello

I have a weird thing happening in my data storage
I use the following statement to update the date field in the tabe
'pax'

sqlString = "UPDATE Pax SET Pax.FlightDate=#" & me.NewDate & "# WHERE
Pax.FlightNo=75 AND Pax.FlightDate=#" & me.CurrDate & "#;"
DoCmd.RunSQL sqlString

The box NewDate is where the updated date is entered. I have used
different formats (dd-mmm-yyyy, mmm-dd-yyyy, mm-dd-yyyy) and they
all have the following problem.

Dates in which the day is 12 or less get formatted int he table as
mm/dd/yyyy but dates in which the day is greater than 12 get
formatted as dd/mm/yyyy.

What is going on? I have changed data types all over the place and
even used defaults and I can't get it to work

David

See Allen Brownes article http://allenbrowne.com/ser-36.html for
explanations.

Use either that format, or ISO 8601 when you concatenate the date
into the string.

....Pax.FlightDate=#" & Format(me.NewDate, "yyyy-mm-dd") & "# WHERE