Dlookup is giving an #error

[aron.ridgway]

Hi there i am trying to do a Dlookup in an textbox which is looking at a query that isnt a bound source of the form. my code is

 =DLookUp("[EmailAddress]","[qrySupplierEmail]","[SupplierPK]=" & [Forms]![frmPurchaseEntry]![SupplierFK])

Im looking for the field Emailaddress in the query, matching the supplier Primary key to the Form Supplier Foreign key.

Email address is hyperlink, the PK and fk are short text. I've used a simular Dlookup before but having no luck with this one?

Am i doing anything obviously wrong?

thanks

 

[JHB]

If [SupplierPK] is a text field type, then you are missing two '

=DLookUp("[EmailAddress]","[qrySupplierEmail]","[SupplierPK]='" & [Forms]![frmPurchaseEntry]![SupplierFK] & "'")

 

[bob fitz]

Perhaps that needs to be something like:
=DLookUp("[EmailAddress]","[qrySupplierEmail]","[SupplierPK]='" & [Forms]![frmPurchaseEntry]![SupplierFK])[/CODE] & "'")

 

[aron.ridgway]

thank you for the reply, I have tried the following

 =DLookUp("[EmailAddress]","[qrySupplierEmail]","[SupplierPK]='" & [Forms]![frmPurchaseEntry]![SupplierFK] & "'")

with the same result?

The error# flashes in the textbox?

 

[aron.ridgway]

I moved the ' further along and it is now working

 =DLookUp("[EmailAddress]","[qrySupplierEmail]","'[SupplierPK]=" & [Forms]![frmPurchaseEntry]![SupplierFK] & "'")

thanks for pointing me in the right direction!

 

[bob fitz]

Glad you have it working but a bit surprised by the solution you posted. I'm sure the usual syntax would be as posted by myself and JHB. I would like to hear from anyone who can explain why it is different in this case.

 

[JHB]

I moved the ' further along and it is now working

 =DLookUp("[EmailAddress]","[qrySupplierEmail]","'[SupplierPK]=" & [Forms]![frmPurchaseEntry]![SupplierFK] & "'")

thanks for pointing me in the right direction!

It should NOW give an error so as bob fitz I'm really surprised, could we get an small copy of your database, with some sample data, so that we could investigate why it work by your code?