Query with Criteria is not returning accurate or relevant results

Question

I have a data set of 9 records in a table that is test data. I have a sample of the data below. In the table below, the first line is the header.

+-------------+-------------+----------+---------------+---------------+
| BehrInvoice |  TboInvoice |  TboRloc |     TboDoc    |     TboPax    |
+-------------+-------------+----------+---------------+---------------+
|        4312 |  1449S      |  WIUBLF  |  -0772089627  |  ASARCH/CHAD  |
|        4313 |  1457S      |  TAQXKU  |  XD7366998723 |  CARREON JR/L |
|        4314 |  1457S      |  TAXXKU  |  -7366998723  |  CARREON JR/L |
|        4317 |  1461S      |  TOXSEH  |  XD7366998726 |  ALVA/MICHAEL |
|        4318 |  1460S      |  TOXSEH  |  -7366998726  |  ALVA/MICHAEL |
|        4320 |  1458S      |  ULHHZO  |  -7366998724  |  GREENFIELD/M |
+-------------+-------------+----------+---------------+---------------+

What I would like to do is be able to search each of the columns, together.

I would expect that if I typed in alva I would see the Alva/Michael records pop up, at least first.

Or if I typed in the TboInvoice search box 1458, and alva in the TboPax search box, I would see all three of those records.

I was trying to use this:

SELECT *
FROM Main
WHERE ((Main.TboInvo) LIKE [Forms]![SearchForm]![TboInvoice] & "*")
OR ((Main.TboPax) LIKE [Forms]![SearchForm]![PaxName] & "*")

But the result set came back with everything. I isolated to the TboInvoice, and tried this:

WHERE ((Main.TboInvo) = [Forms]![SearchForm]![TboInvoice] & "[S]")

and it brought back nothing.

I suppose I should focus on just TboInvoice here, and get that running properly.

So, in summary, the question is:

How do I query the TboInvoice column here and get more accurate results?

=== EDIT 190906

So when I put in:

SELECT * FROM Main​ 
WHERE​ Main.TboPax LIKE "alva*"​;

it worked perfectly. when I put in:

SELECT *
FROM Main
WHERE (((Main.TboPax) Like [Forms]![SearchForm]![PaxName]));

AND the value on the form of [PaxName]== "alva", i get nothing. Maybe I am referencing the form incorrectly?

Answer 1

The reason that your first query returns everything is because if either of the textboxes TboInvoice or PaxName are empty, then [Forms]![SearchForm]![TboInvoice] & "*" will yield "*", thus matching all records.

With this in mind, you will need to include a test within your selection criteria to account for when the form control is null, perhaps something along the lines of:

select * from main
where
(
    [Forms]![SearchForm]![TboInvoice] is not null and 
    main.tboinvo like [Forms]![SearchForm]![TboInvoice] & "*"
) or
(
    [Forms]![SearchForm]![PaxName] is not null and 
    main.tbopax like [Forms]![SearchForm]![PaxName] & "*"
)

Depending on the desired behaviour, you may want all records to be returned if both form controls are null, which would require a third condition such as:

select * from main
where
(
    [Forms]![SearchForm]![TboInvoice] is not null and 
    main.tboinvo like [Forms]![SearchForm]![TboInvoice] & "*"
) or
(
    [Forms]![SearchForm]![PaxName] is not null and 
    main.tbopax like [Forms]![SearchForm]![PaxName] & "*"
) or
( 
    [Forms]![SearchForm]![TboInvoice] is null and
    [Forms]![SearchForm]![PaxName] is null
)

This could alternatively be written:

select * from main
where
(
    [Forms]![SearchForm]![TboInvoice] is null or
    main.tboinvo like [Forms]![SearchForm]![TboInvoice] & "*"
) and 
(
    [Forms]![SearchForm]![PaxName] is null or 
    main.tbopax like [Forms]![SearchForm]![PaxName] & "*"
)

And noting that Null & "*" yields "*", this could become:

select * from main
where
    main.tboinvo like [Forms]![SearchForm]![TboInvoice] & "*"
    and 
    main.tbopax like [Forms]![SearchForm]![PaxName] & "*"

Answer 2

I suspect you simply want and:

SELECT *
FROM Main
WHERE (Main.TboInvo LIKE [Forms]![SearchForm]![TboInvoice] & "*") AND
      (Main.TboPa LIKE [Forms]![SearchForm]![PaxName] & "*")

If you use OR and leave either text box empty, then all rows are included by the condition for that text box.