MS Access - VBA - Code to loop thru all fields in a table, test for a few criteria and if the criteria are correct, change the value of the field to 0

I have a table with about 50 fields. If the value in the first field is = '99' I would like to loop thru each of the remaining fields and if the field data type is numeric and the current value is null, I would like the value changed to 0. Is there a straight forward way using vba to do this?

The table name is dbo_stafil00
The first field name is whs, and that is the field we need to test for being equal to '99'
All of the other fields need to be tested to determine if they are numeric and if so, test for null and if that is also true changed to 0
Rick RudolphAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Don't think there is a "straight forward" method, but you could probably do something like the following.

*WARNING: untested, use a backup copy of this table to test this code before actually running it against your table.

Dim rs as dao.recordset
Dim fld as DAO.Field

'This will open an empty recordset
set rs = currentdb.openrecordset("SELECT * FROM dbo_stafil00 WHERE 0 = 1")

For each fld in rs.fields
    if <> "whs" AND _
       (fld.Type = dbDecimal OR fld.Type = dbSingle OR fld.Type = dbDouble OR _
        fld.Type = dbFloat OR fld.Type = dbInteger OR fld.Type = dbLong OR _
        fld.Type = dbNumeric) Then
        strSQL = "UPDATE dbo_stafil00 SET [" & & "] = 0 " _
                     & "WHERE [whs] = '99' AND [" & & "] IS NULL"
        currentdb.execute strsql, dbfailonerror
    end if


Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Are you sure you want to do this?  Typically Null means unknown whereas 0 means the value is known to be 0.  This will impact certain domain functions such as average.

The average of 3, null, 3 is 3  But the average of 3, 0, 3 is 2.  That should tell you whether you want the unknown values to be stored as 0.  This would be critical in an application that calculated grades.  You wouldn't want an unposted grade to be treated as 0 since it would skew the average even if only briefly.  Also, how would you know in the future if the grade truly was 0 or if you just forgot to enter it?

Once you find you have to allow nulls in numeric fields, you need to always consider them in your calculations.  The SQL and domain functions ignore nulls so you won't have any problem with them.  It is only if you have to perform arithmetic that you will run into a problem.
fld1 + fld2 will result in Null if either column could be null.  So in this case, you would need to substitute 0 to make the calculation return a result.
Nz(fld1, 0) + Nz(fld2, 0) will substitute 0 for null in this expression so the result will be 0 or the value of the non-null field.

If it turns out that you have nulls when you should have 0's, use the code suggested by Dale to fix up the data.  Then to prevent a recurrence, modify the table design to assign 0 as the default rather than null.
Rick RudolphAuthor Commented:
Thanks for the comments, I am sure I want the 0s. I mass initialized a bunch of records in an SQL table that is normally updated by another program which cannot deal with the nulls.  I plan on trying Dales suggestion in the morning.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Rick RudolphAuthor Commented:
This worked perfectly, thank you very much.
Dale FyeOwner, Developing Solutions LLCCommented:
glad I could help.
Don't forget to modify the table definition to have 0 as the default for these fields.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.