Running same query for each column

[njvsummit]

I have an inherited table that is going to be used for reporting (will not be updated). It contains over 150 columns. In each of the columns a row might have a "real" value, but could also have a blank or null. I would like to set each of the blank values to null. I can do that with the following type of query:

update mytable
set mycolumn = NULL
where len(mycolumn) = 0

Is there a way (with code I'm guessing) to avoid running a separate query for each of the 150+ columns in my database?

Thanks for your help

 

[PHV]

Something like this ?

Dim rs As DAO.Recordset
Dim i As Long
Set rs = CurrentDb.OpenRecordset("mytable")
For i = 0 To rs.Fields.Count - 1
DoCmd.RunSQL "UPDATE mytable SET [" & rs(i).Name & "]=NULL WHERE Len([" & rs(i).Name & "])=0;"
Next i
rs.Close
Set rs = Nothing

You may consider DoCmd.SetWarnings False

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244