By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,114 Members | 1,570 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,114 IT Pros & Developers. It's quick & easy.

reformatting fields in a table

P: n/a
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.

Nov 27 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Can I just do this by changing the property ?

On Tue, 27 Nov 2007 17:18:40 GMT, sparks <js******@swbell.netwrote:
>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.
Nov 27 '07 #2

P: n/a
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 <js******@swbell.netwrote:
>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.
Nov 27 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.