how to use like and wildcard

[ges via AccessMonster.com]

I have table with customer data as follow:
A-1 Express Delivery
20/7 McKinney
1 2 3 SouthWest ======>(1 space 2 space 3 space) SouthWest
Adriana's Insurance

I have a text box that user will type in the value for search. How do I use
the "LIKE" syntac in the WHERE clause?

Currently my string look like below:

WHERE (((tblAccounts.a_FileType)='Arden) AND "
((tblAccounts.a_Account)=" & """" & txtSearchAC & """" & ") "
OR ((tblAccounts.a_BillName) =" & """" & txtSearchAC & """" & ")
"
OR ((tblAccounts.a_BillName) LIKE " & Chr(34) & txtSearchAC & "%
" & Chr(34) & ")) "
ORDER BY tblAccounts.a_Account DESC , tblAccounts.a_BillName
DESC;"

When I try to search :
A-1 Express Delivery
20/7 McKinney
1 2 3 SouthWest ======>(1 space 2 space 3 space) SouthWest
Adriana's Insurance

It doesn't give me error, the record just doesn't show up (access doesn't
find it).

Anyone can help me? I greatly appreciate any input, THANK YOU!

Ges

 

[Jeanette Cunningham]

Hi ges,
there is a great little sample database of how to search for customers using
like and wildcard at this webiste
http://allenbrowne.com/ser-62.html

Make a copy of the sample, change it to the fields you want to search on.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

It doesn't give me error, the record just doesn't show up (access doesn't
find it).

Use * rather than % as the wildcard. Oracle and SQL/Server use % as a "match
anything" wildcard; Access uses an asterisk instead.

 

[ges via AccessMonster.com]

Thanks Jeanette & John,

I'm actually using access as front end with SQL server 2005 back end. I have
no problem find record such as : Aaron Mortgage Group, Inc but I have
problem finding record such as follow:
A.M.A. Plastics (with " . "dot)
A-1 Tickets, Inc (with "-" )

I have tried using Like "* & textbox & "*" still don't work
No error, the record just don't show up

Can you help?

Thanks,
Ges

 

[John W. Vinson]

I have tried using Like "* & textbox & "*" still don't work
No error, the record just don't show up

Unbalanced quotes! Shouldn't that be

Like "*" & textbox & "*"

 

[ges via AccessMonster.com]

John,
Thanks for your response. I just type it wrong in this post, I did have
balance quotes in my program. I found out there is something wrong with my
DB, some records missing, so the problem is not the "like" statement,
something else.

Thanks anyway for all your help.

B.rgds,
Ges