I need British date format but goes in as American please help.

[MottoK]

Hi,

Need some quick help - trying to do what should be very simple. I have a form in access with a text box and a button, when clicking the button SQL updates a table with 1 field (in date format) - it goes in as american date - I can't get British date format!!

SQL:

sql = "update Current_Date set Current_Date.CurrentDate=" & "#" & Format(Me!FrmCurrentDate, "dd/mm/yyyy") & "#"


E.G. I type this in as 02/04/2007 and it goes in to the table as 04/02/2007.

Please Help!!

 

[PHV]

Use non ambiguous date format:
sql = "UPDATE Current_Date SET CurrentDate=#" & Format(Me!FrmCurrentDate, "yyyy-mm-dd") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MottoK]

You rock, superb many thanks!

 

[MottoK]

Hold up - it still ain't workin.

Tried it with 02/13/2007 and it went in as 13/02/2007.

Arrrgghh been trying to get this for several hours!

 

[HughLerwill]

The date will be stored in the field as a double, whatever format you apply when writing to it. The part of this number to the left of the decimal stores the day number, the decimal part holds the fraction of a day; this happens behind the scenes and need not concern you. When you retieve/ report a date variable it is converted automatically into a recognisable date string which by default has a format specified in regional settings of the OS.

To produce a non-default format (ref OS regional settings)to the date when you have retrieved it you must format it when you report it.

 

[HughLerwill]

Sorry I think I misunderstood your question...you are having trouble getting the correct date written.

 

[johnwm]

You should always store your date data as a Date type. The only time it should be formatted is when you display to your client

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[HelsByTheDock]

Whenever dates confuse me I re-read a paragraph from this excellent book (IMHO!):

"Beginning Access 2002 VBA" by Smith, Sussman et al:

"The locale of VBA is always English (United States) irrespective of the way that you have configured the Regional Settings in your Control Panel. As a result, if you enter a date in either a SQL statement or as in VBA, it will be interpreted as if it were in the format mm/dd/yy, that is, in US format.

To ensure that all of the dates you enter will be interpreted correctly, it is best to explicitly convert all dates to US format before using them in SQL statements or in VBA."

Personally, I always convert dates to US format when I write SQL in VBA with a couple of variables along the lines of:

dtDueDateUK = Format(Me.tbxRespond_By_Date, "dd/mm/yy")
dtDueDateUS = Format(Me.tbxRespond_By_Date, "mm/dd/yy")

I then pass the dtDueDateUS into my SQL as follows:

DoCmd.RunSQL (GetSQLString(604, lgItemId, dtDueDateUS)

This calls the GetSQLString function, which has a case statement (lgItemId = varInput1 and dtDueDateUS = varInput3):

Case 604 
strSQL = "INSERT INTO tblDiary (Comms_FK,Respond_By_Date) SELECT varInput1 & ", #" & varInput3 & "# FROM tblMember"

Once the data has been stored in the table, the dates are interpreted by your locale, therefore when you query the table (outside of VBA) the format will be correct.

Guess everyone has different ways of achieving this - I'd be interested to see any other ways.