Calculations involving NULL values

[vanilladan]

Hello all,
I wonder if anyone can help.

I have a Database, the information in which is taken from
external reports. The database is therefore "refreshed" on a regular basis.
I run several Select queries to identfy certain records, for instance:

Show records where AVAILABLE STOCK is less than DEMAND.

The problem I have is this:
Many of the records contain NULL values in more than one field.

Where this is the case, ACCESS does not "calculate against" these records.

So, is there a way of saying to Access "treat Null values as Zero"?

If you would like me to give more specific examples, please let me know.
kind regards,

Dan

PS - It may seem like a simple answer to use UPDATE queries to
replace Null values with Zero (which I currently do), but the database is so
large that it takes time to run these queries every time.

 

[bat17]

look up the NZ() function, it does exactly what you need

Peter

 

[Dugantrain]

You can do this via a query using an Iif function.
In the column heading write:

Column_Name:Iif([Your_Column] IS NULL,0,[Your_Column])

What this is doing is saying "If the value in this field IS NULL, return a 0, Else Return the original Value". And just so you know, Iif's are lightning quick in Access, much quicker than the Update Queries would be.

 

[Excelguru]

You should also be able to set the defult value of the field to zero... this will allow calculations on your null fields.

Hope this helps.
JT