MS Access search for text in field

Posted on 2012-09-15
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

    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 44

    Accepted Solution

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now