Is there a way out of this?

[tol514]

In ACCESS 2003, I am importing xls files into ACCESS. BUT, the date formats are over-written. So, I go in each day, after I update the Tables, and click DESIGN and make the field date/time, then format the field to SHORT DATE on the General Tab.

Is there a macro I can build for this in ACCESS?

Tom Walsh, HRIS Analyst
Capital Health System
ADP HR&Payroll wi RSmith

 

[SkipVought]

hi,

I am importing xls files into ACCESS...

Did you defing and SAVE your IMPORT SPEC, where you can define the Data Type for each import field?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tol514]

Yes, After I import the xls, I manually open the table design in ACCESS. I locate the date field, change it from TEXT to Date/Time, then go to the GENERAL TAB (bottom of Desgin Screen) and click on SHORT DATE. Is that what you're asking? Tom

Tom Walsh, HRIS Analyst
Capital Health System
ADP HR&Payroll wi RSmith

 

[SkipVought]

Yes. Did you SAVE that spec and then use it whenever you want to import a new worksheet?

That should work for you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tol514]

I import the xls to the table with the design in place. one of the specs in the design is date/time mm/dd/yyyy for all date fields. the import over-writes the date fields with TEXT. Then, I have to redo the date field back to Date/Time. Sorry if this is cloudy, but I can't attach a screen shot using box.net - I'm blocked. Tom

Tom Walsh, HRIS Analyst
Capital Health System
ADP HR&Payroll wi RSmith

 

[SkipVought]

If you are importing a field as Date/Time, there is no way that it becomes TEXT all by itself.

What evidence do you have that the field becomes TEXT?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tol514]

The field is defined on the Table as DATA TYPE Date/Time with a FORMAT of Short Date. After an import/refresh from an xls, it's back to the default of TEXT.I've tried changing the date fields during the import, but that didn't work. I tried to preserve the mm/dd/yyyy during the inital SQL extract, but that didn't work.
I'm only familiar with the "basics" on ACCESS, far from VB coding. I'm dealing with 8 daily table imports a day, and thought there might be a macro to preserve the format.

Tom Walsh, HRIS Analyst
Capital Health System
ADP HR&Payroll wi RSmith

 

[SkipVought]

What does the data look like in the source Excel file? There can be a problem if the data is MIXED in a column. So the question is:

Are the values in the column REAL DATES -- determine by a) turn on the AutoFilter b) select the COLUMN and change the cell number format to GENERAL c) use the AutoFilter drop down to check the values. They should ALL be NUMBERS and NO BLANKS.

If you have STRINGS or BLANKS, it can adversly affect your import.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tol514]

Thanks!! I'll verify these conditions. Tom

Tom Walsh, HRIS Analyst
Capital Health System
ADP HR&Payroll wi RSmith

 

[tol514]

conditions were OK. I located a macro called TransferSpreadsheet. I think that's going to work. Thanks for all your help on this! Tom

Tom Walsh, HRIS Analyst
Capital Health System
ADP HR&Payroll wi RSmith