#Error and #Type! in blank records

[Tophan]

Hello,

I have two slightly annoying issues. I have a DLookup formula on my data sheet subform that is working, however, in the next blank record, that field has an #Error message which is a little irritating to see. But when I key in data the #Error goes away. Below is the formula:

=IIf([PrgmID]="0"," ",DLookUp("[Rate]","tblPrograms","PrgmID=" & [PrgmID]))

A similar thing is happening in the total field. The formula is working but in the next blank record the field where the formula is, is displaying #Type! which remains until I key in data. This is the formula:

=IIf([PrgmID]="0",[OtherRate]*[Qty],[txtPrgmRate]*[Qty])

Any idea why two working formulas would display these errors in the blank records?

 

[Uncle Gizmo]

It's probably because your text Fields carry a Null value before a record is created. You might be able to use the NZ function to get rid of the Null..

 

[Uncle Gizmo]

The other thing you could do is go to the table and set a default value for the fields, that might cure the problem as well...

 

[Tophan]

Hi,

Adding Nz didn't work and both fields are in the form only, not in the tables. I tried setting the Default Values in the form properties to 0 but that didn't work

 

[Uncle Gizmo]

Maybe post a sample dB with roughly what you've got in it, a simplified one, so we can have a play!

 

[Isaac]

Adding Nz didn't work

Can you post your actual code?

 

[bastanu]

For the control where you get the #Error can you try to just have the dlookup in the control source:
=DLookUp("[Rate]","tblPrograms","PrgmID=" & [PrgmID])
For the other one you can try to create a calculated field in the subform's record source:
Rate:IIf([PrgmID]="0",[OtherRate],[PrgmRate]) then in the control source enter = [Rate]*[QTY]

Cheers,

 

[Tophan]

Thank you all for your help. For the first formula I took out the IIF part and that worked.

For the second one, I split the formula into two separate calculated controls and then totalled those two. No #type! or #Error messages.

Thanks again

I might have another query later on