Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

Access 2003 date confusion

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
causewaybay
Asked:
causewaybay
  • 6
  • 3
  • 2
1 Solution
 
MrXmasCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try  using Format(CDate([YourDateField]), "mm-dd-yyyy")

basically using CDate to wrap you field.

mx
0
 
causewaybayAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
MrXmasCommented:
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
 
causewaybayAuthor Commented:
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
 
causewaybayAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
causewaybayAuthor Commented:
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
 
causewaybayAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
""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
 
causewaybayAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now