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?
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 FyeCommented:
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 fld.name <> "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 [" & fld.name & "] = 0 " _
                     & "WHERE [whs] = '99' AND [" & fld.name & "] IS NULL"
        currentdb.execute strsql, dbfailonerror
    end if

NExt

Open in new window

0

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
PatHartmanCommented:
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.
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rick RudolphAuthor Commented:
This worked perfectly, thank you very much.
0
Dale FyeCommented:
glad I could help.
0
PatHartmanCommented:
Don't forget to modify the table definition to have 0 as the default for these fields.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.