Null Values and IIF problem

[dazstarr]

Hi everyone

Please can someone help me with this problem I am having.

I have a form with a subform on it.

On the main form are two date fields - when the user enters the two dates - the subform shows any records which fall between the two dates.

One of the fields on the subform is a currency field. I have another field on the main form which totals up this currency field. All works well....

But if no records appear in the subform - my total field on the main form has '#Error'.

Here is the content of the field:

=IIf(Forms!frmInvoices!subTaxiChangesForInvoices!TCCalc=Null,0,Forms!frmInvoices!subTaxiChangesForInvoices!TCCalc)

What I would like it to do is display a zero if there is no records, or if there are records then show the content of the above field. I have tried replacing the NULL part above with two quotes but it doesnt work.

I created a command button with displays a msgbox of the the total field, but if I press this button when no records are displayed in the subform then I get this lovely error message:

Run Time Error 2427

You entered an expression that has no value.

I really need this to work as I use this field for another calculated field and it doesnt work if the #Error is there.

Hope this makes sense.

Many Thanks in advance.
Daz

 

[namliam]

you can NEVER use = when it concerns Null...

Iether you have to use "Is null" or the function IsNull(Yourfield)
The first in SQL statements
The second in expresions.

Hope this resolves your issue...

 

[dazstarr]

Hi

Thanks for your response.

I have edited my expression as follows:

=IIf(IsNull(Forms!frmInvoices!subTaxiChangesForInvoices!TCCalc),0,Forms!frmInvoices!subTaxiChangesForInvoices!TCCalc)

But I am still getting the #Error. I am entering this on the Control source of my calculated field.

 

[Mike375]

Try what the Mailman said

=IIf(Forms!frmInvoices!subTaxiChangesForInvoices!T CCalc Is Null,0,Forms!frmInvoices!subTaxiChangesForInvoices!TCC alc)

That works for me when on the main form and referring to subform

From one of my mainforms

=IIf(Forms!MasterForm!SPP!SumInsured Is Null,0,Forms!MasterForm!SPP!SumInsured)

 

[dazstarr]

Hi thanks for replying.

I have tried both variations of namliam's advice but they both resulted in the #Error. I'm not sure what I can do really.

If there is no records displayed in the subform - i think it isnt even a null value - its like there is no recordset at all so Null doesnt even apply.

I might be talking rubbish..please correct me if I am wrong!

 

[Mike375]

I just deleted the records from a subform, that is, those records relating to that person and I get 0

On the main form are two date fields - when the user enters the two dates - the subform shows any records which fall between the two dates.

One of the fields on the subform is a currency field. I have another field on the main form which totals up this currency field. All works well....

But if no records appear in the subform - my total field on the main form has '#Error'.

I have not studied that to carefully but you might have a problem from an earlier link in the chain of calculations.

 

[dazstarr]

I sorted it - the problem was that in the subform's underlying query - I had the Group By option set - i disabled it and rejigged my query and it all works now! Yey!!

Thanks for your help guys.

 

[Mike375]

PS,

Mine are referencing a textbox that is a query field.

You might have some problem arising if you have a chain of unbound textboxes where each one relies on calculated data in another textbox.

Formulas in textboxs are slow.

As an example, if you have code or a macro that opens another form and the form being opened has an ubound text box with a formula and you have code or macro to transfer the value from the textbox to a textbos on the main form, then it won't work because the setvalue action happens before the formula puts the value in the unbound textbox. But if the value on the form being opened is from a fiel in a query then all is well.

But I would be very suprised if the Is Null is not working as I have a countless number of that sort of thing. But then again this stuff is meant to pull your hair out