We help IT Professionals succeed at work.

Dlookup format for table lookup with criteria

jwjjwj
jwjjwj asked
on
I am trying to look up a selected record in a table.
The table holds Company names and a field to identify Report Paths.
This table called tblServerReportTable has three fields ServerID, Company and ReportPath.
I want to look up the 'report path' based on selecting a specific company.
In my code, I have a variable called MyCompany which identifies the company.
However  my Dlookup code,  to look up this report path, fails.
The code I am trying to use is;
MyLookup = DLookup("[ReportPath]", "tblServerReportTable", "[company] =" & [MyCompany])
The code is failing with the error
"Syntax error (missing operator) in query expression '[company]=MyCompanyName'
(MyCompanyName is the return of the variable MyCompany.)
I have tried many different quotes formats, etc without success.
Can anyone help with what is wrong with my code, or is there a better way, for example a SQL select code ?
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2013

Commented:
Try this:
MyLookup = DLookup("[ReportPath]", "tblServerReportTable", "[company] =" & CHR(34) & [MyCompany] & Chr(34))

Open in new window

Most Valuable Expert 2012
Top Expert 2013
Commented:
Text criteria needs to be delimited with quotation marks.

Also, if no records are found, DLookup will retur a null, so you need handle that possibility.

Appending an empty string is one way to do this:

MyLookup = "" & DLookup("ReportPath", "tblServerReportTable", "company =" & CHR(34) & [MyCompany] & Chr(34))

Open in new window

Most Valuable Expert 2012
Top Expert 2013

Commented:
Also...

Jim Dettman has an article here that covers this topic very thoroughly:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html

Author

Commented:
Thank you, the solution worked perfectly, first time. Also thank you for the reference to Jim Dettman, very helpful