DLookup on a field

[Darren26]

In a previous database i used the dlookup function as the conrol source for an unbound text box.
it looked like this:

=DLookUp("[Name]","tbl_GP_Details","[GPNumber] =" & [Forms]![frmPatient_Dets]![GPNumber])

This populated the text box with the name from the gp details where the gp number was the same as the gp number on the form.

This worked fine but now i need to create something similar however instead of using a number to link them (GPNumber) the field is text this time, the above code does not work. Do i have to add anything else so it recognizes it to be a text field and not numeric??

Hope this makes sense

 

[Dave Eyley]

=DLookUp("[Name]","tbl_GP_Details","[GPNumber] =[Forms]![frmPatient_Dets]![GPNumber]")

It always wordks for me regardless of the link being text or number.
As long as the relational link is unique, of course.

HTH

Dave Eyley

 

[iago18]

Try this:

=DLookUp("[Name]","tbl_GP_Details","[GPNumber] ='" & [Forms]![frmPatient_Dets]![GPNumber] & "'")

Strings variables need to be concatenated with single quotes around them.

 

[Dave Eyley]

Only if you use the '&' sign to concatenate the staement. If you leave it out you don't get the problem...At least, I've never had the problem doing it this way.

Dave Eyley

 

[iago18]

Yeah, you might be right. Another easily solution is simply to bind the data source to the field itself. Why use Dlookup?

 

[Pat Hartman]

Many DLookup()s can and should be replaced by modifying the form/report's ControlSource to be a query that joins the main table to the lookup table. Two caveats.
1. Make sure to set the locked property for the lookup fields to Yes. You don't want the user to accidentally change the lookup values.
2. If the lookup value is optional, use a Left Join in the query rather than an Inner Join.