Nz & Dlookup problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Donna - 20 Mar 2008 22:52 GMT

I keep getting the #Error in my text box. What is wrong with this syntax?

=Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")

Reply to this Message

Ofer Cohen - 21 Mar 2008 00:24 GMT

Check the Dlookup
1. Check if the name are correct
2. If FacID field is a text field and not numeric, add a single quote before
and after the criteria

DLookUp("[Affiliation]","tblFaculty","[FacID] ='" & [Outside] & "'")

3. In any case, try running the dlookup without the text box using the
Immidate windows (press Ctrl+g) and then type
?DLookUp("[Affiliation]","tblFaculty","[FacID]
=EnterHereAValueThatWillReturnData")

Press Enter and see if any value returned or you are getting an error message

Signature

Good Luck
BS"D

> I keep getting the #Error in my text box. What is wrong with this syntax?
>
> =Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")

Reply to this Message

Donna - 21 Mar 2008 00:34 GMT

It works fine when there is data; but when the field is empty, I get #Error
in the text box. It just dawned on me that I "posted" this under Reports
instead of Forms. Would the syntax be different on a form?

> Check the Dlookup
> 1. Check if the name are correct
[quoted text clipped - 13 lines]
> >
> > =Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")

Reply to this Message

Evi - 21 Mar 2008 10:31 GMT

No, the syntax for a DLookup is the same whereever it appears.
What sort of data is in the fields in your dlookup? Date? Text? Yes/No tick
box?
Number? Find out by looking at the DataType column in Table Design View of
the table that has these fields.

List them eg
Affiliation - Yes/No

Evi

> It works fine when there is data; but when the field is empty, I get #Error
> in the text box. It just dawned on me that I "posted" this under Reports
[quoted text clipped - 21 lines]
> > >
> > > =Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")

Reply to this Message

Marshall Barton - 21 Mar 2008 00:31 GMT

>I keep getting the #Error in my text box. What is wrong with this syntax?
>
>=Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")

Nothing syntactically wrong that I can see.

If FacID is a Text field in its table, then you need to use:

..., "[FacID] =""" & [Outside] & """")

I seriously doubt that you need to use Nz for this and it is
possible that a result of "" could cause a problem.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Evi - 21 Mar 2008 00:35 GMT

Perhaps you could use

IIF(NZ(DLookup("[Affiliation]",TblFaculty","[FacID]=" & [Outside])) =
0,"",DLookup("[Affiliation]",TblFaculty","[FacID]=" & [Outside]))

Are FacID and Outside both number fields?

Evi

> I keep getting the #Error in my text box. What is wrong with this syntax?
>
> =Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")

Reply to this Message

Ofer Cohen - 21 Mar 2008 01:14 GMT

In that case try

=DLookUp("[Affiliation]","tblFaculty","[FacID] =" & Nz([Outside],0))

Assuming that 0 return no value, so the nz will add a value to the criteria,
other wise when there is no value the dlookup act as

=DLookUp("[Affiliation]","tblFaculty","[FacID] =")

And that will cause an error

Signature

Good Luck
BS"D

> I keep getting the #Error in my text box. What is wrong with this syntax?
>
> =Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")

Reply to this Message

Donna - 24 Mar 2008 18:07 GMT

thank you - that did it

> In that case try
>
[quoted text clipped - 10 lines]
> >
> > =Nz(DLookUp("[Affiliation]","tblFaculty","[FacID] =" & [Outside]),"")

Reply to this Message