Hi. I have over 20 tables with many fields in each that are Currency
fields. Every one has a Default Value of 0, which gives every new
record I create hundreds of $0 values. Is there a way to remove this
Default Value for all these fields and tables at one time? or will I
have to finish out the school of hard knocks one field at a time?
Many thanks for your continued help!
Greg.
On Apr 9, 8:28=A0am, g_k_harri...@yahoo.com wrote:
> Hi. I have over 20 tables with many fields in each that are Currency
> fields. Every one has a Default Value of 0, which gives every new
> record I create hundreds of $0 values. Is there a way to remove this
> Default Value for all these fields and tables at one time? or will I
> have to finish out the school of hard knocks one field at a time?
>
> Many thanks for your continued help!
> Greg.
First, go to the table definitions and remove the default values for
each field.
Next, you will have to create an update query for each table that
updates all currency fields with values =3D 0 to a desireable value,
like NULL.
UPDATE tblAny1 SET
Field1 =3D NULL
WHERE Field1 =3D 0
Execute this query after changing the field names for each field in
the table. Repeat this process for each table.
On Apr 9, 1:49=A0pm, Technolust
> On Apr 9, 8:28=A0am, g_k_harri...@yahoo.com wrote:
>
> > Hi. I have over 20 tables with many fields in each that are Currency
> > fields. Every one has a Default Value of 0, which gives every new
> > record I create hundreds of $0 values. Is there a way to remove this
> > Default Value for all these fields and tables at one time? or will I
> > have to finish out the school of hard knocks one field at a time?
>
> > Many thanks for your continued help!
> > Greg.
>
> First, go to the table definitions and remove the default values for
> each field.
>
> Next, you will have to create an update query for each table that
> updates all currency fields with values =3D 0 to a desireable value,
> like NULL.
>
> UPDATE tblAny1 SET
> =A0 =A0 =A0Field1 =3D NULL
> =A0 =A0 =A0WHERE Field1 =3D 0
>
> Execute this query after changing the field names for each field in
> the table. =A0Repeat this process for each table.
I dont THINK you have to change the table structure in order to change
the values in existing records. You can merely run the UPDATE query
for each field/table. The change/removal of the default should only
affect new records added w/o a value specified for the fioeld(s) in
question.
On Apr 9, 12:49=A0pm, Technolust
> On Apr 9, 8:28=A0am, g_k_harri...@yahoo.com wrote:
>
> > Hi. I have over 20 tables with many fields in each that are Currency
> > fields. Every one has a Default Value of 0, which gives every new
> > record I create hundreds of $0 values. Is there a way to remove this
> > Default Value for all these fields and tables at one time? or will I
> > have to finish out the school of hard knocks one field at a time?
>
> > Many thanks for your continued help!
> > Greg.
>
> First, go to the table definitions and remove the default values for
> each field.
>
> Next, you will have to create an update query for each table that
> updates all currency fields with values =3D 0 to a desireable value,
> like NULL.
>
> UPDATE tblAny1 SET
> =A0 =A0 =A0Field1 =3D NULL
> =A0 =A0 =A0WHERE Field1 =3D 0
>
> Execute this query after changing the field names for each field in
> the table. =A0Repeat this process for each table.
Thank you for that suggestion!
g_k_harrison@yahoo.com wrote:
>Hi. I have over 20 tables with many fields in each that are Currency
>fields. Every one has a Default Value of 0, which gives every new
>record I create hundreds of $0 values. Is there a way to remove this
>Default Value for all these fields and tables at one time? or will I
>have to finish out the school of hard knocks one field at a time?
You could write a utility procedure to make those design
changes. Loop through the TableDefs collection (skipping
the system and other table you do not want to change). Loop
through each table's Fields collection checking the data
type for dbCurrency. Then you can set the DefaultValue
property to ""
A general air code outline of DAO code could be something
like:
For Each tdf In db.TableDefs
If tdf.Name Like "MSYS*" _
Or tdf.Name = "some odd table" _
Or . . . Then Exit For
For Each fld In tdf.Fields
If fld.Type = dbCurrency Then
fld.Properties!DefaultValue = ""
End If
Next fld
Next tdf
If any currency field does not have a default set, then use
error handling to ignore the missing property error.
--
Marsh