Loop Through Columns and set Nulls to 0 (zero)

[mavrrick]

Hello,

I am running a MS Access query manually where I select each individual data field and set it to zero (0) if the criteria is Null. There are over 25 fields (and increasing), so I was attempting to find a simplier way to automate this process (e.g. a For Next loop).

Doing it once looks something like this:

UPDATE Table
SET JanuarySales = 0
WHERE JanuarySales IS NULL

Is there a For Next loop that can parse through each of the Columns (JanuarySales, FebruarySales, etc) without knowing the Column Names prior to running?

Thanks!

 

[Golom]

Something like this maybe

Dim fld As DAO.Field
Dim db  As DAO.Database
Dim SQL As String
Set db = CurrentDb()
For Each fld In db.TableDefs("myTable").Fields
   SQL = "UPDATE myTable SET [" & fld.Name & "] = 0 " & _
         "Where [" & fld.Name & "] IS NULL"
   db.Execute SQL
Next
Set db = Nothing

 

[PWise]

if your number of fields is ever increasing your data must not be nomelized

anyway this can be done

set def = currentdb.tabledefs("tablename")
for each fld in def.fields

docmd.runsql "UpDATE Table SET " & _
fld.name & _
"= 0 " & _
"WHERE JanuarySales IS NULL"


next

 

[JoeAtWork]

each of the Columns (JanuarySales, FebruarySales, etc)

...and a cold shiver went down my spine when I read that.

Your current problem is only the tip of the iceberg of problems you will encounter when your database is not normalized.

 

[Golom]

Someone around here invented the term "committing spreadsheet" to describe what you are doing. You have columns that contain data for each month and you add a new column when you get a new month's data.

As JoeAtWork implies ... this way there are dragons!

Standard SQL becomes almost useless and just about anything you want to do starts to require new coding to handle the ever-changing structure of your database.

The basic rules of thumb are
- Normalization is a good thing
- Long is better than wide

Think of a structure like this

[tt]tblSales
FirstDayOfMonth DateTimeField
MonthSales Currency
[/tt]
Then
- You don't need to set anything to 0 because the record simply doesn't exist.
- You just get more records for a new month. Your table structure is unchanged.
- SQL to retrieve the data is constant.
- You don't need to write VB to deal with changes.

 

[mavrrick]

Thanks for the tips guys.

And yes, I know I need to normalize this table at some point, this is just a database I inherited so until I have more time to spare I am working with what I have.