DLookUp - Syntax (1 Viewer)

ardy

Registered User.
Local time
Today, 02:58
Joined
Sep 24, 2012
Messages
98
Hello All,
I am trying to pull a number(Not string) from a form I am working on. I think my issue is syntax but can't figure this out and I have tried several version all ending up in #Error

Domain = Access_Agreement
expression = APN (number)
criteria = lookup from a field from the form (Well_ID is String)

I have pretty good feeling the issue is in the syntax of the criteria, but can't nail it down. Any help is appreciated.

Here is what I have tried so far.
Code:
=DLookUp("APN","Access_Agreement","Well_ID = & [Forms]![Facility_Details]![Well_ID])
=DLookUp("APN","Access_Agreement","Well_ID = '" & [Forms]![Facility_Details]![Well_ID]"'")
=DLookUp("APN","Access_Agreement","Well_ID =  & Me.Well_ID")

Ardy
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:58
Joined
Feb 28, 2001
Messages
27,001
Your last one came close.

Code:
=DLookUp("APN","Access_Agreement","Well_ID =  '" & Me.Well_ID & "'")

Should work better. Watch out for the apostrophes (single-quotes) and double-quotes being close. NOTE that this DOES require that you use the ME construct on the form where [WELL_ID] appears as a control.

Your second attempt also came close but omitted a second "&" after [WELL_ID].[/CODE]
 

ardy

Registered User.
Local time
Today, 02:58
Joined
Sep 24, 2012
Messages
98
Thanks for the quick reply.....

I tried
Code:
=DLookUp("APN","Access_Agreement","Well_ID =  '" & Me.Well_ID & "'")

I get the #Name? error. I did check the Control Source in the form and it is Well_ID, I am not sure if it make a difference or not by the expression builder automatically add [] to the criteria. "Well_ID = ' " & [Me].[Well_ID] & " ' ". I also double check the table and field name and they are also correct.

Any Idea as to why I get the #Name?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:58
Joined
Oct 29, 2018
Messages
21,358
The presence of the equals sign at the beginning indicates you're trying to use this expression in the Control Source of an unbound Textbox. Is that correct? If so, you cannot use Me there. Try removing it and just use [Well_ID].

Edit: Fixed typo. Thanks to @cheekybuddha for pointing it out.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:58
Joined
Feb 28, 2001
Messages
27,001
You get a #Name# error when the problem isn't syntax, but in finding the object. Just for snorts and giggles (that's the polite version of that phrase), put brackets around APN, Access_Agreement, and Well_ID. Bracketing sometimes helps. Sometimes not. But it's an easy test.
 

bob fitz

AWF VIP
Local time
Today, 09:58
Joined
May 23, 2011
Messages
4,717
Pardon me for poking my nose in, but could it be that the textbox isn't actually named "Well_ID".
 

ardy

Registered User.
Local time
Today, 02:58
Joined
Sep 24, 2012
Messages
98
theDBguy - The_Doc_Man I do appreciate the help.....
theDBguy you are right I am using the unbounded in a form to pull info from another table.....

Here the versions I tried & get the same error.

Code:
=DLookUp([APN],[Access_Agreement],"[Well_ID] =  ' " & [Me].[Well_ID] & " ' ")

Code:
=DLookUp([APN],[Access_Agreement],"Well_ID =  ' " & [Me].[Well_ID] & " ' ")
 

Minty

AWF VIP
Local time
Today, 09:58
Joined
Jul 26, 2013
Messages
10,355
Could it be that Well_ID is a Number?
Not a text field?
 

ardy

Registered User.
Local time
Today, 02:58
Joined
Sep 24, 2012
Messages
98
Hello bob fitz..... Thank for joining in...... see below

Facility_Details.jpg
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:58
Joined
Oct 29, 2018
Messages
21,358
theDBguy - The_Doc_Man I do appreciate the help.....
theDBguy you are right I am using the unbounded in a form to pull info from another table.....

Here the versions I tried & get the same error.

Code:
=DLookUp([APN],[Access_Agreement],"[Well_ID] =  ' " & [Me].[Well_ID] & " ' ")

Code:
=DLookUp([APN],[Access_Agreement],"Well_ID =  ' " & [Me].[Well_ID] & " ' ")
But didn't you understand what I was recommending? I was saying to try it this way.
Code:
=DLookUp("[APN]","[Access_Agreement]","Well_ID ='" & [Well_ID] & "'")

Edit: Fixed typo. Thanks to @CJ_London for pointing it out.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 09:58
Joined
Feb 19, 2013
Messages
16,553
this

=DLookUp("APN","Access_Agreement","Well_ID = '" & Me.Well_ID & "'")

should work if the unbound control is on the same form - so begs the the question - where are you using this DLookup? On the same form? a different form? a subform? a parent form? Ditto the Well_ID control

@theDBguy -need quotes?

DLookUp([APN],[Access_Agreement]
 

ardy

Registered User.
Local time
Today, 02:58
Joined
Sep 24, 2012
Messages
98
Opps, Sorry I missed that One but still same error
Tray_3.jpg
#Name?
 

ardy

Registered User.
Local time
Today, 02:58
Joined
Sep 24, 2012
Messages
98
this

=DLookUp("APN","Access_Agreement","Well_ID = '" & Me.Well_ID & "'")

should work if the unbound control is on the same form - so begs the the question - where are you using this DLookup? On the same form? a different form? a subform? a parent form? Ditto the Well_ID control

@theDBguy -need quotes?

DLookUp([APN],[Access_Agreement]
Good Question...... I thought that also, Not being very good in terminology I am using the unbounded box in the same form I am doing the Dlookup. The form is tabbed form
Facility_Details_PropertSheet.jpg
 

cheekybuddha

AWF VIP
Local time
Today, 09:58
Joined
Jul 21, 2014
Messages
2,237
Code:
=DLookUp("APN","Access_Agreement","Well_ID = '" & [Well_ID] & "'")
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:58
Joined
Feb 19, 2013
Messages
16,553
try navigating for each value. in your expression builder, click on GWM_wellProfile, select the form, select the control for me.well_id
 

Users who are viewing this thread

Top Bottom