DLookUp - Syntax

[ardy]

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.

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

Your last one came close.

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

Thanks for the quick reply.....

I tried

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

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.

 

[The_Doc_Man]

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]

Pardon me for poking my nose in, but could it be that the textbox isn't actually named "Well_ID".

 

[ardy]

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.

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

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

 

[Minty]

Could it be that Well_ID is a Number?
Not a text field?

 

[ardy]

Hello bob fitz..... Thank for joining in...... see below

 

[ardy]

Could it be that Well_ID is a Number?
Not a text field?

Well_ID is a String

 

[theDBguy]

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.

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

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

=DLookUp("[APN]","[Access_Agreement]","Well_ID ='" & [Well_ID] & "'")

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

 

[CJ_London]

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]

Opps, Sorry I missed that One but still same error

#Name?

 

[cheekybuddha]

But didn't you understand what I was recommending?

Probably cos you made a typo!

Is that correct? If so, you can use Me there.

I think you meant '... can't use ...'

 

[ardy]

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

 

[theDBguy]

@theDBguy -need quotes?

Of course, I fell victim to the Copy & Paste monster again. Thanks!

 

[cheekybuddha]

=DLookUp("APN","Access_Agreement","Well_ID = '" & [Well_ID] & "'")

 

[theDBguy]

Probably cos you made a typo!


I think you meant '... can't use ...'

I haven't had breakfast yet, so maybe I can't think straight yet.

 

[CJ_London]

try navigating for each value. in your expression builder, click on GWM_wellProfile, select the form, select the control for me.well_id

 

[cheekybuddha]

I haven't had breakfast yet, so maybe I can't think straight yet.