The "" equivalent for numbers

[alktrigger]

I have a set of queries that dump into a table, but a recent change has them leaving blanks (instead of 0's). I'm trying to create an update query that will sort through the numbers (and blanks) and place a 0 (zero) in the columns that currently have no contents. I'm experimenting with an update query, but it is giving me an error "Data type mismatch in criteria expression" which leads me to believe that using the <> "" (not equal to null) is causing the problem. SQL looks as following:

UPDATE tblReport1 SET tblReport1.Wk1 = 0,...
WHERE (((tblReport1.Name)<>"") AND ( ((tblReport1.Wk1)<>"") OR ((tblReport1.Wk2)<>"") OR ...

Where the values in Wk1 are numerical

Any suggestions to different criteria?

 

[HiTechCoach]

Using "using the <> "" (not equal to null)" is not a true statement.

<> "" only works for field that are NOT null.

To test for null in VBA use the IsNull() or in SQL is "Is Null".

So try this:

WHERE ((( tblReport1.Name) Is Not Null ) 
  AND ( ((tblReport1.Wk1) Is Not Null)

 

[alktrigger]

here is a picture of my table (unpopulated still in testing). My goal is to run a query to make all the blanks say "0". Should I rethink the query using "IS NULL" criteria, or restart and try something else?

 

[HiTechCoach]

... but a recent change has them leaving blanks (instead of 0's).

This sounds like this real issue. I would fix this issue so that they are never blank, but zero (0).