Saving a Date in Access International Regional Settings...

[neal007]

I get an error when using a INSERT INTO when saving a Date Value that is in the format of German Regional Settings. How do you format the date properly for saving to MS Access date fields?

I've tried Format(myDate,"M/d/yyyy hh:mm:ss")

But it's still showing as 27.11.2003 11:11 and throwing an exception.

[Jaiboy]

That is not at all a problem. You should not worry about the format in storing the date. Whatever it is that field is a date field which can store a date along with the time. While storing a date don't format it. Instead format the date while getting it from the database. This is the trademark all programmers follow.

The problem will arise only, When you try to update your date field from a formatted date string value. Then store the datevalued string into a date variable and then insert in to the table.
Ex.

Table X
---------
X1 Number,
X2 Date. (We consider the british format m/d/y hh:mm:ss)

you have value x11 = 10
x12 = "1/2/2003 12:12:10" ' Means Jan 2

So you declare a variable
Dim P as Date,Sql as string
P = x12

SQL = "Insert into X Values(" & X11 & ",'" & Format(P,"dd-MMM-yyyy hh:mm:ss") & "')"

'Any how there is no need of formatting here, but when u r thinking about the client-server model, your machine and the server machine where the database service resides may have different regional settings in date format. So I think all databases will support and won't get confused the above mentioned format. If u r going to execute the code in the same machine where the database service exists then probably you can omit the format. '
And then execute the SQL.

-Jai

[sw_is_great]

Follow the thumb rule :

inserting into table :
NO NEED TO FORMAT DATE

fetching from table :
YOU MAY FORMAT THE DATE FIELD