asked on

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?

Jim Dettman (EE MVE)

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.

Jim Dettman (EE MVE)

BTW, if [vare_ID] is a numeric field, it would be:

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

Jim

Evert Jor

ASKER

I'm getting invalid syntax on both your suggestions.

Evert Jor

ASKER

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.

Gustav Brock

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

Evert Jor

ASKER

Still getting the same syntax error cactus_data.

Evert Jor

ASKER

Hi.

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

Evert Jor

ASKER

Neither Dlookup or Elookup seems to work. See: https://www.experts-exchange.com/questions/27991704/Controls-w-DLookup-ControlSource-endlessly-recalculating-in-Windows-8-Access-2003.html

It just returns empty strings. I'm using Windows8 with the 32 bit version of Access.

Gustav Brock

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

ASKER CERTIFIED SOLUTION

Evert Jor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

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

Evert Jor

ASKER

I have not received a suggestion that solves the problem.