Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access 2003 date confusion

Posted on 2007-11-13
11
Medium Priority
?
473 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

636 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