Strip 'time' from exported date/time data

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

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!!
 
Dlimey said:
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!!
Click to expand...

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.
 
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")
 
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.
 
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.
 
Pat said:
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.
Click to expand...

DateValue strips the time portion off, but if you export that you will still
have 00:00:00 shown in the exported data.
 
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

John Nurick said:
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!!
Click to expand...
Click to expand...
 
Back
Top