Sorry if I posted this in the wrong section.

I've linked an ODBC driver to access importing data. The data populates fine but one of the fields is formatted as "text" and I want it to be "date". It will not allow me to change the format as it seems to be set by ODBC source; further more the source wont let me change it either. Is there a way I can take the raw data and transfer it to a local table or query and then force format it to "date"? Currently I’m exporting it to excel, manually changing the format to date (by cutting and pasting), linking the table back to access and then using that to set the criteria of my query. It works but there has to be a better, less manual way.

Consult your ODBC driver's manual and see if they have information. Sometime this can be set via Option statement.

I have a similar problem with numeric fields coming through as text, I fix them like this:

Dim db As Database
Set db = CurrentDb
db.Execute "ALTER TABLE ESI_ORDERS ALTER COLUMN DELIVERYNO FLOAT;"

Can't find my 'Alter table' info at the moment, but surely there's a 'date' option?

My driver does not give me the option to change the field. I will look into Robjoy's suggestion. Any other suggests would be appreciated.

Thanks

Matt356

I figured it out. There is a conversion function called CDate. I just created an expression in the query using CDate and my field; it works like a charm.

FYI- There are lots of other conversion options so you might look at this if you have a similar problem.