Dlookup is giving an #error (1 Viewer)

A

aron.ridgway

Registered User.

Local time

Today, 12:52

Joined

Apr 1, 2014

Messages

148

• May 12, 2014

• #1

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

Code:Copy to clipboard

 =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

 

J

JHB

Have been here a while

Local time

Today, 13:52

Joined

Jun 17, 2012

Messages

7,732

• May 12, 2014

• #2

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

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

Click to expand...

 

bob fitz

AWF VIP

Local time

Today, 12:52

Joined

May 23, 2011

Messages

4,722

• May 12, 2014

• #3

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

 

A

aron.ridgway

Registered User.

Local time

Today, 12:52

Joined

Apr 1, 2014

Messages

148

• May 12, 2014

• #4

thank you for the reply, I have tried the following

Code:Copy to clipboard

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

with the same result?

The error# flashes in the textbox?

 

A

aron.ridgway

Registered User.

Local time

Today, 12:52

Joined

Apr 1, 2014

Messages

148

• May 12, 2014

• #5

I moved the ' further along and it is now working

Code:Copy to clipboard

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

thanks for pointing me in the right direction!

 

bob fitz

AWF VIP

Local time

Today, 12:52

Joined

May 23, 2011

Messages

4,722

• May 12, 2014

• #6

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.

 

J

JHB

Have been here a while

Local time

Today, 13:52

Joined

Jun 17, 2012

Messages

7,732

• May 12, 2014

• #7

aron.ridgway said:

I moved the ' further along and it is now working

Code:Copy to clipboard

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

thanks for pointing me in the right direction!

Click to expand...

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?