Strip 'time' from exported date/time data

[Guest]

I know it is Saturday but I need this for Monday. I need to export Access
data to a comma delimited text file but with numbers and date not in 'text'
(no quotes). If I do it with a query or macro to 'format' the data it exports
as 'text' - if I export it as date/time I get the date and 00:00:00 added to
the actual date. HELP!!

 

[Rick Brandt]

I know it is Saturday but I need this for Monday. I need to export
Access data to a comma delimited text file but with numbers and date
not in 'text' (no quotes). If I do it with a query or macro to
'format' the data it exports as 'text' - if I export it as date/time
I get the date and 00:00:00 added to the actual date. HELP!!

I don't think you will be able to what you want with an Export. You will have
to use the file i/o functions in VBA to write the file yourself as you loop
through a RecordSet.

 

[Ken Snell [MVP]]

The " characters are omitted if you set the "Text Qualifier" property to
None in the Export wizard window. The date can be exported as just the date
(no time) if you export a query where you use a calculated field in place of
the real date field:

JustTheDate: Format([NameOfDateField], "mm\/dd\/yyyy")

 

[Pat Hartman]

Rather than using the Format() function to format the date, try using the
DateValue() function. It returns only the date part of the date/time field
but it returns it without changing its datatype. TimeValue() returns only
the time.

 

[John Nurick]

Use a query to format the date. When exporting it, set the Text
Qualifier option in the export wizard to {none}; this exports the data
without quotes.

If you need to have other text fields enclosed in quotes, add them in
calculated fields in the query, e.g.

QuotedTextField: """" & [TextField] & """"

If you are exporting under program control, export the query once
manually and use the text wizard to create an export specification
(click the Advanced button in the wizard). Then when the time comes pass
the name of the specification to the TransferText statement or macro
action.

 

[Rick Brandt]

Rather than using the Format() function to format the date, try using
the DateValue() function. It returns only the date part of the
date/time field but it returns it without changing its datatype. TimeValue()
returns only the time.

DateValue strips the time portion off, but if you export that you will still
have 00:00:00 shown in the exported data.

 

[Guest]

John,
GREAT!!!! I had already been all the places in the other postings but your's
was 'outside the box' and I had not thought of. You're a life saver and I
much apprecitate it. Just goes to show it is sometimes all about using what
you know in a creative way versus looking for a capability in the software
that may not exist. I had looked at using 'None' as a qualifier but since the
customer required Text fields be qualified with " I gave it up as a
non-player.
Thanks again,

dave

Use a query to format the date. When exporting it, set the Text
Qualifier option in the export wizard to {none}; this exports the data
without quotes.

If you need to have other text fields enclosed in quotes, add them in
calculated fields in the query, e.g.

QuotedTextField: """" & [TextField] & """"

If you are exporting under program control, export the query once
manually and use the text wizard to create an export specification
(click the Advanced button in the wizard). Then when the time comes pass
the name of the specification to the TransferText statement or macro
action.

I know it is Saturday but I need this for Monday. I need to export Access
data to a comma delimited text file but with numbers and date not in 'text'
(no quotes). If I do it with a query or macro to 'format' the data it exports
as 'text' - if I export it as date/time I get the date and 00:00:00 added to
the actual date. HELP!!