Query: Assigning a null value

[illy2k]

I have a qeury that is pulling totals for a report. It needs to pull six totals, and then show the totals for each Order ID#. So it should say order Id #1, then six totals, even if the total is 0.00. Each type of service is coded, so code#1 is a tires repair, code#2 is a fluids repair, ect... all the way to six. In the form, there is a combo box that is a lookup to a Products table, and when a Fluids coded item is entered, the code(#2) is sent into the table along with it. However, for each of the codes (1-6) if it is not picked on the form, it is not entered into the table. So when the query looks for totals it only pulls the order ID #'s that have all the 6 totals. So I need to somehow, in the query for the report assign a $0.00, if one of these totals colums are coming into the query with no value.

 

[dcx693]

Try creating calculated fields in your query that show code# amount for each orderID. Then make it into a totals query where you group by order ID and sum over the code# fields. That should give you zeros for your code# fields that require them. Make that totals query the source for your report.

 

[illy2k]

Too Complex

I might have made it very confusing. All I need is to assign a 0.00 to a null value in a query. When a query comes upon a null value all I need is it to say hey, there is a null value, I'll put in a 0.00

 

[simongallop]

nz([FieldName],Default Value)

so for yours would have: nz([MyFiled],0)

HTH

 

[illy2k]

Didn't Work

The nz function doesn't seem to work. Is there anything else i can do, like and IIf function or something. I just need something that can go in the criteria of a field within a query, and tell the query that if it a null value, that means if there is nothing there, give it this value. Is there anyway to do that?

 

[simongallop]

Post your query code and will see