how to get zero for null fields in a query

[rsmack]

i use a query to summarize transaction data from several tables for presentation in a form. if a table has no entries, because there are not yet any transactions of that type, the query returns null values for that table. these are unusable for calculations, such as totals. is there some way to get a zero value into these queries?

 

[lars7]

Maybe something like this:

Exp1:iif([Yourfield]is null,0,)

 

[rsmack]

thank you lars7,

i can work this out one field at a time, but i have something like 8 fields for each of four quarters, as well as summaries of in and out transactions of multiple field payments for each of 13 customers, so i am looking for a global method to get zeroes into the queries, instead of having to resolve each field on a form.

 

[AlexCuse]

Sorry rsmack, it doesn't work that way. You could change all your form or report controls to handle for the nulls I suppose, but your probably better off just doing it in your query, IMO.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[yont11]

Use the Access NZ function e.g NZ([MynumberField],0) this converts the Null value to zero.

 

[rsmack]

alexcuse,

how do i do i handle it in the query?

yont11,

thanks. i do know (now) how to do that. it does require a move for every field.

 

[lespaul]

how do i do i handle it in the query?

SELECT NZ(MyNumberFIeld, 0) From TableName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins