Solved

Access 2003 date confusion

Posted on 2007-11-13
11
462 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 - Microsoft MVP, Access and Data Platform) 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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
 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

809 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