Date format wrong

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access 2000 program that reads the date in d/m/yy instead of m/d/yy
on one customer's pc.

The program is running Access 2000 runtime on a WinXP Prof. OS with Office
2000 Small Business installed. The appl first opens to a window that contains
the MSCal activex control. The control is set to default to the current date
on open (using the Now function); however, it opens to the wrong date. i.e.
Today is 11/10/04. The calander opens set to 10/11/04.

Then when the reports are run, the data on the report shows the dates in
d/m/yy format also but the dates are not being read correctly. i.e. I select
today's date on the calander for the date range of data to report. It tells
me there are no records. However, if I select the date range 1/1/04 thru
today, the report displays the data but data for today shows 10/11/04 on the
report.

I have checked the regional settings on the pc and everything looks ok
there. Any suggestions where to look next?
 
Unless you hard-coded the Format property of the field in the table or the
Format propery of the text box on the form, the fact that dates display as
d/m/y indiates the issue is with the Regional Setting the the Windows
Control Panel on the user's machine.

If you have unbound controls on a form, set their Format to Short Date so
Access recognises them as dates.

If you have calculated date fields in a query, typecast with CVDate() so JET
recognises them.

If you have literal dates in SQL strings, Where Conditions, Criteria
arguments, etc, explicitly format them m/d/y, as explained in this article:
http://members.iinet.net.au/~allenbrowne/ser-36.html

You could try using Date() instead of Now() to initialize your calendar
control, or if it is uncooperative you could replace it with this simple
little Access form and avoid the Reference and versioning problems of the
calendare control:
http://members.iinet.net.au/~allenbrowne/ser-51.html
 
Allen Browne said:
Unless you hard-coded the Format property of the field in the table or the
Format propery of the text box on the form, the fact that dates display as
d/m/y indiates the issue is with the Regional Setting the the Windows
Control Panel on the user's machine.
Click to expand...

No hard-coded Format properties in the tables or on the report. The Regional
Settings looks correct. Is it possible a dll is corrupt? If so, which one and
how do I tell?

If the Regional Settings were set to display dates in d/m/yy wouldn't the
dates still be read correctly? i.e. Wouldn't the system know that the date
5/11/04 is still Nov. 5 instead of May 11? That isn't happening on this
particular customer's pc.
If you have unbound controls on a form, set their Format to Short Date so
Access recognises them as dates.
Click to expand...
No unbound controls on the report.
If you have calculated date fields in a query, typecast with CVDate() so JET
recognises them.
Click to expand...
No calculated date fields in queries.
If you have literal dates in SQL strings, Where Conditions, Criteria
arguments, etc, explicitly format them m/d/y, as explained in this article:
http://members.iinet.net.au/~allenbrowne/ser-36.html
Click to expand...
I already check the locale setting before initializing the SQL criteria
statement. If it is not English(US), I force the date values into US format
(m/d/yy).
You could try using Date() instead of Now() to initialize your calendar
control, or if it is uncooperative you could replace it with this simple
little Access form and avoid the Reference and versioning problems of the
calendare control:
http://members.iinet.net.au/~allenbrowne/ser-51.html
Click to expand...
I will try substituting Date() for Now() but that will only affect the form
that displays the Calander. How does that affect the report output, which
also displays the date in d/m/yy?
 
Sounds like you understand most of this but a couple of pointers in-line.


Jean said:
No hard-coded Format properties in the tables or on the report. The
Regional
Settings looks correct. Is it possible a dll is corrupt? If so, which one
and
how do I tell?
Click to expand...

I doubt that. You might want to check that msjet40.dll is 4.0.8xxx.0, but
even an earlier one should not have these issues AFAIK.
If the Regional Settings were set to display dates in d/m/yy wouldn't the
dates still be read correctly? i.e. Wouldn't the system know that the date
5/11/04 is still Nov. 5 instead of May 11? That isn't happening on this
particular customer's pc.
Click to expand...

For the last 11 years, I've been developing in a country with d/m/y dates.
If I enter:
5/11/04
in the interface (e.g. a form, directly into a table, or in the Criteria row
in query design), Access does interpret that to mean 5th November. However
if make the same entry in code (e.g. VBA, SQL view of a query, Criteria of
DLookup()), then it is interpreted as May 11.

If I write:
CDate("5/11/04")
then Access interprets it as 5 Nov.
However:
CDate(#5/11/04#)
is interpreted as 11 May.
I will try substituting Date() for Now() but that will only affect the
form
that displays the Calander. How does that affect the report output, which
also displays the date in d/m/yy?
Click to expand...

I guess I was wondering if the value had made it correctly into the table,
or if it had been misinterpreted when assigned.
 
Thank you for the response.

You have explained some things that I didn't know. It looks like I may need
to check my code more thoroughly. Although, just FYI the program was designed
in English(US) locale and the customer with the problem is also using that
locale.

However, I'm still not understanding why anything in my program would cause
the Calander control to display the wrong date OnOpen. The form opens when
selected from a custom menu option. The form is unbound. It contains 3 groups
of check boxes, 2 Calander controls, an unbound combo box with a string
value, and a command button. The Form_Load event sets both Calander controls
to the current date with the Now() function. That's it. Nothing else happens
 
Jean I don't use the calendar control because it is subject to breaking
(references and versioning problems), so I can't answer that from
experience.

Could you experiment by using the Immediate window to test its value, assign
a different value directly, and see what's going on?
 
You *only* have the runtime version?

If you do not have a full version of Access, you may need to buy one. The
alternative would be to get some help from whoever created this application
for you, or someone else who can open it and track it down for you.
 
I created the application. What I meant in my previous posting is that the
customer's pc where this application is installed only has the runtime
version. This application is installed on numerous pcs but only the one
customer is having this problem. How do I troubleshoot on the problem pc if
they only have runtime?

FYI - I think the initial problem has been resolved. Although, I'm not sure
what caused the problem in the first place. However, since I could not find
an obvious cause, I previously attempted to "fool" windows into thinking I
made changes to the regional options by reselecting the current locale. Then
I rebooted the pc. The problem seems to have disappeared. I'm not confident
the problem is gone but it appears to be for now.
 
It's Baaacccckkk!

I received a call yesterday from the customer stating that the date issue
reappeared on 12/1. I'm at a loss on where to look next.

A couple of things I have noticed as of yesterday 12/6:

The Now function causes the Calendar control to default to June 12. (6/12
instead of 12/6)

The date/time field in the detail portion of the report is defined as
'General' format in the underlying table and defined as 'Short Date' format
on the report so that only the date portion is displayed. The date displays
as 6/12/04.

However, in a subtotal footer field the date/time field displayed is a
result of a calculated field using the DateValue function on the result of
the DateAdd function and defined as 'Short Date' on the report. This field is
based on the same field as the detail portion of the report. This field
displays as 12/6/04.

If I search for records with a date of Dec. 6, 2004, no records are found.
If I search for records from Dec 1, 2004 to Dec 31, 2004, records for
11/12/04 are found. These are actually records for Nov 12 and they are
displayed in the detail of the report as 11/12/04.

I also pulled the backend .mdb from the customer's pc to my pc to see the
data actually in the tables. Everything looks like it should.

Help. This is over my head and I really don't know where to go from here. If
you would like to communicate directly with me please send an email to Jean
at GoTechknow dot com.
 
Hi Jean,

I am sorry to hear that :-(

From your descriptions, I understood that it works well on your side, but
this issue will only occur in your customer's side. Have I understood
correctly? If so, I am afraid it will also hard for me to make a reproduce
on my side.

Anyway, it might be computer configuration issue instead of a programmatic
issue so that please check the Regional and Language Options in Control
Panel, make sure you are doing the same configurations as your customer do.
Provide us more information about your customer's scenario.

BTW, Have you remember anything was done before you resolve this issue in
the first time?


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
 
After more troubleshooting on the customer's pc I have verified that the
issue is with the OS. Here is a summary of what I found:

The Regional settings show the short date format as mm/dd/yyyy. However,
running the Date command from a DOS prompt shows 12/7/04 with a prompt to
enter the date as dd-mm-yy (in other words the Regional settings and the DOS
settings do not match). I changed the Regional setting to mm/dd/yy (2 digit
year versus 4 digit year) and the problem disappeared. I then changed the
Regional setting back to the original mm/dd/yyyy and everything was still OK.

It appears that making any change (or at least making the system think I
made a change) to the Regional settings causes the system to reinitialize the
settings and it puts everything back in sync. The last time this happened and
the problem disappeared, I had reselected the current locale and saved the
changes.

I have posted a request for help on one of the Windows Development
newsgroups but so far I have not received a response. Any idea where this
Windows problem should be posted to get help?
 
Hi Jean,

Thanks for the prompt updates!

Generally speaking, we will response within 24 hours and we are not
monitoring all the newsgroups. Here is the list for all MSDN Managed
Newsgroup

Welcome to MSDN Managed Discussion Groups
http://msdn.microsoft.com/newsgroups/managed/

Keep me notified whenever there is anything more I could do to help you on
this thread :-)


Sincerely yours,

Michael Cheng

Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
 
Back
Top