Date Formats & Display

[Simon Harris]

Hi All,

I'm having problems with date formats, on a report, they always seem to be
displayed as mm/dd/yyyy (US Format) what I need is British format
(dd/mm/yyyy). It works Ok, when the day is over 12. (As expected)

- Regional settings OK
- Table column datatype is Date/Time
- Table column format is short date, which on the drop down shows as
dd/mm/yyyy
- Report field formatted as short date

I've also tried format(DateField,"dd/mm/yyyy") which strangly makes no
difference!

My test code below returns the same value for each MsgBox, both with the
date as mm/dd/yyyy

Private Sub Command1_Click()
Dim Conn As DAO.Database
Dim strSQL As String
Dim RSTest As DAO.Recordset
Set Conn = CurrentDb
strSQL = "select reading_date from qry_invoices where idinvoice = 805"
Set RSTest = Conn.OpenRecordset(strSQL)
MsgBox (RSTest("reading_date"))
MsgBox (Format(RSTest("reading_date"), "dd/mm/yyyy"))
End Sub

But...this code seems to work AOK, in fact both msgbox's display the correct
date format:

Dim aDate As Date
aDate = CDate("01/07/2006")
MsgBox (aDate)
MsgBox (Format(aDate, "dd/mm/yyyy"))

Do I need to format the date on the way into the database? As I understand
it, date values are stored as:

"The date is not "stored" in ANY format: it's stored as a double float
count of days and fractions of a day (times) since midnight, December
30, 1899."
(Thanks John Vinson for the info on a reply to an earlier thread!)

I dont geddit!! Help! (Please!)

Cheers ;-)
Simon.

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!

 

[Simon Harris]

I should mention that when I open the table, the date is also displayed
incorrectly in there. e.g. 01/07/2006 (dd/mm/yyyy) is displayed as
07/01/2006 (mm/dd/yyyy)

Simon.

 

[Simon Harris]

And I've tried formatting the date within my insert SQL as follows:

....#" & Format(CDate(Me.comboReadingDate.Value), "dd/mm/yyyy") & "#...

 

[RoyVidar]

And I've tried formatting the date within my insert SQL as follows:

...#" & Format(CDate(Me.comboReadingDate.Value), "dd/mm/yyyy") &
"#...

In SQL strings, it need to be US, ISO or other unambiguous format
(ISO 8601 below)

....#" & Format(CDate(Me.comboReadingDate.Value), "yyyy-mm-dd") &
"#...

 

[Allen Browne]

Simon, open the Windows Control Panel | Regional Settings.
Are your settings correct there?
Is Short Date defined as dd/mm/yyyy?

If so, Access should interpret and display your entry correctly, unless you
explicitly formatted it (in the Format property of the field in the table,
or the Format property of the text box on your form/report.)

However, when you build SQL statements, you must use the m/d/y format, not
your own regional settings. Presumably Microsoft programmed it this way so
that a line of code will work anywhere in the same way, regardless of where
it is executed.

For more info, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The way you describe the dates as behaving, it may be that the date values
are actually stored wrongly. To test this, open the Immediate Window
(Ctrl+G)< and use DLookup() on a field that returns what you expect to be 1
July 2006. Wrap the whole expression CDbl(), e.g.
? CDbl(DLookup("MyDate", "MyTable", "ID = 999"))
If it is actually July 1, you should see the answer 38899. If you see
38724, the stored date has the value 7 January 2006.

 

[Simon Harris]

Allen/Roy,

Thank You! Since changing the SQL so that the date is in mm/dd/yyyy
format, the correct date ends up in the DB and all my calculations work as
expected....WOOO!

Cheers!
Simon.