Access Table Field data type

[math20]

Hi,

I am importing data from excel to access table one of the field is in date/time format. Within excel the date type is consistent with mm/mm/yyyy hh:mm format.
When I import this data into excel using the get exteranl data method from access.
This data field changes the format:

all dates upto 12th day shows as mm/dd/yyyy from the 13th day the foramt changes to dd/mm/yyyy.

Can anyone please help me to resolve this. I need to have one consistent format for me to use the data.

Thank you.

 

[lameid]

The quick way for a one time thing is to save the excel file to a CSV file and then import that TEXT file into Access.

Tell Access that the text delimiters are double quotes (") and the field delimiter is the comma (,).

Specify the order of the date elements on the same screen.

 

[math20]

Hi lameid,

Thank you. The table with date filed comes out blank. This happens only for the date field with time, I have other field that shows date they are without time and are imported correctly. The ones with date and time field are imported as blank field.

Any suggestion how to fix it?

Thank you again.

 

[lameid]

Did you specify that the field is date/time when you attempted to import it?

 

[math20]

Hi lameid,

Yes it does say Date/Time for that field unless I should be specifying it in another place. Should it be indexed as Yes (duplicate OK)?


Thanks.

 

[lameid]

So after you hit avanced you have specified DMY as the date order and specified the datatype of the field as Date/Time...

Can you post the way the date and time looks like in the file (copy from notepad, include delimiters around it).

 

[math20]

Hi Lameid,

As per your advice here are the three columns.

10/21/2008 04:07 PM Boucher, Nancy 12/16/2008 05:32 PM

Thank you.

 

[lameid]

I misread your original Post... you want to specify MDY not DMY.

 

[math20]

Preferably MDY as long as it is consistent and does not change.

Thanks.

 

[lameid]

I'm talking about specifing MDY as the date order in the import specification like I said above.

 

[math20]

Sorry yes. It is specified as MDY.