How to use DLOOKUP with the Like * Functions

  • Thread starter Thread starter dbarmer
  • Start date Start date
D

dbarmer

I have a form with a command button, when chosen goes to vba code. Within
this code I have a InputBox asking the user for a Employee name with a string
variable named 'SearchString'. I want to take that expression and 'Dlookup'
to a table, with the LIKE function, where the 'SearchString' does not have to
be the full text of the fieldname 'Employee'

I get a syntax error while writing it, can't seem to get the "LIKE *
function to work.

HELP?
 
It's not clear to me whether or not the user is typing the wildcard
character into the InputBox.

If they are, you need code along the lines of:

DLookup("[NameOfField]", "[NameOfTable]", "[EmployeeName] Like """ &
SearchString & """")

That's 3 double quotes in front of SearchString, and 4 double quotes
afterwards. (You need to use the double double quotes rather than a single
quote to allow for names with apostrophes in them, like O'Reilly)

If they aren't including the wildcard character, use

DLookup("[NameOfField]", "[NameOfTable]", "[EmployeeName] Like """ &
SearchString & """*")

or

DLookup("[NameOfField]", "[NameOfTable]", "[EmployeeName] Like "*"" &
SearchString & """*")
 
Thanks! - That did the trick!!!

Douglas J. Steele said:
It's not clear to me whether or not the user is typing the wildcard
character into the InputBox.

If they are, you need code along the lines of:

DLookup("[NameOfField]", "[NameOfTable]", "[EmployeeName] Like """ &
SearchString & """")

That's 3 double quotes in front of SearchString, and 4 double quotes
afterwards. (You need to use the double double quotes rather than a single
quote to allow for names with apostrophes in them, like O'Reilly)

If they aren't including the wildcard character, use

DLookup("[NameOfField]", "[NameOfTable]", "[EmployeeName] Like """ &
SearchString & """*")

or

DLookup("[NameOfField]", "[NameOfTable]", "[EmployeeName] Like "*"" &
SearchString & """*")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dbarmer said:
I have a form with a command button, when chosen goes to vba code. Within
this code I have a InputBox asking the user for a Employee name with a
string
variable named 'SearchString'. I want to take that expression and
'Dlookup'
to a table, with the LIKE function, where the 'SearchString' does not have
to
be the full text of the fieldname 'Employee'

I get a syntax error while writing it, can't seem to get the "LIKE *
function to work.

HELP?
Click to expand...
Click to expand...
 
Back
Top