Solved

Access 2003 date confusion

Posted on 2007-11-13
11
461 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

832 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