Nz & Dlookup problem

[Donna]

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

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

 

[Ofer Cohen]

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

 

[Donna]

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
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






--
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]),"")

 

[Evi]

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

 

[Ofer Cohen]

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

 

[Marshall Barton]

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.

 

[Evi]

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
instead of Forms. Would the syntax be different on a form?

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






--
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]),"")

 

[Donna]

thank you - that did it

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


--
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]),"")