recast format without renaming field

[miconian]

I have a field in my table called [Capture ID]. This field is formatted as text when the table is imported from another system, but is always contains a nine-digit Integer, and I want to change the data type. I was attempting to do it in a make-table query, like this:

Format([Capture ID],"General Number")

This works, but access won't let me keep the name of the field. It changes the formula to this:

Expr2: Format([Capture ID],"General Number")

I attempted to work around the issue by changing it to this:

Capture ID: Format([Capture ID],"General Number")

But, alas, Access perceives this as a circular reference.

I don't really want to give the field a new name. Other queries are dependent on the current field name, and it would be a pain to update them.
I also don't want to change the format by editing the original table, because this problem is part of a process that has to happen regularly.

 

[Joe4]

Import your data into a temporary table.
Then, use an Append Query to write the data to your final table. You can include your calculation in the Append Query.

 

[miconian]

When I try to enter the formula in an append query, the same thing happens; it's interpreted as an expression where I need to put a new expression name:

Expr2: Format([Capture ID],"General Number")

 

[Joe4]

It doesn't matter what it names the Field in your Append Query, because you designate which field to place the result in below that on the "Append To" row. See the screen print here under "Step 3: Choose the destination fields": http://office.microsoft.com/en-us/a...ble-by-using-an-append-query-HA010076526.aspx

 

[miconian]

I see what you're saying. But I'll still have to create the new table first. This overall process has to be repeated regularly... I think it ultimately might be faster to manually change the data type in the original, imported table each time. Or, I guess I could always have the same blank table prepared for the append query. I'll experiment. Thank you!

 

[Joe4]

I use this process quite regularly, especially if I am wanting to import a table, but only add certain records to the final table (i.e. new ones) and drop the rest.

The key is I have the temporary table shell, and have a macro or VBA code so that the whole process is automated. Here are the steps:

1. Run Code to delete all old information from the temporary table
(code looks like: "DoCmd.RunSQL "Delete [Table1].* From [Table1]"

2. Import the new file into the temporary table

3. Run the Append Query to add the records to the final table
(append query may include calculations or selection criteria)

So you only ever need to create the Temporary Table once. Then you are simply deleting/adding data to the existing Temp Table each time.