Convert date field into a numeric or text field 1

[kiglid]

I have an Access table that I'm exporting to Excel. The date field needs to be in the format of yyyymmdd. I'm having 2 problems:

1. The field shows up in Excel as mm/dd/yyyy.

2. Really old dates (1900 or older) don't get exported and Access creates an ExportErrors table showing these records.

I've tried converting the field to a text type and then exporting, but then I don't get the format I need in Excel.

Would it be better to try and do the conversion to the correct format from within Excel, or can I do it in Access?

Thanks.

 

[PaulBricker]

You might try creating a query using your table and then converting your dates to numbers using the CDbl() function. In a new column you would put
MyDate:CDbl([DateField])
Then try importing them into a column in Excel that is formated to handle the dates the way you want. It should, in theory, work. It works when formatting the cells for mm/dd/yyyy

Paul

 

[kiglid]

Thanks, it works great!

 

[HARIL]

Hey I am importing a file and the dates show up as
20030527 and I am comparing it to the next value 05/27/2003 how can I change the 20030527 to show up like that in 1 cell , so I can compare them and return a true value if the dates match. Please help
Email : hpatel@na2.us.ml.com

 

[Golom]

HARIL

You should post new questions in a new thread.

As to your question:

Importing from what? Excel? Another Database?

 

[PaulBricker]

As Golom implies it makes a difference what you are importing and where it's coming from. If this is a text file, you should be able to set the import specification for the date field to the format you want but more info would be helpful to give better advice.

Paul