Exporting Date includes 0:00:00 - Don't Want That!

M

magmike

My query includes a date field (namely named 'Date') which, when exported,
includes the data, plus the addition of a null time (0:00:00). My table
field is set as a Date/Time field, and is formatted for just the date
(YYYY-MM-DD). If I switch the table field to a text field, this allows me to
just export the field data, but then I have no control over it's formatting
(and it returns as MM/DD/YYYY).

I tried the Int(Date) command (seen in other post with same problem), but it
returns a long integer. I tried the DatePart(Date) command, but can only get
it to include part of the date. When I try formatting the command for the
whole date, I get an error.

Has anybody experienced this before and found a solution?

Thanks,

Mike
 
R

Rick Brandt

My query includes a date field (namely named 'Date') which, when
exported, includes the data, plus the addition of a null time
(0:00:00). My table field is set as a Date/Time field, and is
formatted for just the date (YYYY-MM-DD). If I switch the table field
to a text field, this allows me to just export the field data, but
then I have no control over it's formatting (and it returns as
MM/DD/YYYY).
I tried the Int(Date) command (seen in other post with same problem),
but it returns a long integer. I tried the DatePart(Date) command,
but can only get it to include part of the date. When I try
formatting the command for the whole date, I get an error.

Has anybody experienced this before and found a solution?
Click to expand...

Format properties used in a table will not affect the data that is exported.
Create a query and in the query use the Format() function to format the date
the way you want and then export the query instead of the table.
 
M

magmike

I'm not sure where I would use that, but under the properties of the Date
query field, I set yyyy-mm-dd in the format field of the properties.
However, that works for the visual results inside the database, but still
wants to export the empty time.
 
M

magmike

How do I use the Format function? In the Criteria field? I'm not writing raw
SQL here. I'm using the Query Design View.

PS - I AM exporting from the query.
 
R

Rick Brandt

How do I use the Format function? In the Criteria field? I'm not
writing raw SQL here. I'm using the Query Design View.

PS - I AM exporting from the query.
Click to expand...

In your query where you currently have the name of your date field [Date] (which
is a bad name for a field BTW since that is a reserved word) replace that
with...

Format([Date], "YYYY-MM-DD")
 
Top