mm/dd/yyyy format

[BirdByte]

I run a query in Access for a report that has to be a comma delimited
text file. The text fields have to be in quotations, and the two date
fields have to be mm/dd/yyyy. When I run the query, the resulting table
is formatted correctly. However, when I export it to a text file, the
date fields are formatted as mm/dd/yyyy 00:00:00.

The underlying tables are formatted as short date, with a 99/99/9999;0;
mask (I tried all 0s in the mask as well). The query has been formatted
that same way, and also as Field1: format([BirthDate], "mm/dd/yyyy"). I
also tried Field1: format([BirthDate], "short date"). None of the
combinations held the date format when the file was exported.

Any ideas or workarounds to get rid of 00:00:00 in the text file will
be greatly appreciated. Thanks.

 

[BirdByte]

Sorry, I should have mentioned that I'm using Office 2000. I've also
tried 2003, but had the same problem.

 

[Tom Ellison]

Dear Bird:

There is possible confusion about the term "format".

The date/time values stored in the database are not formatted at all. They
are capable of representing both date and time.

Formatting occurs when data is displayed. You have some choices in this
regard. You may be looking at a default format.

When you export it as text, you can choose to format it as you which to see
it and store it.

Tom Ellison

 

[BirdByte]

I'm aware of this. When I export it, I choose to format it as comma
delimited, quotes surrounding the text fields, m/d/y, and leading zeros
in front of the date field. I have not seen a choice for mm/dd/yyyy or
mm/dd/yyyy 00:00:00. So how do I get rid of the zeros?

I've also tried exporting it as tab delimited, which gets rid of the
zeros, but also gets rid of the commas and quotations. I've then
imported that file back into Access and tried exporting again, but
STILL end up with the trailing time format.

 

[Tom Ellison]

Dear Bird:

Format(Date, "m/d/yyyy")

Tom Ellison

 

[j h]

I opened the table in design view, and tried this on the format line
for the date field and it converted itself to: "For"m"at"("d"ate,
m/d/yyyy)".
I assigned the specific field: Field1: format([BirthDate], "m/d/yyyy")
and had the same time formatting issue when I exported.
I downloaded and installed all the Office 2000 updates, and the problem
remained. I created a new database thinking there might be a corrupted
file and the problem carried over. I tried it in Office 2003 but had
the same problem. I know it can be done because I've seen the proper
fomatting in reports (although not consistantly). Thank you for your
suggestions so far. I will keep plugging away at it and if I find a
solution I will share it in case it happens to someone else.

 

[Douglas J Steele]

When setting the format property for a field or control, all you put is
m/d/yyyy. No Format, no field name, no parentheses.

 

[BirdByte]

That's the first thing I tried. I've also tried not formatting at all.
Same results. Thanks for the suggestion though. I'm trying everything
now on a fresh db to rule out corrupt entries.

 

[Rick Brandt]

That's the first thing I tried. I've also tried not formatting at all.
Same results. Thanks for the suggestion though. I'm trying everything
now on a fresh db to rule out corrupt entries.

And it really IS a DateTime field right? Not a Text field that just happens to
have date strings entered in it?

 

[BirdByte]

Yes, it's date/time, not text.