Remove time from date upon export to csv

[CW]

I cannot remove the time from the date when exporting invoice data to a csv
file.
I have followed previous advice and created a query based on my tblInvoices
table, in which I have formatted the InvDate field in the Design Grid as
follows:
Date: Format([tblInvoices.InvDate], "dd/mm/yyyy") (UK format)
In the Query, this is fine - just the date, no time shown.
I saved an export spec in the Advanced bit of the File/Export wizard.
But in the resulting csv, there are six zeroes representing the time!
However if I do a manual export by following exactly the same process but
not saving it as a spec, it works fine and the time characters do not appear.
There doesn't seem to be any other more detailed option in the Advanced
window, to fine tune this. So why does it fail to exclude the time when I use
the saved export spec?
Many thanks
CW

 

[Ken Snell \(MVP\)]

Saving an export specification shouldn't change the exported format, but
it's possible that your use of Date as the field's alias is a "clue" to Jet
that you want to export a date with the time. Try changing the alias to
something else, such as InvoiceDate.

 

[John W. Vinson]

I saved an export spec in the Advanced bit of the File/Export wizard.
But in the resulting csv, there are six zeroes representing the time!
However if I do a manual export by following exactly the same process but
not saving it as a spec, it works fine and the time characters do not appear.
There doesn't seem to be any other more detailed option in the Advanced
window, to fine tune this. So why does it fail to exclude the time when I use
the saved export spec?

Probably because the saved spec was based on the Table, rather than on the
Query. Try recreating it based on the query.

Also change the name Date: on the query definition; that's a reserved word for
the builtin Date() function and Access may well get confused which you mean.

John W. Vinson [MVP]

 

[CW]

Thanks John and Ken - I have changed the fieldname to InvoiceDate and
modified the export spec to reflect this new name. Sadly, no improvement - I
still get the time shown in the csv as 6 zeros!

 

[Albert D. Kallal]

Thanks John and Ken - I have changed the fieldname to InvoiceDate and
modified the export spec to reflect this new name. Sadly, no improvement -
I
still get the time shown in the csv as 6 zeros!

dump the use of the export spec until you get this working. I am un-able
reproduce your problem...

I suspect you have your export spec set as "date" for the export..and you
only need it to set it as a string, since this is just a export after
all....

Force it as a string in your spec, or dump the use of the spec.....

 

[CW]

I'm not aware of having set the export spec to anything (i.e. date or string)
- there didn't seem to be any way to do this - how would I "force it" to be a
string as you suggest?
Thanks
CW

 

[Ken Snell \(MVP\)]

How are you exporting the query and using the export specification? Perhaps
the syntax for that export code is not correct, and your specification is
not being used at all?

--

Ken Snell
<MS ACCESS MVP>

 

[Albert D. Kallal]

I'm not aware of having set the export spec to anything (i.e. date or
string)
- there didn't seem to be any way to do this - how would I "force it" to
be a
string as you suggest?

In your format command, you could add a cstr() command to *force* the result
as a string.

That way, the delimiters, and date settings in the spec WILL BE ignored (the
m/d/y settings, and also stuff like the check box to export as a 4 digit
year).

As mentioned, if you don't use a spec, then the date settings for export in
the spec ARE ignored, but, then your international date settings from your
control panel *may* be an issue. So, I am suggesting EITHER WAY (using a
spec, or NOT using a spec) to force the result as a string. Therefore we
simply will by-pass any issues of date formatting that "windows" date
settings might effect this, and we also by-pass any settings that ms-access
(in the spec) for dates also...

eg:

cstr(format([dateField],"MM/dd/yyyy"))

 

[CW]

Ken -
I'm using a command btn that fires a macro set up as follows:
Action: TransferText
Transfer Type: Export Delimited
Spec Name: InvoiceExport Export Specification
Table Name: qryInvoiceTransfer
File Name: .....\My Documents\InvoiceExport.csv
Has Field Names: No
I hope that's the info you need, and many thanks for your continued
assistance!
CW

 

[Ken Snell \(MVP\)]

Your macro's setup looks correct. I'm at a loss for why you're seeing this,
other than Albert Kallal's most recent suggestion about explicitly casting
the output as a string value. See his post elsethread.

--

Ken Snell
<MS ACCESS MVP>