incorrect date display

[rightcoast]

I'm developing a database in Access 2002. I am in the UK, and the
regional settings are correct for the date format, etc. The dates are
stored correctly in tables, and displayed correctly on forms.
However, when displaying a date on a report, the format is incorrect.
For example, in a report control with a format of medium date,
2/10/08 is displayed as 10-Feb-2008 instead of 02-Oct-2008. No matter
what format I apply to the control, the date is always displayed as
some version of February 10th instead of October 2nd. This is driving
me crazy...can anyone let me know how to fix this? Thanks.

 

[Allen Browne]

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article (based on 15 years developing in Access in a dmy country)
describes how to avoid the 3 cases where Access will misunderstand your
dates.

 

[rightcoast]

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

The article (based on 15 years developing in Access in a dmy country)
describes how to avoid the 3 cases where Access will misunderstand your
dates.

Thanks - I had come across your link earlier in my searching, but
unless I'm missing something, it does not address reports. I have
confirmed that the regional settings are correct, and as mentioned,
the dates are displayed correctly except on reports. The control in
question is a bound control on the report, but no matter what format I
apply to it, it will not display correctly - frustrating!! Do I
actually have to go to the bother of addressing it somehow in the
detail_format event?

 

[Allen Browne]

Okay, we have to track down the point at which it went wrong.

The report control is bound to a field from a query.
Is this a calculated field or a field in a table?
If so, open the table in design view. What is the Data type of this field?
Assuming it is a Date/Time field named Date1 in Table1:
a) Create a query that uses this table.
b) In the Criteria row under this field, enter:
Is Not Null
c) Type this expression into a fresh column in the Field row:
CDbl([Date1])
If it is actually 10th February, you should see 39488.
If it is 2nd October, you should see 39723.

If it is a Text field in the table, all bets are off.

If it is a calculated field in a query, wrap the calculation in CVDate().

Let us know how you go identifying where it went wrong.

 

[rightcoast]

Okay, we have to track down the point at which it went wrong.

The report control is bound to a field from a query.
Is this a calculated field or a field in a table?
If so, open the table in design view. What is the Data type of this field?
Assuming it is a Date/Time field named Date1 in Table1:
a) Create a query that uses this table.
b) In the Criteria row under this field, enter:
Is Not Null
c) Type this expression into a fresh column in the Field row:
CDbl([Date1])
If it is actually 10th February, you should see 39488.
If it is 2nd October, you should see 39723.

If it is a Text field in the table, all bets are off.

If it is a calculated field in a query, wrap the calculation in CVDate().

Let us know how you go identifying where it went wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Thanks - I had come across your link earlier in my searching, but
unless I'm missing something, it does not address reports. I have
confirmed that the regional settings are correct, and as mentioned,
the dates are displayed correctly except on reports. The control in
question is a bound control on the report, but no matter what format I
apply to it, it will not display correctly - frustrating!! Do I
actually have to go to the bother of addressing it somehow in the
detail_format event?

Ugh - my apologies. I overlooked the very obvious fact (forest for
the trees...) of how the date is getting into the table - via code.
So of course your link does address this. Formatting it correctly in
code resolved the problem. I've been developing for many years, but
those were all in the US, so hopefully there aren't any other little
'gotchas' waiting for me here in the UK! Thanks so much for your help.