Dlookup not working - getting #Name?

Hi.

I'm trying to do a Dlookup in a subform, but pulling some data from the main form:

I'm using the following as a record source:
=DLookUp("Location";"Q_strukturvaretotal";"id_vare"="'" & [vare_id] & "'")

I'm getting #Name! instead of the lookup value. I use [vare_id] to refer to the text field in the main form. What is the correct syntax for correctly refering to a text field in the main form in this instance?
Evert JorDVM/ResearcherAsked:
Who is Participating?
 
Evert JorDVM/ResearcherAuthor Commented:
Have still not gotten a working solution. Please close this ticket.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You need comma's between the arguments, not semi-colon's and your delimiting on the text value was not correct:

=DLookUp("Location","Q_strukturvaretotal","id_vare='" & [vare_id] & "'")

or more clearly:

=DLookUp("Location","Q_strukturvaretotal","id_vare=" & chr$(34) & [vare_id] & chr$(34))

chr$(34) giving you a quote (") character.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, if [vare_ID] is a numeric field, it would be:

=DLookUp("Location","Q_strukturvaretotal","id_vare=" & [vare_id] )

Jim
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Evert JorDVM/ResearcherAuthor Commented:
I'm getting invalid syntax on both your suggestions.
0
 
Evert JorDVM/ResearcherAuthor Commented:
This is in a form not in a query. As far as I know I have to use semicolons in this instance (ie. when refering to the Dlookup function as a record source).

Tested by switching to commas - and I get the "syntax error" as described in my last posting.
0
 
Gustav BrockCIOCommented:
Of course you need your localized semicolons. Thus, for a numeric id:

=DLookUp("Location";"Q_strukturvaretotal";"id_vare = " & [vare_id] & "")

or for a string id:

=DLookUp("Location";"Q_strukturvaretotal";"id_vare = '" & [vare_id] & "'")

/gustav
0
 
Evert JorDVM/ResearcherAuthor Commented:
Still getting the same syntax error cactus_data.
0
 
Evert JorDVM/ResearcherAuthor Commented:
Hi.

Now I'm only getting bank values in the Dlookup - but no errors. The values exist in the underlying query.
0
 
Evert JorDVM/ResearcherAuthor Commented:
Neither Dlookup or Elookup seems to work. See: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27991704.html

It just returns empty strings. I'm using Windows8 with the 32 bit version of Access.
0
 
Gustav BrockCIOCommented:
Then either  [vare_id] contains no value (try with =[vare_id]), or you need to rename the textbox of which this is your controlsource.

/gustav
0
 
Gustav BrockCIOCommented:
Neither did you for more than a year provide any feedback.

Anyway, if vare_id is on the parent form, you may need this modification:

=DLookUp("Location";"Q_strukturvaretotal";"id_vare = '" & Forms!NameOfTheParentForm![vare_id] & "'")

 /gustav
0
 
Evert JorDVM/ResearcherAuthor Commented:
I have not received a suggestion that solves the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.