Error message with IIF statement

[Lynnjm]

When I enter the following statement:

IIf([Fieldname]=0,null,[Fieldname])

I get an Error message.

Also, in the query, if I say <> 0, the results do not show anything, however in my reports that field will then show up as Error in my calculations.

Do you have any ideas what I am doing wrong.

Thank you

 

[Pat Hartman]

If Fieldname is a numeric field and it is not defined as required, the IIf() should work.

You need to specifically inculde null values if you want them in the recordset. Just using "<>0" will only include rows where the field HAS a value that is not = 0. If you also want nulls, you need to change the criteria to:

<>0 Or Is Null

 

[Lynnjm]

Thank you Pat, but this does not really address the problem I was having.

I have a form, Consultants, with a subform, expenses. Even if there was no expense allocated to the consultant for a specific job on a specific date, I still have to make a "0" entry in the expenses subform, otherwise I get "Error" in my report. This causes a lot of "0" to show up when I create my report; so what I was trying to accomplish was doing a query and asking that either "0" or "null" not be included. This was done but then I again get "error". As soon as I remove the <> 0 from the query the "error" message goes away but then all the 0s show up

 

[Rich]

What do you mean "0" is this a text field? If it's numeric then just put >0 in the criteria no quotes.

 

[Lynnjm]

Rich:

No, it is a numeric field and I only put "" on it for emphasis in this text - I do not include " in my queries

 

[Pat Hartman]

Let's try again. You don't want zero values AND you don't want null values:

<>0 And Not Is Null

 

[Lynnjm]

That is correct - I don't want 0 or null values to show. However, if I select this option in the query for my report' when I look at the report "Error" will show up. If I remove the <>0 or not null from the query, on which the report is based, then the "error" message has gone, but all the 0s show up

 

[Pat Hartman]

Are you using OR as the relational operator or are you using AND? Boolean logic requires the AND operator when combining conditions when at least one of the conditions includes the Not operator.

 

[Lynnjm]

My problem is not with the and or or - it is when I create my report. If there is no associated entry in either the Expenses or Disbursements (to go with the hours) then the report totals for expenses, disbursements and total with show "Error". I can get around this by putting a dummy entry of 0 in both the Expenses and Disbursements, but then I also have a report with many many unnecessary 0s.

 

[Rich]

Use the Nz function on the reports