Dlookup with 'like' operator and wildcards

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
D

Douglas J. Steele

First of all, you need to use Like rather than = for wildcards to work.
Second, I believe that DLookup still uses DAO, so that you need * as the
wildcard character, not %.

ID = DLookup("[man_id]", "[tblmanufact]", _
"[man_name] Like '*" & Me.txtFindPart.Value & "*'")

(exagerated to show quotes, "[man_name] Like ' * " & Me.txtFindPart.Value &
" * ' ")

You could also use Chr$(34) (34 is the Ascii value of "):

ID = DLookup("[man_id]", "[tblmanufact]", _
"[man_name] Like " & Chr$(34) & "*" & Me.txtFindPart.Value & "*" &
Chr$(34))
 
Hi,
I am trying to use the dlookup function in an event to
return the value of an id column where part of the value
in another column(same row) matches the value of a textbox.

The database is sequel with access front end.

The problem I seem to be having is that access basic feels
bad when I use %'s in my code. Depending on where i place
the delimiters I get several different errors. It also
seems that single quotes do not work at all where %'s are
on the same line.

this works:
ID = DLookup("[man_id]", "tblmanufact", "[man_name] = '" _
& Me!txtFindPart & "'")

this doesn't:
ID = DLookup("[man_id]", "[tblmanufact]", "[man_name] _
= ""%" & Me.txtFindPart.Value & "%""")

(Returns runtime error: "Invalid column name '%cdc%'."
where cdc is the value of me!txtfindpart

Another example(not dlookup, but i had %,' trouble):
works:
me.fiter = "([part_description] Like ""%" & _
Me!txtFindPart & "%"")"

works not:
me.filter = "([part_description] Like '"%" & _
Me!txtFindPart & "%"')"


I have been working with access for about a week and i'm
fairly certain i don't know what i'm doing. Any help will
be greatly appreciated.

Michael
 
Doug, sorry about the "=", a mistype.
This is what finally worked:

DLookup("[man_id]", "[tblmanufact]", "[man_name] like '%"
& Me.txtFindPart.Value & "%'")

It is identical to your first suggestion except with %
instead of *

with the *'s the error is a 'type mismatch'

and the second suggestion results in the error "Invalid
column name '*text*'. Where text is whatever is in
me.txtFindPart.Value

I really appreciate your help but i still don't understand
the 'why' here, especially when i compare this working
dlookup statement the the working filter statement below.
What is the diffence between single and double quotes? and
how do you know when to use what? also i thought the
percent or asterisk wildcard depended on the database.

Thanks again,
Michael
-----Original Message-----
First of all, you need to use Like rather than = for wildcards to work.
Second, I believe that DLookup still uses DAO, so that you need * as the
wildcard character, not %.

ID = DLookup("[man_id]", "[tblmanufact]", _
"[man_name] Like '*" & Me.txtFindPart.Value & "*'")

(exagerated to show quotes, "[man_name] Like ' * " & Me.txtFindPart.Value &
" * ' ")

You could also use Chr$(34) (34 is the Ascii value of "):

ID = DLookup("[man_id]", "[tblmanufact]", _
"[man_name] Like " & Chr$(34) & "*" & Me.txtFindPart.Value & "*" &
Chr$(34))

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Michael Streeks said:
Hi,
I am trying to use the dlookup function in an event to
return the value of an id column where part of the value
in another column(same row) matches the value of a textbox.

The database is sequel with access front end.

The problem I seem to be having is that access basic feels
bad when I use %'s in my code. Depending on where i place
the delimiters I get several different errors. It also
seems that single quotes do not work at all where %'s are
on the same line.

this works:
ID = DLookup("[man_id]", "tblmanufact", "[man_name] = '" _
& Me!txtFindPart & "'")

this doesn't:
ID = DLookup("[man_id]", "[tblmanufact]", "[man_name] _
= ""%" & Me.txtFindPart.Value & "%""")

(Returns runtime error: "Invalid column name '%cdc%'."
where cdc is the value of me!txtfindpart

Another example(not dlookup, but i had %,' trouble):
works:
me.fiter = "([part_description] Like ""%" & _
Me!txtFindPart & "%"")"

works not:
me.filter = "([part_description] Like '"%" & _
Me!txtFindPart & "%"')"


I have been working with access for about a week and i'm
fairly certain i don't know what i'm doing. Any help will
be greatly appreciated.

Michael
Click to expand...


.
Click to expand...
 
At least part of your problem was caused by your misuse of the continuation
character _.

Your example was

ID = DLookup("[man_id]", "[tblmanufact]", "[man_name] _
= ""%" & Me.txtFindPart.Value & "%""")

You can't put a continuation in the midst of a string like that: you have to
end the string first, and concatenate on the next line:

ID = DLookup("[man_id]", "[tblmanufact]", "[man_name] " _
& " = ""%" & Me.txtFindPart.Value & "%""")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Michael Streeks said:
Doug, sorry about the "=", a mistype.
This is what finally worked:

DLookup("[man_id]", "[tblmanufact]", "[man_name] like '%"
& Me.txtFindPart.Value & "%'")

It is identical to your first suggestion except with %
instead of *

with the *'s the error is a 'type mismatch'

and the second suggestion results in the error "Invalid
column name '*text*'. Where text is whatever is in
me.txtFindPart.Value

I really appreciate your help but i still don't understand
the 'why' here, especially when i compare this working
dlookup statement the the working filter statement below.
What is the diffence between single and double quotes? and
how do you know when to use what? also i thought the
percent or asterisk wildcard depended on the database.

Thanks again,
Michael
-----Original Message-----
First of all, you need to use Like rather than = for wildcards to work.
Second, I believe that DLookup still uses DAO, so that you need * as the
wildcard character, not %.

ID = DLookup("[man_id]", "[tblmanufact]", _
"[man_name] Like '*" & Me.txtFindPart.Value & "*'")

(exagerated to show quotes, "[man_name] Like ' * " & Me.txtFindPart.Value &
" * ' ")

You could also use Chr$(34) (34 is the Ascii value of "):

ID = DLookup("[man_id]", "[tblmanufact]", _
"[man_name] Like " & Chr$(34) & "*" & Me.txtFindPart.Value & "*" &
Chr$(34))

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Michael Streeks said:
Hi,
I am trying to use the dlookup function in an event to
return the value of an id column where part of the value
in another column(same row) matches the value of a textbox.

The database is sequel with access front end.

The problem I seem to be having is that access basic feels
bad when I use %'s in my code. Depending on where i place
the delimiters I get several different errors. It also
seems that single quotes do not work at all where %'s are
on the same line.

this works:
ID = DLookup("[man_id]", "tblmanufact", "[man_name] = '" _
& Me!txtFindPart & "'")

this doesn't:
ID = DLookup("[man_id]", "[tblmanufact]", "[man_name] _
= ""%" & Me.txtFindPart.Value & "%""")

(Returns runtime error: "Invalid column name '%cdc%'."
where cdc is the value of me!txtfindpart

Another example(not dlookup, but i had %,' trouble):
works:
me.fiter = "([part_description] Like ""%" & _
Me!txtFindPart & "%"")"

works not:
me.filter = "([part_description] Like '"%" & _
Me!txtFindPart & "%"')"


I have been working with access for about a week and i'm
fairly certain i don't know what i'm doing. Any help will
be greatly appreciated.

Michael
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top