Date Format when Exporting

  • Thread starter Thread starter Chris
  • Start date Start date
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.
 
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)
 
Try using the DateValue function in your query instead;

WithEffectFromDate: DateValue([WEFDate])
 
Try using the DateValue function in your query instead;

WithEffectFromDate: DateValue([WEFDate])
Click to expand...

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.
 
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])
Click to expand...

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.
Click to expand...
 
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.
Click to expand...

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.
 
Back
Top