Inserting a date from a variable (2000 (9.0.4402 SR-1))

[Niven]

I'm having a problem with the way dates are stored when inserting into a date field from a variable.

For instance the date field has been set up as a UK format, dd/mm/yyyy, but when I use a variable which has been set to 05/07/2004, the date when viewed has been switched to 07/05/2004. I'm somewhat puzzled, because if I have a date which is say 19/06/2004 it inserts and views correctly. Also if I manually type this in as 06/19/2004 the field switches it round to what it should be - 19/06/2004.

My regional settings in the control panel are set to English (United Kingdom)

Does anyone have any pointers as to what's going on, or any settings I'm missing?

Cheers,

Niven

[HansV]

This is due to (a) the way Windows handles dates and ([img]/forums/images/smilies/cool.gif[/img] the US-centricity of VBA.

If you enter a date such as 19/06/2004 or 06/19/2004, it will always be interpreted correctly, whether you use UK or US date settings. If you enter the date the "wrong" way for your date system, Windows will correct it automatically, because there is only one way it can be seen as a valid date.

However, if you enter a date such as 05/07/2004, it is valid both in US and in UK settings, but with a different date. If you enter a literal date in VBA code, you must use US format:

Dim datSaleDate As Date
datSaleDate = #07/05/2004#

results in datSaleDate being set to the 5th of July. The same holds if you use dates in an SQL string:

strSQL = "SELECT * FROM tblSales WHERE SaleDate = #07/05/2004#"

If you have a date value on a form, it may be handled correctly in code, but it is wise to be explicit:

strSQL = "SELECT * FROM tblSales WHERE SaleDate = #" & Format(Forms!frmInput!txtSaleDate, "mm/dd/yyyy") & "#"

[Niven]

Cheers for that, thought I was going mad! <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

Niven