DLookup in report text box

thandel
thandel used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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 & ""'")
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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"
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.
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
?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 )"
in code look for the immediate window.
or you can also type that in any query
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
try this
?DLookUp("Abr","tOffice","Office = '" & "Central Square" & "'")

Open in new window

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial