Truncated Dates!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anyone know why the following is happening:

I have a table with a date field, in short date format. It has been
formatted like this:

mm/dd/yyyy

I have it formatted this way so that there are always double digit value for
the month and day fields, ex. 01/05/2005 instead of 1/1/2005.

The problem that I am having is that when I query the data in this field
with a Left function, it always chops off the first zero in the month field.
I want my results to say 01, not 1/. Does anyone know how to fix this?
Thanks.
 
Instead of using Left(), use the Format function, e.g.:
Format([MyDateField], "mm")
 
Robert said:
Does anyone know why the following is happening:

I have a table with a date field, in short date format. It has been
formatted like this:

mm/dd/yyyy

I have it formatted this way so that there are always double digit
value for the month and day fields, ex. 01/05/2005 instead of
1/1/2005.

The problem that I am having is that when I query the data in this
field with a Left function, it always chops off the first zero in the
month field. I want my results to say 01, not 1/. Does anyone know
how to fix this? Thanks.
Click to expand...

If your query is applying the Left function directly to the field, like
this:

SELECT Left([YourDateField], 2) As DayOfMonth

then the field is being converted to text without regard to whatever
Format property you may have applied to the field itself. That's an
Access property that will be respected by Access objects like forms,
reports, and datasheets, but not by the database engine that is
processing the query.

If you just want to extract just the day of the month and show it as
text with a leading zero, you can use the Format *function* in the
query:

SELECT Format([YourDateField], "mm") As DayOfMonth

If you want some arbitrary substring of the full formatted date, you
have to use the Format function first and take a substring of that:

SELECT Left(Format([YourDateField], "mm/dd/yyyy"), 5) As MonthAndDay

However, for almost any purpose I can think of, it would be simpler just
to use the Format function alone.
 
Dirk Goldgar said:
If you just want to extract just the day of the month and show it as
text with a leading zero, you can use the Format *function* in the
query:

SELECT Format([YourDateField], "mm") As DayOfMonth
Click to expand...

Oops! Of course, that's the month being extracted, not the day of the
month. Need more coffee ...
 
Thanks a lot. This helped me a lot! :)
--
RSF


Dirk Goldgar said:
Dirk Goldgar said:
If you just want to extract just the day of the month and show it as
text with a leading zero, you can use the Format *function* in the
query:

SELECT Format([YourDateField], "mm") As DayOfMonth
Click to expand...

Oops! Of course, that's the month being extracted, not the day of the
month. Need more coffee ...

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top