Help Please! Defining Null as 0 for no Result

[ksaab]

I am in a bind. I am trying to calculate sales by month, compare those sales to what the client contracted for and look at the result as incremental growth.

I can calculate monthly sales from start of Contract Date. I can subtract contracted units per month based on contract date like so :

in an Update query:

Sales Incremental[Sales.August] -[Seg.UnitsContracted])
Where ContractDate is <=#8/3104 and SalesDate Between #8/1/04# and #8/31/04#

I get the result I want - kind of...

The problem is this - I am trying to also populate 0 in those fields where the criteria requirment is not met (where ContractDate is >= #8/30/04#)

I tried setting a default value but it gets over written by a null string. I can't seem to figure out how to update the nulls to zero - aregular update query isn't working..

Any ideas?

 

[FoFa]

if it is a "on the fly" thing, use NZ function NZ([MyCol],0)
is that what you are looking for?

 

[ksaab]

I know that function and do use it often, but it won't work here because the query is not comparing all values it is only comparing those that are created after the contractdate. I guess a better way to state what I am trying to do is to populate the fields that return no value with 0. I tried running a second query to update null or " " to 0 but it does nothing? If I were in SQL I would use a If /then/else..I just don't know how to obtain that result in Access..