Changing field formats through a macro

[Keith Sparkes]

I have an import routine which imports a txt file into a database via a macro which works really well but I need to be able to change the date and time fields from text to date/time so that after import the table has all the correct formats.

Is this possible through a macro so that after the table has been created, the macro command converts the data type?

Any help would be most appreciated.

Many thanks

 

[Mile-O]

I use a two queries, actually, to perform this.

A main table and a temp table to begin with.
The main table has the structure you want; the temp table has the structure that's imported.

The first query, an APPEND query, inserts the fields into the main table.
The second table, a DELETE query, removes all the temp records.

To force a textual date into a date format you can use the CDate() function with the field name as its argument. It is, however, logical to check that it actually is a date before conversion, otherwise to leave it as Null and thus rwo more functions are introduced; one for date checking and one for deciding upon a course of action - the IsDate() function and the IIf() respectively.
ie..

FixedField: IIf(IsDate([Field]), CDate([Field]), Null)

 

[Keith Sparkes]

Thanks for this - but does anyone know how to run a query via a macro please?

 

[Mile-O]

Select these options:

SetWarnings (set to False)
OpenQuery (append query's name)
OpenQuery (delete query's name)
SetWarnings (set to True)

In code, more efficient than a macro, it's:

DoCmd.SetWarnings False
DoCmd.OpenQuery "append query's name"
DoCmd.OpenQuery "delete query's name"
DoCmd.SetWarnings True

 

[Keith Sparkes]

I'm sorry, you have lost me!!

I will try to explain where I trying to come from.

I have a simple macro that imports a text file. Within that text file I have some dates and times.

I need those date and time field to change from text fields to date and time fields.

If I change the import routine I get a load of import errors with the date and time conversions so I though I could simply create an append query to transfer the data that has been imported into the proper table structure with all the fields set to either text or to date and time.

I would like the import routine via the macro to include the append query and was wondering whether there is a way of doing this?

Many thanks for all your help.

Keith

 

[spacepro]

I'm sorry, you have lost me!!

See the image attached, I am assuming you are using transfertext for the import.

Basically create a table

tblImportTemp

Create the fields that match the import data exactly.

Set the arguments in the macro, change the import table destination to tblImportTemp and then set the openquery arguments as per Mile-o's instructions.

Thi s will append the data to the final table and then deletes the temp table as per Mile-0's answer.

If you follow Mile-O's answer by each sentence you will be able to carry out , what you want. Is would be better to use VBA code.

Andy