ELookup #Name? Issue

[ardy]

Hello all:
I am trying to use Allen Browne’s Extended DLookup() http://allenbrowne.com/ser-42.html namely ELookup(), But am running to a bit of problem. At first I got some syntax errors in which I figured out but now I am hitting a wall with No result…..I am hoping some of you might be able to shed some light on this…….


Let me explain the landscape……
I am using this in a form(A) an unbounded control. The form I am using is referencing “Table A(Facility)” The unbounded control is using the expression(ELookup) to look at a value(number) using a control box Value(string) in form A… It should work but it is giving me the #Name? :banghead:……. Any help is appreciated…..

[FONT=&quot]=ELookup("[MP_Elev]","Elevation","Well_ID = " & [Forms].[Facility_Datails].[Well_ID],"ChangeDate DESC")[/FONT]

Regards
Ardy

 

[Uncle Gizmo]

Is form "Facility_Datails" open?

 

[ardy]

Yes The form will open (see Attachment)

 

[vbaInet]

See here:

[Forms][COLOR="red"]![/COLOR][Facility_D[COLOR="Red"]a[/COLOR]tails][COLOR="red"]![/COLOR][Well_ID]

 

[ardy]

Changed it as suggested, same result (#Name?)... I think I have Already tried this,,,

=ELookup("[MP_Elev]","Elevation","Well_ID = " & [Forms]![Facility_Datails]![Well_ID],"ChangeDate DESC")

Ardy

 

[vbaInet]

Are you sure it's Datails and not Details?

 

[pr2-eugin]

Your Form is called Facility_Details not Facility_Datails. Check again.

 

[ardy]

OK.... This is really embarrassing, You are right it's Details not Datails.....
Corrected that and now it gives me a syntax error

Syntax error (missing operator) in query expression 'Well_ID = 05S02W32E002'.

The Well_ID is string alpha numeric.......

 

[vbaInet]

Have a look at the following link paying attention only to the parts highlighted in red. It will shows how to handle Number, Text and Date types.

http://baldyweb.com/wherecondition.htm

 

[ardy]

Thanks for the info......I have modified it as per the Article........
The ELookup is looking for a numeric value (MP_Elev) using an String, Alpha Numeric (Well_ID), If I understand this the Expression should be build for Text, if I am right it should look like this

=ELookup("[MP_Elev]","Elevation","Well_ID = ' " & [Forms]![Facility_Details]![Well_ID] & " ' ","ChangeDate DESC")

No Syntax error but I don't get any results it comes up blank......The test data I have it should come up as 102

 

[vbaInet]

Try this (exactly as-is):

=ELookup("[MP_Elev]", "Elevation", "Well_ID = '" & [Forms]![Facility_Details]![Well_ID] & "'", "ChangeDate DESC")

 

[ardy]

It Worked.......Great......Thanks.....

So Is it the extra space that prohibit it from giving the result.......?

 

[vbaInet]

That's right. So the text "facility" is different from " facility "

 

[ardy]

Thank you very much for your help