Export / Import to and from Excel

[mohsinhq]

hi,

I'm having a small but annoying problem exporting data to excel and then importing it back..

The problem is that when i export a table to Excel - it changes the date format from dd/mm/yy (which is what i have in Access) to DD-MMM-YY (automatically in Excel)

This proves to be a problem because when i import the spreadsheet back Access gives me a data type conversion error.

Its easily solved manually by changing the field in excel to dd/mm/yy format but annoying as the user will have to do this everytime they export and import!

Any suggestions??

Thanks in Advance..

 

[RV]

Base your export on a query, NOT on a table.
Use the Format function to get the desired date format.

RV

 

[mohsinhq]

Brilliant. Thanks for that

Cant believe it was as easy as that. Bit embarassed i asked!

 

[maxmangion]

try to set the correct date format in the Regional Settings from your control panel.

 

[RV]

try to set the correct date format in the Regional Settings from your control panel.

That's a definite NO GO as this setting is actually a Windows setting....

RV

 

[Hapr1]

date conversion problem

hello , I have the same date conversion problem but the only difference is i run a macro which imports a file from excel to access then performs the queries and exports it back to excel and i ve the same problem with the date i want it in excel as mm/dd/yy but i get as dd-mmm-yy. i tried to change by using the format option of the query by selecting the field , but it did not work out .

I guess the reason should be when it imports the input file it imports all fields as text .
What can be done
can anyone help me ?

 

[Hapr1]

HOw to use the format function . I had tried to use it but does not work for me .
when i click properties of the particular column in a query and go to format tab , i type in mm:dd:yy and then execute the query but nothing happens.
can someone help me ?