[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Dlookup not working - getting #Name?

Posted on 2013-06-26
12
Medium Priority
?
129 Views
Last Modified: 2014-10-16
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?
0
Comment
Question by:Evert Jor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 
LVL 58
ID: 39279280
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
 
LVL 58
ID: 39279290
BTW, if [vare_ID] is a numeric field, it would be:

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

Jim
0
 

Author Comment

by:Evert Jor
ID: 39279385
I'm getting invalid syntax on both your suggestions.
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:Evert Jor
ID: 39279402
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39279611
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
 

Author Comment

by:Evert Jor
ID: 39279844
Still getting the same syntax error cactus_data.
0
 

Author Comment

by:Evert Jor
ID: 39280214
Hi.

Now I'm only getting bank values in the Dlookup - but no errors. The values exist in the underlying query.
0
 

Author Comment

by:Evert Jor
ID: 39280226
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39280444
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
 

Accepted Solution

by:
Evert Jor earned 0 total points
ID: 40374940
Have still not gotten a working solution. Please close this ticket.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40374945
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
 

Author Closing Comment

by:Evert Jor
ID: 40383883
I have not received a suggestion that solves the problem.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question