Automate a data type change

[drwigginton]

Hello.

I have a table in an Access 2003 database, tblDataDump, that accepts data imported from an Excel spreadsheet. The data is processed through a make table query that performs some simple modifications for further use. One of the fields, CostCenter, is imported into tblDataDump as a text field, and I need it converted to a number. Is there a method for automating a data type change through either a query, macro, etc, or VBA?

Thanks for any suggestions!

[Stewart Ross]

...One of the fields, CostCenter, is imported into tblDataDump as a text field, and I need it converted to a number. Is there a method for automating a data type change through either a query, macro, etc, or VBA?

Hi. Whilst you can use a VBA procedure to change the data type of a field in a table definition programmaticall y, you would need to import your data then remember to run the conversion routine every time you do a new import. In my opinion it would be far simpler to leave the text-converted CostCenter field as it is, and instead use a new base query to feed further processing steps to which you add a calculated field converting CostCenter from text to numeric. This is easily achieved by using the CInt() function (if the value is a simple integer) or Clng (if it is a long integer) as appropriate.

The SQL for the query is of this form:
[CODE=SQL]SELECT (CInt([CostCenter]) as [RevCostCenter], [Other Fields1], [Other Fields2],..., [Last Field] FROM [tblDataDump];[/CODE]
-Stewart

[drwigginton]

Hi. Whilst you can use a VBA procedure to change the data type of a field in a table definition programmaticall y, you would need to import your data then remember to run the conversion routine every time you do a new import. In my opinion it would be far simpler to leave the text-converted CostCenter field as it is, and instead use a new base query to feed further processing steps to which you add a calculated field converting CostCenter from text to numeric. This is easily achieved by using the CInt() function (if the value is a simple integer) or Clng (if it is a long integer) as appropriate.

The SQL for the query is of this form:
[CODE=SQL]SELECT (CInt([CostCenter]) as [RevCostCenter], [Other Fields1], [Other Fields2],..., [Last Field] FROM [tblDataDump];[/CODE]
-Stewart

It worked like a charm! Thanks, Stewart!