MS Access search for text in field

Posted on 2012-09-15
Medium Priority
Last Modified: 2012-09-27

I have this SQL statement:
Set rst3 = dbs.OpenRecordset("SELECT Customer.[ListID], Customer.[Name], Customer.[FullName] " _
& "FROM Customer;")

Assuming some data of Customer.[Name]:
Smith, John/Sally
Brooks, Mel
Tel, Billy/Tony/Madeline

I'm wondering if it's possible and, if so, how I can query the Customer.[Name] using the following:
sFirstName_BillItemLine = Trim(Split(student_name, ",")(1))
sLastName_BillItemLine = Trim(Split(student_name, ",")(0))

Assume (since I'm looping) the current variables as:
sFirstName_BillItemLine  = "John"
sLastName_BillItemLine  = "Smith"

So, in other words, I have to search in the table Customer where the field Customer.[Name] contains "Smith" (i.e. before the comma) and then "John" (i.e. after the comma).

How do I tackle this problem?

I'm wondering if a simple LIKE will do or is there a better way?

Question by:Victor Kimura
LVL 61

Assisted Solution

mbizup earned 1000 total points
ID: 38401671
You can compare similar strings like this:

If instr(1,"" & sFirstName_BillItemLine, "John") > 0 then
         Msgbox "John was found in the First Name field"
End if

Open in new window

If instr(1,"" & sLastName_BillItemLine, "Smith") > 0 then
         Msgbox "Smith was found in the Last Name field"
End if

Open in new window

LVL 46

Accepted Solution

aikimark earned 1000 total points
ID: 38403992
If I think I understand your problem, you have individual (atomic) first and last name data and you want to use those to select records from your Customer table.
sFirstName_BillItemLine  = "John"
sLastName_BillItemLine  = "Smith"

Set rst3 = dbs.OpenRecordset("SELECT Customer.[ListID], Customer.[Name], Customer.[FullName] " & _
"FROM Customer Where Customer.[Name] Like """ & sLastName_BillItemLine & ", *" & sFirstName_BillItemLine & "*""  ;")

Open in new window


Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Implementing simple internal controls in the Microsoft Access application.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question