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

  • Thread starter Thread starter magmike
  • Start date Start date
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
 
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.
 
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.
 
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.
Click to expand...

As I said, the format properties are PRECISELY responsible for display within
the Access GUI they do not affect the actual data and therefore do not affect
what is exported. If you build a query you can use the format function to
transform your date into a string that has the format you want and then you
export the query instead of the table.
 
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.
 
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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formatting date gives bad results in query 2
Date format in query export. 4
Date() update format 5
Cdate funtion inconsistency 4
Date/time query 1
Date Function in query 3
query on date 1
Format DATETIME to Date in query on linked table 4

Back
Top