How to hide #Error in text box on form ? 1

[MSealy]

Hi,

How can I go about hiding the #Error appearing in a text box please ? Ideally so it shows a blank.

This relates to a value depending on input from e.g. box 1 - so when that box 1 is empty, #error appears in box 2.

I'm currently using dlookup as the control source for box 2. Is it possible to add some code to the end of that, or will I need to insert some code ?

Thanks and regards.

 

[PHV]

Have a look at the Nz function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MSealy]

Ok thanks. This is the control source propery of box 2...

=DLookUp("[FILE REF:]","tblINVOICES",Forms!frmMAIN!txtInvSearch & " Between [tblINVOICES]![INCLUDES:] And [to]")

Which without any input in box 1 (txtInvSearch) produces #Error.

I've tried ..

=If Nz(DLookUp("[FILE REF:]","tblINVOICES",Forms!frmMAIN!txtInvSearch & " Between [tblINVOICES]![INCLUDES:] And [to]"))

.. but this just produce #Name? instead. Removing the If just reverts to #Error. I've tried to add it onto the end, but it won't have it.

Would something like 'Else " "' work ?

 

[PHV]

What about this ?
=DLookUp("[FILE REF:]","tblINVOICES",Nz(Forms!frmMAIN!txtInvSearch, 0) & " Between [tblINVOICES]![INCLUDES:] And [to]")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

What about this ?
=DLookUp("[FILE REF:]","tblINVOICES",Nz(Forms!frmMAIN!txtInvSearch, 0) & " Between [INCLUDES:] And [to]")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MSealy]

Great that works perfectly. Thanks.

If I were also to apply this in an expression which uses dates, where would I put the Nz ?

I was trying to change ..
=DLookUp("[FILE REF:]","tblPYTRUN","#" & [Forms]![frmMAIN]![txtPytRunSearch] & "# Between [INCLUDES:] AND [to]")

into..

=DLookUp("[FILE REF:]","tblPYTRUN","#" & Nz([Forms]![frmMAIN]![txtPytRunSearch],0) & "# Between [INCLUDES:] AND [to]")

but that didn't work, even though it should based on your answer.

 

[PHV]

Perhaps something like this:
=DLookUp("[FILE REF:]","tblPYTRUN","#" & Nz([Forms]![frmMAIN]![txtPytRunSearch],"1900-01-01") & "# Between [INCLUDES:] AND [to]")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886