Changing Date Format in Query to Export Data

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

Guest

I use a query to generate an "exported" file. This query should create all
output date fields as YYYYMMDD with no associated time (field will only
contain 8 char for date).
Table field is defined as: date/time with format: MM/DD/YYYY
Query field should be defined as: YYYYMMDD

What steps do i need to perform to enable this type of functionality in a
query. If requires steps outside of the query please outline.

thanks,
John
 
Hi John.

Have you tried formatting the Field in the Query? In the Design view of the
Query, right-click on your date field and select Properties to view the
Properties Window. For the Format property, enter yyyymmdd .

-Michael
 
I used the properties on this field; however, when I export the results into
a tab delimited file I get "19000101 000000" as a format for this field. I
do not want the time portion only the 8 char date formated as 19000101.
Also, the value is coming from a table called job with a field name of effdt.



Thanks for your response,
John
 
In that case, try wrapping your date field in the Format function. In other
words, instead of having just the fieldname "DateField" in the "Field" row of
your query design, use this instead:
FormattedDate: Format([DateField],"yyyymmdd")

-Michael
 
I use a query to generate an "exported" file. This query should create all
output date fields as YYYYMMDD with no associated time (field will only
contain 8 char for date).
Table field is defined as: date/time with format: MM/DD/YYYY
Query field should be defined as: YYYYMMDD

What steps do i need to perform to enable this type of functionality in a
query. If requires steps outside of the query please outline.
Click to expand...

You'll need a calculated field in the Query, e.g.

ExpDate: Format([datefield], "yyyymmdd")

This will be a Text type field and can be exported in place of the
date/time field itself.

John W. Vinson[MVP]
 
I tried the fieldname in brackets;however, the query is now prompting me for
the entry via a dialog screen. I want the query to use a table's values
instead.
Table: Job Field: effdt
How would i format the function call:
FormattedDate: Format([?????],"yyyymmdd")

thanks and again I appreciate the quick turnaround.

Michael H said:
In that case, try wrapping your date field in the Format function. In other
words, instead of having just the fieldname "DateField" in the "Field" row of
your query design, use this instead:
FormattedDate: Format([DateField],"yyyymmdd")

-Michael



John said:
I used the properties on this field; however, when I export the results into
a tab delimited file I get "19000101 000000" as a format for this field. I
do not want the time portion only the 8 char date formated as 19000101.
Also, the value is coming from a table called job with a field name of effdt.



Thanks for your response,
John
Click to expand...
Click to expand...
 
Thanks for your responses, please disregard my last email as it was an error
on my part.

john


Michael H said:
In that case, try wrapping your date field in the Format function. In other
words, instead of having just the fieldname "DateField" in the "Field" row of
your query design, use this instead:
FormattedDate: Format([DateField],"yyyymmdd")

-Michael



John said:
I used the properties on this field; however, when I export the results into
a tab delimited file I get "19000101 000000" as a format for this field. I
do not want the time portion only the 8 char date formated as 19000101.
Also, the value is coming from a table called job with a field name of effdt.



Thanks for your response,
John
Click to expand...
Click to expand...
 
John, thanks for your response. Both you and Michael were able to answer my
question.

Regards,
John

John Vinson said:
I use a query to generate an "exported" file. This query should create all
output date fields as YYYYMMDD with no associated time (field will only
contain 8 char for date).
Table field is defined as: date/time with format: MM/DD/YYYY
Query field should be defined as: YYYYMMDD

What steps do i need to perform to enable this type of functionality in a
query. If requires steps outside of the query please outline.
Click to expand...

You'll need a calculated field in the Query, e.g.

ExpDate: Format([datefield], "yyyymmdd")

This will be a Text type field and can be exported in place of the
date/time field itself.

John W. Vinson[MVP]
Click to expand...
 
Mr Vinson you are a hero - Iv'e been living with this problem for five years
exporting a text file for mailmerge. Thank you so much - we wont have to go
through mail deleting times anymore.

John Vinson said:
I use a query to generate an "exported" file. This query should create all
output date fields as YYYYMMDD with no associated time (field will only
contain 8 char for date).
Table field is defined as: date/time with format: MM/DD/YYYY
Query field should be defined as: YYYYMMDD

What steps do i need to perform to enable this type of functionality in a
query. If requires steps outside of the query please outline.
Click to expand...

You'll need a calculated field in the Query, e.g.

ExpDate: Format([datefield], "yyyymmdd")

This will be a Text type field and can be exported in place of the
date/time field itself.

John W. Vinson[MVP]
Click to expand...
 
Back
Top