How to Keep Date Format on Export?

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

Guest

I am running a query on a database to pull a file number along witht the date
it was filed. I am exporting this to a comma dilimited text file.

The date field in my query comes in as mm/dd/yy, and I would like it to say
"dd of mmmm, yyyy." An example is with , 09/29/06 I want it to say "29 of
September, 2006". So I'm looking at design view of my queiry, and I go to
the properties of the date and have set the format the way I want, and it
shows up in the grid the way I want. My problem is, when I export the data
to the text file, the date in the text file shows as 9/29/2006 0:00:00.

How can I get it to show up the way I have it formatted in the text file? I
need the text file to be comma dilimited, I've tried checking the save
formatted box, but that doesn't work because it's no longer comma dilimited.
I'm not sure how or if I can do this. Please offer any suggestions you can.
I'm a little new to working with Access and am learning. Thanks
 
First of all, Thanks for your quick reply.
Ok in trying to do this, it was working for me at first, but then I started
to get an error when I try to run it.

Here is what I have entered in the Field box for the date I'm wanting to
format:
"FormattedDate: Format([EnteredDate],"mmddyyyy")"
After I finish typing this and move out of the field, the table seleced for
this date field in table selection disappears. Then I reselect the table and
try to run the query, and I get the error stated below.

"Syntax error (comma) in query expression
'dbo_CaseTabe.[Format(EnteredDate], "mmddyyyy")]'.


:( I don't know what I did, it was working at first.
 
FormattedDate: Format(dbo_CaseTabe.[EnteredDate],"mmddyyyy")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


adna said:
First of all, Thanks for your quick reply.
Ok in trying to do this, it was working for me at first, but then I
started
to get an error when I try to run it.

Here is what I have entered in the Field box for the date I'm wanting to
format:
"FormattedDate: Format([EnteredDate],"mmddyyyy")"
After I finish typing this and move out of the field, the table seleced
for
this date field in table selection disappears. Then I reselect the table
and
try to run the query, and I get the error stated below.

"Syntax error (comma) in query expression
'dbo_CaseTabe.[Format(EnteredDate], "mmddyyyy")]'.


:( I don't know what I did, it was working at first.

Douglas J. Steele said:
Don't use the Format property: use the Format function.
Click to expand...
Click to expand...
 
Here is what I have entered in the Field box for the date I'm wanting to
format:
"FormattedDate: Format([EnteredDate],"mmddyyyy")"
Click to expand...

Are you doing this in the Table (incorrect) or in a Query based on the
table (correct)?

For the format you described, you should be using

FormattedDate: Format([EnteredDate], "dd of mmmm, yyyy")


John W. Vinson[MVP]
 
:

Are you doing this in the Table (incorrect) or in a Query based on the
table (correct)?

For the format you described, you should be using

FormattedDate: Format([EnteredDate], "dd of mmmm, yyyy")


John W. Vinson[MVP]
Click to expand...


Thanks again for your replies.

I'm trying to do this in a query based on a table. I have tried both your
solutions and am still having problems. For some reason, it seems to have a
problem with the comma, at least that's what the error says.

It could also be something I'm doing wrong although I'm not sure what. When
I copy either of your lines directly into the 'field' box in the query, I
still get syntax errors. I find it really strange because when I first tried
formatting the date using the format function, it worked except it wasn't
showing just the way I wanted. After messing with the format some, it
completely ceased to work and I get that error. The only thing I changed was
that I removed the custom format from the format property of that field, but
even if I put that back it doesn't fix it. I'm not sure what I'm doing
wrong.
 
Well I finally got it to work. Somehow the problem was caused by the
criteria I had entered for >=#09/01/2006#. when the Criteria was removed,
the formatting worked.

However since I needed to be able to enter criteria, I just added the
EnteredDate field to the query a second time with the criteria set to be
<=#9/01/2006#, but had it hidden, and removed the criteria from the
EnteredDate with the format set.

Thanks

adna said:
:

Are you doing this in the Table (incorrect) or in a Query based on the
table (correct)?

For the format you described, you should be using

FormattedDate: Format([EnteredDate], "dd of mmmm, yyyy")


John W. Vinson[MVP]
Click to expand...


Thanks again for your replies.

I'm trying to do this in a query based on a table. I have tried both your
solutions and am still having problems. For some reason, it seems to have a
problem with the comma, at least that's what the error says.

It could also be something I'm doing wrong although I'm not sure what. When
I copy either of your lines directly into the 'field' box in the query, I
still get syntax errors. I find it really strange because when I first tried
formatting the date using the format function, it worked except it wasn't
showing just the way I wanted. After messing with the format some, it
completely ceased to work and I get that error. The only thing I changed was
that I removed the custom format from the format property of that field, but
even if I put that back it doesn't fix it. I'm not sure what I'm doing
wrong.
Click to expand...
 
Back
Top