Date Formating Changes

[Guest]

Hi i'm using Access 2002.

I have an unbound form frmNewClass. On this form I have a command button
that uses an insert SQL to insert a record to tblClassDate. One of my form
fields txtDate is formated as Medium Date. The corresponding table field
classDate is formatted Short Date with an input Mask of Medium Date type.
When I try to update the table the month and day values switch( eg txtDate
reads 02-Jun-05 but reads 06/02/05 in the table). this only happens however,
if the day value is <= 12, if the day value is greater that 12 then it
updates the table properly. I debugged my code and in the watch the date
value is formatted correct.
I'm using docmd.runsql to update the table. Is this what could be giving me
problems? I've read that it can mess with date formats but haven't read what
the bugs are.

Help appreciated.
thanks,
Jason

 

[Andreas]

In your SQL statement, try something like:
Format(txtDateField,"dd-mmm-yyyy")
rather than referringto the text field directly.

Regards,
Andreas

 

[Steve Schapel]

Jason,

When manipulating dates in VBA code, American date format will always be
assumed. You should do one of two things:
1. Always explicitly format your date variables in American date format
#mm\/dd\/yy# for example...
"INSERT INTO YourTable ( ClassDate ) VALUES ( #" & Format(Me.TextDate,
"mm\/dd\/yy") & "# )"
2. Use the CLng() function to return the numerical value of the date
variable for example...
"INSERT INTO YourTable ( ClassDate ) VALUES ( " & CLng(Me.TextDate) & " )"

 

[Guest]

Thanks explicitly formating the date worked.

Jason,

When manipulating dates in VBA code, American date format will always be
assumed. You should do one of two things:
1. Always explicitly format your date variables in American date format
#mm\/dd\/yy# for example...
"INSERT INTO YourTable ( ClassDate ) VALUES ( #" & Format(Me.TextDate,
"mm\/dd\/yy") & "# )"
2. Use the CLng() function to return the numerical value of the date
variable for example...
"INSERT INTO YourTable ( ClassDate ) VALUES ( " & CLng(Me.TextDate) & " )"