Hide #Name error in a text box (1 Viewer)

[djackson]

Hi,

I'm sure there must be quite an easy solution to this, but i cant find it anywhere.

I have 2 text boxes on a form with the following in the control source:

=DLookUp("[Phone Call]","qryCallsCount")
=DLookUp("[Visit]","qryCallsCount")

The qryCallsCount is a crosstab query that returns the counts of the Phone Calls and Visits made to a particular customer.

The problem is that not every customer has been visited/called, so they do not have the field created in the crosstab query, therefore the Dlookup can't find the field and returns a #Name error.

Instead of displaying this error, i would like it to be blank.

I looked into using NZ, but that seems to be only for Null values in existing fields. Is there a way to hide, or display blank ("") if there is an error?
Something similat to the IfError function in Excel 2007?

All help much appreciated.

Dave

 

[HiTechCoach]

Try:

IsError()


=IIf( IsError(DLookUp("[Phone Call]","qryCallsCount")) = True, 0, DLookUp("[Phone Call]","qryCallsCount") )

 

[ajetrumpet]

one thing you can do here Dave is check the value of the box when the error comes up. check it in the immediate window. and write an IIF() statement based on that in the control source. maybe that would work?

 

[seopositive2]

Me![subformCONTAINERname]![controlname].Value
but the easier way to do it is to use the Expression Builder: in the Data Source property of the control on the main form, click on the ellipsis (the ...).

 

[djackson]

Try:

IsError()


=IIf( IsError(DLookUp("[Phone Call]","qryCallsCount")) = True, 0, DLookUp("[Phone Call]","qryCallsCount") )

Hi Boyd,

Thank you very much for your suggestion, but i still cant get it to work. I'm still getting the #NAME error. Is the IsError function only available in Access 2007 (i'm using 2000), as the IfError is only available in Excel 2007?
If so, is there a workaround? If not, any clue what i'm doing wrong?

Adam - I'm pretty new to Access and don't know how to get the immediate window up, and probably wouldnt know what to do with it if i did. Sorry.

Seopositive2 - I dont have a subform, and i'm assuming your suggestion requires one, or i'm misunderstanding what you're saying.

Just for reference, i now have the following in the two text boxes, with both returning #NAME when the field can't be found:

=IIf(IsError(DLookUp("[visit]","qryCallsCount"))=True,0,DLookUp("[visit]","qryCallsCount"))

=DLookUp("[Phone Call]","qryCallsCount")

Any ideas what i'm doing wrong??

Cheers,
Dave