#Error and #Type! in blank records (1 Viewer)

Tophan

Registered User.
Local time
Today, 01:43
Joined
Mar 27, 2011
Messages
362
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:

Code:
=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:

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

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

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 05:43
Joined
Jul 9, 2003
Messages
16,245
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

Nifty Access Guy
Staff member
Local time
Today, 05:43
Joined
Jul 9, 2003
Messages
16,245
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

Registered User.
Local time
Today, 01:43
Joined
Mar 27, 2011
Messages
362
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

Nifty Access Guy
Staff member
Local time
Today, 05:43
Joined
Jul 9, 2003
Messages
16,245
Maybe post a sample dB with roughly what you've got in it, a simplified one, so we can have a play!
 

bastanu

AWF VIP
Local time
Yesterday, 22:43
Joined
Apr 13, 2010
Messages
1,401
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

Registered User.
Local time
Today, 01:43
Joined
Mar 27, 2011
Messages
362
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 :D

I might have another query later on ;)
 

Users who are viewing this thread

Top Bottom