Alter Table/Column

Question

Hello all,

After importing a text file as a table, I need to alter the data type of a column.

The data is numbers, but I have a mix where some records for the column are decimal, i.e. 92.45 and some are do not have decimals, i.e 10 .

I've tried to use this method after importing the table:

DoCmd.RunSQL "ALTER TABLE TempTable ALTER COLUMN LENGTH DOUBLE"

Where "TempTable" is the table name and "LENGTH" is the column name.

I've tried  this:

DoCmd.RunSQL "ALTER TABLE TempTable ALTER COLUMN LENGTH DECIMAL (3, 2)"

But I get a syntax error when running.

Thought on the correct or best way/syntax to convert all the data in the "LENGTH" column to a 2 decimal place data type?

Access 2013

Thanks

***Office Sub-topic moved to Microsoft Office Programming.***

Answer 1

Might there be a way to set the field propertied at the time the table is created?

Here is how I am creating the table:

SQLstr = "SELECT [CABLE BILL STATUS ALL].BILL, [CABLE BILL STATUS ALL].CIRCUIT, [CABLE BILL STATUS ALL].LENGTH, '' AS TAGS INTO TempTable " & _

         "FROM [CABLE BILL STATUS ALL]" & _

         "WHERE ((([CABLE BILL STATUS ALL].BILL)='" & MyValue & "'));"

DoCmd.RunSQL SQLstr

Answer 2

actually, as you may have guessed by the column title, the column is a footage for cable.

I am ultimately taking the footage (LENGTH") and dividing by ten and rounding up to the next whole number (even if the value is less than .5).

I'm doing this  to set the number of tags needed.

Once I have the footage I use this routine to get the needed number (for tags):

Str = (rst.Fields("LENGTH") / 10)

    x = InStr(1, Str, ".")

    Str1 = Left(Str, x)

    If Str > Str1 Then

        Str2 = Str1 + 3

    End If

    rst.Edit

    rst.Fields("TAGS") = Str2

    rst.Update

    rst.MoveNext

As you can see I'm looking for the decimal.

and the routine errors out if it does not find the decimal.

This is why I was hoping to convert the "LENGTH" column to decimal, so that anything that was a number without a decimal, i.e. 10 would be converted to a number with a decimal, i.e. 10.00, and anything that already had a decimal, i.e. 49.25 would be left alone.

Answer 3

You are confusing storage with presentation.

Double or Single is the correct data type for floating point values.

Decimal should be avoided as there is little support for it in VBA.

After changing the data type you can run a few update queries if you like, such as to round all values to 2 decimals.

10 will still be 10.

Then when it comes time to present the data, either in a form or a report, you can apply the correct formatting using the Format property of the control.