Date Format when Exporting


C

Chris

I've created a database which outputs a .csv file. The .csv file has
(among others) two date fields which have to be in the format
dd/mm/yyyy. I've avoided the problem of Access exporting the time
along with the date by exporting from a select query, where the date
fields use the expression:

WithEffectFromDate: Format([WEFDate],"dd/mm/yyyy")

That's fine - except that if you open the .csv in Notepad, it shows
that the dates are enclosed with quotes:

,,,"29/01/2009","01/01/2099","EM", (etc.)

Other fields are text and need the quotes, but is there anything I can
do to stop the quotes appearing in the date fields? I've set the
format of the date fields in the query to be 'short date', but that
doesn't seem to make any difference.

Any ideas?

Chris.
 
Ad

Advertisements

W

Wayne-I-M

Format([WEFDate],"dd/mm/yyyy")
will give you text - hense the "" ""

Is there a reason why you can't just export WEFDate (assuming that its a
date field)
 
B

Beetle

Try using the DateValue function in your query instead;

WithEffectFromDate: DateValue([WEFDate])
 
C

Chris

Try using the DateValue function in your query instead;

WithEffectFromDate: DateValue([WEFDate])

Unfortunately, that still outputs the time as well - so although I
loose the quotes, I get:

29/1/2009 00:00:00

- and the field must only contain dd/mm/yyyy - 29/01/2009.

The same happens when I export WEFDate without specifying a format.

Surely there's an answer to this??

Chris.
 
B

Beetle

Hmmmm...

I've tried a few different ways but can't get the results you're
looking for. Seems to be an issue with the Export process itself.
If you pass the value as a date, the Export process will add the
time portion if there isn't one, if you pass it as text it will add the
quotes. You can choose the option to remove the text qualifier but
that will remove it for all text fields. I'm sure I'm not telling you
anything you don't already know but, basically, I don't have an
answer.

Maybe someone else will pick up on this thread with an answer.

--
_________

Sean Bailey


Chris said:
Try using the DateValue function in your query instead;

WithEffectFromDate: DateValue([WEFDate])

Unfortunately, that still outputs the time as well - so although I
loose the quotes, I get:

29/1/2009 00:00:00

- and the field must only contain dd/mm/yyyy - 29/01/2009.

The same happens when I export WEFDate without specifying a format.

Surely there's an answer to this??

Chris.
 
Ad

Advertisements

C

Chris

Hmmmm...

I've tried a few different ways but can't get the results you're
looking for. Seems to be an issue with the Export process itself.
If you pass the value as a date, the Export process will add the
time portion if there isn't one, if you pass it as text it will add the
quotes. You can choose the option to remove the text qualifier but
that will remove it for all text fields. I'm sure I'm not telling you
anything you don't already know but, basically, I don't have an
answer.

Maybe someone else will pick up on this thread with an answer.

Sean - thanks for your time, even if you didn't come up with the
solution! It will be interesting to see if anyone else has some
ideas. I'm sure I'm not the only person to have come up against this
Access "feature".

But I may just have to resign myself to accepting this as one of those
quirks that makes Access such a joy - that, and coming into contact
with helpful people like yourself and Wayne, of course!

Chris.
 
Ad

Advertisements


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

Top