reformatting fields in a table

last month I started on changing the format in a LOT of tables.

changing Long Integer and Singles to double.

I got that working but I have one more question..


Sub AlterFieldType(TblName As String, FieldName As String, DataType
As
_
String, Optional Size As Variant)
Dim cd
If IsMissing(Size) Then
DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" &
FieldName & _
"] " & DataType
Else
DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" &
FieldName & _
"] " & DataType & "(" & Size & ")"
End If
End Sub

AlterFieldType "tblTEST", "test", "Single"

it worked nicely

and all was well but now they want to change one more thing and I am
not sure how.

most of these variables are set Single with format = fixed
and decimal places being 1,2 or 3

can these be removed like a new variable.

they dont' want any formatting on these numbers and want everything
set to double.

the double is no problem but I can not find the syntax for the other
things.

Re: reformatting fields in a table

Can I just do this by changing the property ?

On Tue, 27 Nov 2007 17:18:40 GMT, sparks wrote:

>last month I started on changing the format in a LOT of tables.
>
>changing Long Integer and Singles to double.
>
>I got that working but I have one more question..
>
>
> Sub AlterFieldType(TblName As String, FieldName As String, DataType
>As
> _
> String, Optional Size As Variant)
> Dim cd
> If IsMissing(Size) Then
> DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" &
> FieldName & _
> "] " & DataType
> Else
> DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" &
> FieldName & _
> "] " & DataType & "(" & Size & ")"
> End If
> End Sub
>
> AlterFieldType "tblTEST", "test", "Single"
>
> it worked nicely
>
>and all was well but now they want to change one more thing and I am
>not sure how.
>
>most of these variables are set Single with format = fixed
>and decimal places being 1,2 or 3
>
>can these be removed like a new variable.
>
>they dont' want any formatting on these numbers and want everything
>set to double.
>
>the double is no problem but I can not find the syntax for the other
>things.

Re: reformatting fields in a table

Well almost got it.

For Each prp In fld.Properties
' Debug.Print prp.Name & " === " & prp.Type

If FieldTypeName(fld) = "Long Integer" Or
FieldTypeName(fld) = "Single" Then
'Debug.Print FieldTypeName(fld) & " FOUND"


If prp.Name = "Format" Then prp.Value =
"General Number"


if I use prp.value = " " this removes the formatting but
it will not take General Number even though I can print it and it
shows General Number if I format it in the table as such.

any ideas?





On Tue, 27 Nov 2007 17:18:40 GMT, sparks wrote:

>last month I started on changing the format in a LOT of tables.
>
>changing Long Integer and Singles to double.
>
>I got that working but I have one more question..
>
>
> Sub AlterFieldType(TblName As String, FieldName As String, DataType
>As
> _
> String, Optional Size As Variant)
> Dim cd
> If IsMissing(Size) Then
> DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" &
> FieldName & _
> "] " & DataType
> Else
> DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" &
> FieldName & _
> "] " & DataType & "(" & Size & ")"
> End If
> End Sub
>
> AlterFieldType "tblTEST", "test", "Single"
>
> it worked nicely
>
>and all was well but now they want to change one more thing and I am
>not sure how.
>
>most of these variables are set Single with format = fixed
>and decimal places being 1,2 or 3
>
>can these be removed like a new variable.
>
>they dont' want any formatting on these numbers and want everything
>set to double.
>
>the double is no problem but I can not find the syntax for the other
>things.