We help IT Professionals succeed at work.

Error can't find table ??

SteveL13
SteveL13 asked
on
138 Views
Last Modified: 2017-03-10
I am trying to open a report filtered by the current user.  But I'm getting an error saying the d/b can't find the table or it is misspelled.  It is spelled correctly.  Do I have a syntax issue with this code...

DoCmd.OpenReport "rptReferrals", acViewPreview, , "[Agent] = '" & DLookup("[CurrentUser]", "LOCALtblCurrentUser" & "'"), acWindowNormal
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
try something like

dim strAgent as string
strAgent=Dlookup("CurrentUser", "LOCALtblCurrentUser")

debug.print strAgent

DoCmd.OpenReport "rptReferrals", acViewPreview, , "[Agent] ="" & strAgent & "'"

or this

DoCmd.OpenReport "rptReferrals", acViewPreview, , "[Agent] = '" & DLookup("[CurrentUser]", "LOCALtblCurrentUser") & "'" acWindowNormal
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Check the syntax of the DLookup().  What is the --- & "'"  --- before the ending parenthesis?

Author

Commented:
Rey,

I am trying your first suggestion but am getting "Syntax error in string in query expression '[Agent] =" & strAgent & '

The degug.print returns the correct string of characters.
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.