Microsoft Access SQL statement

[camelman]

I am using VBA in MICROSOFT ACCESS to write a record to a table.
My regional settings are "English South Africa",my short date is "dd/mm/yyyy" and my long date is "dd MMMM yyyy"
I have an auto date in the table (ie: default value = now() ) in a field called [autodate] and I am writing another date into a field called [mydate] to the table using an SQL statement.
If I type in "6/11/2001 11:32:45" as [mydate], the record saves with the following 2 dates.
[mydate] = "6/11/2001 11:32:45"
[autodate] = "11/6/2001 11:32:45" (month and day have swapped round)

There is nothing wrong with my code leading up to the SQL statement which is
DoCmd.RunSQL "INSERT INTO [Credits] ([mydate]) VALUES (#" & new_date & "#)"
If I use a break point and add a watch to the variable "new_date" I can see that it has the date with the day and month the right way around.
It only swaps it after it has been written to the table.

I have written a function that swaps the day and the month before writing it to the table but this is only a temporary solution. The way the screen works I have to use an SQL statement to write the record to the table.
Help would be much appreciated.

 

[nickjar2]

Hi,

I always have this problem. I think your solution of changing the day and months around is the only solution. SQL only processes American dates (someone correct please if I am wrong). Your date will be fine until processed by the SQL, so the break point will show your date as being correct.
Sorry I don't know a way round this. Swapping the day and month seems to be the only way around this annoying problem for us non Americans. Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[camelman]

I suppose if there is no other way then that is what I have to do. I always worry that it will be tempramental and that one day I will find out that my date swapping will cause it to make the correct date incorrect. Maybe that is just stupid but I hate pulling stunts to get things to work properly. This also happens if you are using an Access Database. You'd think that Microsoft would have fixed this problem by now.

Thanks for confirming that I am not just going mad.

Anyone else got anything to say about whether there is a fix for this ?

 

[nickjar2]

The only other think that may work (probably won't though) is to convert the dates to a long int first.

dim lngDate1 as long

lngDate1 = myDate


and using the lngDate1 var in your SQL. I haven't tested this, but I have seen it used in the dLookup function. Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)