Getting rid of #Errors

  • Thread starter Thread starter grep
  • Start date Start date
G

grep

I have several calculated fields that rely on lookups of on data that
may not yet exist. (This occurs most often on New records). Often the
result of the calculation is an #Error condition, which it says right in
the field. I'd like to get rid of the #Error message. Just being blank
would be better. How?

TIA

grep
 
You can use an IIf or Nz function to get rid of the errors.

Example:
=Nz([Field1])+Nz([Field2])
or
=IIf(IsDate([Field1]), [Field1], "")
or
=IIf(IsNull([Field1]), "No Entry", [Field1])
or
=Nz([Field1], "No Entry")

The last two are equivalent. To give more specific examples, I would have to
see your equations and know what data types the fields are.
 
I'll see if I can find a useful direct example that wouldn't be too
convoluted - hell, my entire coding might be - but here's specifically
where I'm trying to go:

The field is set to: dlookup("Subtotal1", "tblAllWidgets", "[OrderID]= "
& OrderID) + nz(Subtotal2, 0)

Now, the problem is that until I've actually told the current record
what OrderID it's associated with (i.e. while it's still in NewRecord
state), "[OrderID]= " & OrderID produces an error condition because
Me.OrderID = Null. So the entire lookup can't happen. Hence #Error.

That's what I'm trying to relieve. I'd prefer it to be blank.

grep
 
See if this will work for you.

=IIf(IsNull([OrderID]), "", DLookup("Subtotal1", "tblAllWidgets",
"[OrderID]= " & OrderID) + nz(Subtotal2, 0))

If the "" gives a data type mismatch, try Null instead.
 
Wayne said:
See if this will work for you.

=IIf(IsNull([OrderID]), "", DLookup("Subtotal1", "tblAllWidgets",
"[OrderID]= " & OrderID) + nz(Subtotal2, 0))

If the "" gives a data type mismatch, try Null instead.
Click to expand...

Hey Wayne - as soon as I looked at your suggestion, I knew it would
work. It did. The way I was looking at it was so much more complicated.
Thanks much!!

grep
 
Back
Top