We help IT Professionals succeed at work.

DLookup in report text box

thandel
thandel asked
on
48 Views
Last Modified: 2018-10-04
Trying to get a report to use dlookup based on a value on a form (fReportSelect) in the drop down field of LocSelect.

In my report I have the following in a text box:

=DLookUp("Abr","tOffice","Office = " & "'" & ([Forms]![FReportSelect]![LocSelect]) & "'")


But it's not pulling up values from table tOffice.

What is wrong with my syntax?
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
What is the value of ([Forms]![FReportSelect]![LocSelect]) ?
Does it exist as a value of Office in tOffice?
Is the confusion caused because of a table level lookup?

Author

Commented:
It's a text value and yes its matches up with the values in the table (the drop down list gets the value from the table I'm dlooking up with.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Is the other report open?  The implication of your syntax is that some form or report is referencing a different form or report.  Otherwise, the syntax would be:
 
=DLookUp("Abr","tOffice","Office = " & """ & LocSelect & "'")

You might also be missing quotes:

=DLookUp("Abr","tOffice","Office = " & """" & LocSelect & ""'")

Author

Commented:
It's a form and yes the form is open.... I tried both your suggestions but I get an error "The expression you entered has an invalid string"
CERTIFIED EXPERT

Commented:
try this

=DLookUp("Abr","tOffice","Office = " & "'" & ([Forms]![FReportSelect].[LocSelect]) & "'")

Open in new window

or
=DLookUp("Abr","tOffice","Office = " & "'" & [Forms]![FReportSelect].[LocSelect] & "'")

Open in new window

Author

Commented:
Odd no errors but nothing is displaying.    If I use just [Forms]![FReportSelect].[LocSelect] it works OK to display the value from the drop down list on the form.
CERTIFIED EXPERT

Commented:
are you sure there are none null values on the table?
what happens when you try the actual value in debug window?

Author

Commented:
nope no null values.  Not sure how to the value in the debug in window - sorry
CERTIFIED EXPERT

Commented:
?DLookUp("Abr","tOffice","Office = " & "'" & PutTheValueOnFormHere & "'")
and press enter

Author

Commented:
Where do I type that in to?

Author

Commented:
I think in the Immediate window?

I did that and go the following error:

"Compile error, expected: list separator or )"
CERTIFIED EXPERT

Commented:
in code look for the immediate window.
or you can also type that in any query
CERTIFIED EXPERT

Commented:
what did you you type, post the value

Author

Commented:
?DLookUp("Abr","tOffice","Office = " & "'" & Central Square & "'")

Author

Commented:
IF I do this ?[Forms]![FReportSelect]![LocSelect] I get the correct value on the form

Author

Commented:
Seems to be some sort of issue with dlookup
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
That worked

Author

Commented:
so ?DLookUp("Abr","tOffice","Office = '" & [Forms]![FReportSelect]![LocSelect] & "'") works!

Author

Commented:
Got it now and I learned about the debug window! Thanks!