reformat of 17 tables from integer to single

We are redoing an existing database and need to reformat
all the number fields from integer to single fixed 3.
their main thing is negative numbers and the decimal places.

Can this be done in code?

field size long -->single
format fixed
decimal places 3

Since we are going from integer to single format this should
not mess with any existing values should it ?
I can see where single to integer would but not to a larger format.
it didn't when I tried it manually.

Re: reformat of 17 tables from integer to single

Use a DDL query statement to alter the numeric field to SINGLE.

Here's an example:
http://allenbrowne.com/func-DDL.html#ModifyFieldDDL

You will not want to alter *all* the numeric fields to single. For example,
if you have an AutoNumber primary key in (say) the Orders table, and you
have the matching foreign key field in a related table, you must not change
that to Single.

You may also need to be aware that the Single and Double fields types are
subject to floating point errors, so you won't get exact matches. More
information about this:
http://en.wikipedia.org/wiki/Floating_point#Accuracy_problem s
An alternative (especially since you only want 3 places) might be to use
Currency (which is a fixed point field.)

The issue of specifying the Format and Decimal Places property
programmatically will require you to use the DAO library, since that's the
only way to set these properties. Some properties don't exist until you
CreateProperty(), though they may exist if they have been previously set.
Here's an example of some DAO code to set them:
http://allenbrowne.com/func-DAO.html#SetPropertyDAO

I'm not sure if you need to programmatically loop through the fields of the
table as well? If so, this illustrates how:
http://allenbrowne.com/func-06.html

Finally, if you do have many similar numeric fields in the one table,
there's a very good chance that you have set up a spreadsheet like struture
rather than a good relational design. Since you are redoing the entire
database, you might want to consider whether there is a better design. The
built-in analyzer might be the place to start:
Tools | Analyze | Table

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sparks" wrote in message
news:4rt6h3lalkdis0sstqb5jmfv7c6htjmkva@4ax.com...
> We are redoing an existing database and need to reformat
> all the number fields from integer to single fixed 3.
> their main thing is negative numbers and the decimal places.
>
> Can this be done in code?
>
> field size long -->single
> format fixed
> decimal places 3
>
> Since we are going from integer to single format this should
> not mess with any existing values should it ?
> I can see where single to integer would but not to a larger format.
> it didn't when I tried it manually.

Re: reformat of 17 tables from integer to single

yes I did this on a test

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


========
when looking at DAO and the field properties I find this
field size and decimal places but I dont see format.
looking at a double with no format or decimal places set just
a double created.
the default of DecimalPlaces is 2 ??
I assume that since the format is NOT SET it does not show up is this
correct?


test3 Double

Value 7
Attributes 4
CollatingOrder 3
Type 3
Name 12
OrdinalPosition 3
Size 4
SourceField 12
SourceTable 12
ValidateOnSet 1
DataUpdatable 1
ForeignName 12
DefaultValue 12
ValidationRule 12
ValidationText 12
Required 1
AllowZeroLength 1
FieldSize 4
OriginalValue 7
VisibleValue 7
ColumnWidth 3
ColumnOrder 3
ColumnHidden 1
Description 10
DecimalPlaces 2
DisplayControl 3


On Mon, 15 Oct 2007 22:54:26 +0800, "Allen Browne"
wrote:

>Use a DDL query statement to alter the numeric field to SINGLE.
>
>Here's an example:
> http://allenbrowne.com/func-DDL.html#ModifyFieldDDL
>
>You will not want to alter *all* the numeric fields to single. For example,
>if you have an AutoNumber primary key in (say) the Orders table, and you
>have the matching foreign key field in a related table, you must not change
>that to Single.
>
>You may also need to be aware that the Single and Double fields types are
>subject to floating point errors, so you won't get exact matches. More
>information about this:
> http://en.wikipedia.org/wiki/Floating_point#Accuracy_problem s
>An alternative (especially since you only want 3 places) might be to use
>Currency (which is a fixed point field.)
>
>The issue of specifying the Format and Decimal Places property
>programmatically will require you to use the DAO library, since that's the
>only way to set these properties. Some properties don't exist until you
>CreateProperty(), though they may exist if they have been previously set.
>Here's an example of some DAO code to set them:
> http://allenbrowne.com/func-DAO.html#SetPropertyDAO
>
>I'm not sure if you need to programmatically loop through the fields of the
>table as well? If so, this illustrates how:
> http://allenbrowne.com/func-06.html
>
>Finally, if you do have many similar numeric fields in the one table,
>there's a very good chance that you have set up a spreadsheet like struture
>rather than a good relational design. Since you are redoing the entire
>database, you might want to consider whether there is a better design. The
>built-in analyzer might be the place to start:
> Tools | Analyze | Table

Re: reformat of 17 tables from integer to single

That's correct. If the Format has not been set, the property does not exist
yet. Hence CreateProperty().

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sparks" wrote in message
news:ti17h317e6ujbb1lq9i16i7s5e7gafeeo4@4ax.com...
> yes I did this on a test
>
> 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
>
>
> ========
> when looking at DAO and the field properties I find this
> field size and decimal places but I dont see format.
> looking at a double with no format or decimal places set just
> a double created.
> the default of DecimalPlaces is 2 ??
> I assume that since the format is NOT SET it does not show up is this
> correct?
>
>
> test3 Double
>
> Value 7
> Attributes 4
> CollatingOrder 3
> Type 3
> Name 12
> OrdinalPosition 3
> Size 4
> SourceField 12
> SourceTable 12
> ValidateOnSet 1
> DataUpdatable 1
> ForeignName 12
> DefaultValue 12
> ValidationRule 12
> ValidationText 12
> Required 1
> AllowZeroLength 1
> FieldSize 4
> OriginalValue 7
> VisibleValue 7
> ColumnWidth 3
> ColumnOrder 3
> ColumnHidden 1
> Description 10
> DecimalPlaces 2
> DisplayControl 3
>
>
> On Mon, 15 Oct 2007 22:54:26 +0800, "Allen Browne"
> wrote:
>
>>Use a DDL query statement to alter the numeric field to SINGLE.
>>
>>Here's an example:
>> http://allenbrowne.com/func-DDL.html#ModifyFieldDDL
>>
>>You will not want to alter *all* the numeric fields to single. For
>>example,
>>if you have an AutoNumber primary key in (say) the Orders table, and you
>>have the matching foreign key field in a related table, you must not
>>change
>>that to Single.
>>
>>You may also need to be aware that the Single and Double fields types are
>>subject to floating point errors, so you won't get exact matches. More
>>information about this:
>> http://en.wikipedia.org/wiki/Floating_point#Accuracy_problem s
>>An alternative (especially since you only want 3 places) might be to use
>>Currency (which is a fixed point field.)
>>
>>The issue of specifying the Format and Decimal Places property
>>programmatically will require you to use the DAO library, since that's the
>>only way to set these properties. Some properties don't exist until you
>>CreateProperty(), though they may exist if they have been previously set.
>>Here's an example of some DAO code to set them:
>> http://allenbrowne.com/func-DAO.html#SetPropertyDAO
>>
>>I'm not sure if you need to programmatically loop through the fields of
>>the
>>table as well? If so, this illustrates how:
>> http://allenbrowne.com/func-06.html
>>
>>Finally, if you do have many similar numeric fields in the one table,
>>there's a very good chance that you have set up a spreadsheet like
>>struture
>>rather than a good relational design. Since you are redoing the entire
>>database, you might want to consider whether there is a better design. The
>>built-in analyzer might be the place to start:
>> Tools | Analyze | Table
>

Re: reformat of 17 tables from integer to single

Thanks big time for all your help




On Tue, 16 Oct 2007 00:42:59 +0800, "Allen Browne"
wrote:

>That's correct. If the Format has not been set, the property does not exist
>yet. Hence CreateProperty().