changing datatype thru code

[Ted]

Hi all,

I am using a Make Table query to build a table for reporting purposes. I've
tried with no success and I've read and been told that there is no way to
control what data type access chooses for each control. I need it to be a
number field but in the back end tables the data type is text.

After I create the table is there a way thru code to change the data type of
my field?
the table name is CommercialGLPremium
the field name is RatingModificationFactor and RateDepartureFactor

TIA
Ted

 

[Dale Fye]

Generally, the way I do it is similar to Marshall.
1. create the temp table by running the make table query
2. check the data types of the temp table, change the ones the query got
wrong.
3. copy the temp table to a temp database, and link the table to my
application.
4. From that point on, I delete values from the temp table and then append
the new values rather that using a make table. When you create temp tables
in the local mdb, it tends to bloat them, unless you use "Compact on Close",
which tends to create more problems than it fixes. Another caution here is
that you will have to transform the text fields to numeric values using one
of the conversion functions, and you will have to handle the case where the
value is null (causes error in most of the conversion functions) or where the
text value will not convert to a numeric.

INSERT INTO TempTable (TextField, IntField)
SELECT TextField, CINT(TextField)
INTO TempTable
FROM your Field

Assuming [TextField] is a text data type that contains data that can be
converted to a number, the previous should insert values into TempTable.

The down side of this is that if the [TextField] field is null or contains
text that cannot be converted to a numer, it will generate an error, so you
might try:

INSERT INTO TempTable (TextField, IntField)
SELECT TextField,
IIF(ISNULL(TextField),
NULL,
IIF(IsNumeric([TextField]),
CINT(TextField),
NULL))
FROM your Field

HTH
Dale