Help Exporting DATE values

  • Thread starter Thread starter SinCity
  • Start date Start date
S

SinCity

I have a table that has 5000 dates in the "date" column.
I need the date exported to a comma delimited file in this format...

March 10, 2007

But the date is exported like this in the text file...

3/10/2007 0:00:00

How can I fix this? By the way, changing the format of the date field in
the design view of the table doesn't seem to fix this. Help!

Thanks
 
Mybe there is a better way but,
when I need to export a date in a different way, I use a query to change the
format of the date, and then I export the query and in the import/export spec
I define the date field as text, that way it keeping the format I want, and
it doesn't change it back to date/time format
 
Hi Sincity


Write a query making the date field as follows

dateexport: Format([date],"mmmm d"", ""yyyy")

then export the query

Regards

Ian B
 
Write a query making the date field as follows
dateexport: Format([date],"mmmm d"", ""yyyy")

then export the query

Regards

Ian B


Ian,

I wonder if you might help me out a little more please? I'm a bit new at
creating queries. So far I created a query like this...

SELECT Names.*
FROM Names;
dateexport: Format([date],"mmmm d"", ""yyyy")

Obviously it didn't work giving me the error "Characters found after SQL
statement". What am I doing wrong?

SinCity
 
Hi SinCity,

Sorry did not know you were up to the sql statement therefore try this:

SELECT Names.*, Format([date],"mmmm dd"", ""yyyy") AS dateexport
FROM tblTransData;

ps don't forget to put the name of your field in [Date].

In addition the above statement will include your old date as well as the
new format date.

Regards

Ian



SinCity said:
Write a query making the date field as follows

dateexport: Format([date],"mmmm d"", ""yyyy")

then export the query

Regards

Ian B


Ian,

I wonder if you might help me out a little more please? I'm a bit new at
creating queries. So far I created a query like this...

SELECT Names.*
FROM Names;
dateexport: Format([date],"mmmm d"", ""yyyy")

Obviously it didn't work giving me the error "Characters found after SQL
statement". What am I doing wrong?

SinCity
 
Obviously it didn't work giving me the error "Characters found after SQL
statement". What am I doing wrong?

What Ian was suggesting was a calculated field to be typed into a vacant Field
cell in the query grid. The SQL equivalent would have the calculated field in
the SELECT clause:

SELECT Names.*, Format([date],"mmmm d"", ""yyyy") AS DateExport
FROM Names;

Note that if you have a table field named Date that you should change it: Date
is a reserved word for the builtin Date() function, and Access *will* get
confused.

John W. Vinson [MVP]
 
Sorry should read:-

SELECT Names.*, Format([date],"mmmm dd"", ""yyyy") AS dateexport
FROM Names;

Ian B said:
Hi SinCity,

Sorry did not know you were up to the sql statement therefore try this:

SELECT Names.*, Format([date],"mmmm dd"", ""yyyy") AS dateexport
FROM tblTransData;

ps don't forget to put the name of your field in [Date].

In addition the above statement will include your old date as well as the
new format date.

Regards

Ian



SinCity said:
Write a query making the date field as follows

dateexport: Format([date],"mmmm d"", ""yyyy")

then export the query

Regards

Ian B


Ian,

I wonder if you might help me out a little more please? I'm a bit new at
creating queries. So far I created a query like this...

SELECT Names.*
FROM Names;
dateexport: Format([date],"mmmm d"", ""yyyy")

Obviously it didn't work giving me the error "Characters found after SQL
statement". What am I doing wrong?

SinCity
 

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

Back
Top