More Date/Time fun

[Bill Pugh]

I've got a field in a query that is pulling the Date/Time from a table in the db. I've added another field in my query ...
Tran_Datetime: Format([dbo_t_PMCS_History]![Tran_Datetime],"mm/dd/yyyy")
to format the Date/Time into text so I can use the Date()-1 command.

Problem is ... the Date/time in the database is like 1/1/2006. There is no leading 0 on the month or day. Now when I use the format function with "mm/dd/yyyy" the date() command will only work when the Date/Time is has two digits in the mm/dd fields. Hope this makes sense

 

[neileg]

Access stores the date as a number, the format is not important. The only problem you tend to get is that SQL always wants the time in US format.

So if you are using Date()-1 as a criterion in your query, you should have a problem with your date, providing that [dbo_t_PMCS_History].[Tran_Datetime] is a date/time datatype.

Actually, I don't understand why you have the ! in there. It should be a fullstop.

 

[mtairhead]

Is there a way to put chagne the format of a date in a query to have leading zeros anyway?

4/2/2006

I need to appear as:
04/02/2006

I realize that Access doesn't care, but the software that I'll be inputting the Access outputs does.

~Andrew

 

[raskew]

Hi -

Think there may be a misunderstanding re dates and the Date() function.

the date() command will only work when the Date/Time is has two digits in the mm/dd fields

The date() function returns the current system date, e.g. (Today is 9-Aug-06): from the debug (immediate) window:

   ? date()
   8/9/06
 
   ? date()-1
   8/8/06

That's all the Date() function does for you--it gives you a point of reference. If you think it does something else, please check the Help File -- you are seriously lost here! The Now() function returns both the current system date and time, e.g.:

   ? now()
    8/9/06 6:51:30 PM

A Date/Time data type is stored as a double-precision, floating-point number (up to 15 decimal places).

The integer portion of the double-precision number represents the date, which in Access is the number of days since 30-Dec-1899. - The decimal portion represents the time as a portion of a whole day.

To see how dates are stored, try this:

   ? cdbl(date())
   38938 
   ? cdbl(now())
    38938.7886921296

If you want to display a date currently in proper date/time data format you can use the format function, e.g.:

   ? format(date(), "mm/dd/yyyy")
   08/09/2006

...and that works just dandy, but your output has now become a string, and you've lost the functionality of the built-in date-related VBA functions. So, you only want to use that in the 'display' portion of your routine -- not in the calculating portion.

Suggestions:
1) Check out these MSKB posts re dates:
http://support.microsoft.com/support/kb/articles/Q130/5/14.ASP
http://support.microsoft.com/support/kb/articles/Q136/1/27.ASP
2) Dump the mindset of treating dates/times as strings. That's not how they are processed in Access and if you insist on going that route, I'd call it 'a self-inflicted wound'.

HTH - Bob