Date Format Question Again

[paduanow]

I have a date field in a table. The default is Date(). InputMask
0000/99/99; Format is Short Date. Every record will have todays date.

The date must be in the format yyyy/dd/mm. I am output this table to a text
file and the date come out like 10/31/2009 0:00:00:

Is there a way to covert the date to the format I desire and not have the
time?

Thanks again

 

[Allen Browne]

You need to understand that the way the date is stored in the database and
the way it is displayed are 2 different things.

Internally, Access (JET) stores a date/time values as a fractional number,
where the integer part represents the date (Nov 1 is 40118), and the time is
a fraction of a day (noon = 0.5, 6am = 0.25, etc.) That's how it's stored,
regardless of what input mask or format you place on the field.

To export it the way you want, use the Format() function. In the Field row
in query design, enter:
Format([YourDateFieldNameHere], "yyyy\/mm\/dd")
The field in the query is then text, not date, so when you export the query,
you will get text in the format specified. (I've assumed you really do want
the month before the day in that string.)

IME, it's best not to use the Format or InputMask properties of a date/time
field in the table. Using a Format like Short Date has the disadvantage of
suppressing any time value that's present in the field so that what you see
is not what's really there. Using an Input Mask is frustrating for good data
entry operators who know they can enter most dates this year with only 3 or
4 keystrokes e.g.:
12 1

 

[John W. Vinson]

The date must be in the format yyyy/dd/mm. I am output this table to a text
file and the date come out like 10/31/2009 0:00:00:

Is there a way to covert the date to the format I desire and not have the
time?

Yes. Rather than ouputting the table itself, create a Query based on the
table. In a vacant field cell in the query put

ExpDate: Format([datefield], "yyyy/dd/mm")

and export that field rather than the date itself.

I must say that's a *very* odd date format... days between years and months,
and years first!? Where is that format used?