How can I hide '#Error' after DBLookup?

[Jade74]

How can I hide '#Error' in Text Field after DLookup?

Hi there,

I've been struggeling with this problem for hours now and I'm sure there is an easy fix. Searched the forum to no avail

I've got a form with a subform in which combo-boxes are used to add staff to projects. Then, their Role is selected via a second combo-box. I display that role via a DBlookup query in a text field. This has to be done that way to keep the value, as I use a requery for the second combo box for every row.

Now this works well, but the ugly thing is that the DBLookup function always displays "#Error" in the text field, until something has been selected.

I've tried to use conditional formatting for the text box, to set the text colour to white, if the value = "#Error", but Access just tells me "The expression you entered has an invalid date value"

Does anyone have any leads on how to hide this error?
Thanks in advance!

 

[Jade74]

Ok, I kinda solved it by adding a dummy Role with a blank description to the Roles table. Every new entry in the subform-bound table now defaults to that blank role. Not pretty, but it works...

I'm all ears for a more pretty solution, of course

 

[RussG]

I have a similar issue with a calculating Age , until a date is entered I get the #error.
Cheers
RussG

 

[Fernando]

Try if the value is < than 0

 

[boblarson]

Look into using the NZ function to handle nulls. Until a date is entered, it is null.

 

[Jade74]

Look into using the NZ function to handle nulls.

Thanks Bob, I finally worked it out. Writing a whole little program in ONE line kinda sucks... and it's not easy to debug either.

My final "program" was a derivate of:

=IIf(Nz([RoleID],0)=0,"True","False")

where I replaced "True" with "" and "False" with the original DLookup query.

Yay!

 

[boblarson]

cool - happy you got it worked out.