Solved

Access 2003 date confusion

Posted on 2007-11-13
11
460 Views
Last Modified: 2013-11-29
I have a MS Access 2003 database with sales date formatted as short date (99/99/0000;0).  We enter the date mostly by Ctrl +;. Certainly there are times we manually enter mm/dd/yyyy.  All the same the date is formatted  corrrectly, say: 11/13/2007.  The database run smoothly since 2005 ; and has now accumulated over 12000 records.  Recently we found a lot of records showing a funny date format: 0/29/2007.  I first though this is an human error.  But the 0 month appears in all 2005, 2006 and 2007 between October and November when I sort ascendently.  Whereas, the databases I copied on cd's before March 2007 do not show this funny date record.  What could have gone wrong?  The database is used between 2 computers in a network.  One computer was replaced by a new Dell with XP Home  this spring and we have some other date confusion since then:  although the other computer with XP Professional shows date as formatted: mm/dd/yyyy.  The new Dell will show dd/mm/yyyy.  Could the new computer a culprit?  Or is it an Access issue?  Please shed me some light!
0
Comment
Question by:causewaybay
  • 6
  • 3
  • 2
11 Comments
 
LVL 4

Expert Comment

by:MrXmas
ID: 20277622
CausewayBay,

I'm curious about what's actually stored in these weird date fields.

Run this query and let us see the result for a few of the affected rows:

SELECT MyDateField, Format(MyDateField,"MMMM DD YYYY HH:NN") AS MyFormattedDate FROM MyProblemTable;

Thanks,

--Jim Christmas
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 20277647
Try  using Format(CDate([YourDateField]), "mm-dd-yyyy")

basically using CDate to wrap you field.

mx
0
 

Author Comment

by:causewaybay
ID: 20278804
MrXmas:
How may I run the query?  My knowledge is quite fundamental.  I'm at the level of running 'select a field from a table where x equals y...  I try to run your query by bringing up the problem table and select the date field and type: Format(MyDateField,"MMMM DD YYYY HH:NN") in the criterion row.  It doesn't work. I went to view, sql and type the string. It doesn't work neither.  please step me through.  Thank you!
0
 
LVL 4

Expert Comment

by:MrXmas
ID: 20279594
CausewayBay,

You'll want to open a new query window and add the table that's having this problem (if this is multiple tables, just pick one of them).  Then double click the date field that is showing bad data to bring it down to the design grid.  In the next column of the grid add in this line:

MyFormattedDate:Format(MyDateField,"MMMM DD YYYY HH:NN")

You'll need to replace the "MyDateField" with the actual name of the field that holds your date (the "MyFormattedDate" doesn't need to change - it can say just about anything).

Then run that query.  If you would be so kind as to copy and paste a few rows where you're seeing the issue, it'll help to see what's actually stored in the field.

Thanks,

--Jim Christmas
0
 

Author Comment

by:causewaybay
ID: 20279682
MrXmas:
I sent you the previous message 'cause I kept recieving data mismatch message so I though I may be wrong in querring.  Eventually I modified the Format function as this:  Format([DateSold],"mm/dd/yyyy") in the criteria row under date field in a new query base on the problem table.   All dates return the same (what were zero are still zero).  
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:causewaybay
ID: 20279817
DatabaseMX:
The CDate function get rid of all the zero months.  Now each entry has a sales date that make sense.  Please tell me how did it happen.  What would cause a zero month in the first place (other than typing mistake)? Has it something to do with the computer that set its time at dd/mm/yyyy.  How did the CDate wrap it back?  I'm asking such questions so I know how trustworthy are the sales date I gain back.  Also,  I don't want to  make the same mistake again.    Thank you!
0
 
LVL 75
ID: 20281483
"Please tell me how did it happen"

Really not sure.  What is the 'Data Type' of that field in the Table?  IF ... it's a Text field and not a Date/Time data type, that could explain this.

CDate() converts anything that '*could be a date* to a date.  Dates are stored internally as Double Precision numbers .... date part to the left of the decimal point, time part to the right of the decimal point.  Formatting has no effect on storing the date, only displaying of the date ... in a desired format.

mx

0
 

Author Comment

by:causewaybay
ID: 20284837
DatabaseMx:
the 'data type' of the field in the table is Date/Time.  Starting from day 1 I  use 'Format': short date; and the 'Input Mask' was defaulted to 99/99/0000;0;-.  I notice after running CDate, the 'Input Mask' changed to mm/dd/yyyy.  I first though the input Mask' is the culprit.  But the imput mask was defaulted to 99/99/0000;0;- since 2003 and the problem only appears in 2007.   I'm puzzled!  Any idea to avoid future confusion?  Thanks a lot!
0
 

Author Comment

by:causewaybay
ID: 20284925
Dear DatabaseMx:

I notice the new computer has a different system time setting.  The main computer with XP professional is using US time: mm/dd/yyyy so typing 02/11/07 will display: 2/11/2007.  But the new computer has a different setting: dd/mm/yyyy and will display the same typing this way: 11/2/2007.  Could this be the cause of 0 month?  If it is, I'm not worrying much as you said "Dates are stored internally as Double Precision numbers" so even the display is wrong we may still fix it - like by using CDate.  Please comment.  Thanks a lot!
0
 
LVL 75
ID: 20301178
""Dates are stored internally as Double Precision numbers" so even the display is wrong we may still fix it - like by using CDate.  Please comment."

Since you have a data type of Date/Time, then it *is* stored as double precision and always has been.

Formatting / displaying are not going to cause a zero month.  I don't know how that might have happened.

Sorry ...

mx
0
 

Author Comment

by:causewaybay
ID: 20306121
Dear databasemx:

Thank you for the explanation.  It helps! I have installed a little popup calendar (which is a form plus some modules I purchased at $15) to prevent human mistakes of confusing mm/dd/yy with dd/mm/yy.  And I feel confident that as the 'double presision' applys, I'll be able to redisply the month and year correctly in case the number turns weird

Have a nice weekend
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now