Search Query On 3 Criteria (1 Viewer)

Hello,

I have a feeling this may be easier than I expect however I am at a standstill.

I have a Query that is called from an unbound list box when data is typed into one or all three unbound txtBoxes "txtLastName" ,"txtFirstName", and "txtVIN" the query populates the listbox almost as it should..

The purpose is to identify duplicate entries based on three critera, last name, first name and VIN with the VIN bieng an execption meaning that if the VIN does not match I still want the matched first and last names to remain in view..

When I open the form where the list and text boxes are all records show in the listbox and as I begin to type the last name all records that do not match that critera are dropped, the same goes for the first name this works great. Once I get to the VIN however if there is no match I loose all three and the listbox is empty.

Is there a way to maintain matched names in the list view eventhough there is no match for the VIN?

Below is the code I am using in the Query Design, it is the same for all three fields Lastname, FirstName and VIN.


Like "*" & [Forms]![frm NewOrderVINVerify]![VinToFindFen] & "*"

Thanks!

Fen How

fenhow said:

Is there a way to maintain matched names in the list view eventhough there is no match for the VIN?

Click to expand...

Yes there is, and you are probably using the wrong joining operator.

fenhow said:

Below is the code I am using in the Query Design, it is the same for all three fields Lastname, FirstName and VIN.


Like "*" & [Forms]![frm NewOrderVINVerify]![VinToFindFen] & "*"

Click to expand...

For all 3 criteria, how are they joined? With the AND? or with the OR?

No match of the VIN with the names would result in the names disappearing (like you say) if you had this SQL... To avoid this, but still be able to match a first name with a last name, use this... If you want each criteria independent of each other, and don't care about one having to match with any other one, use this...

Adam,

Thank you so much. I think this option will best suit this need;

Like 1st criteria AND Like 2nd criteria AND (Like 3rd criteria OR 3rd criteria IS NULL);


The question I am left with is this,

For each of the three fields on my QryDesign

I have this code as indicated:

Like "*" & [Forms]![frm NewOrderVINVerify]![VinToFindFen] & "*"

Do I put your example in the quotes beside Like "*" and where you say Criteria what do I place in there? Here is what I vision please tell me if I am wrong"

Like "what goes here" AND Like "what goes here" AND (Like "what goes here OR "what goes here" IS NULL)& [Forms]![frm NewOrderVINVerify]![VinToFindFen] & "*"

Thanks.

Fen

Here's a sample database for multiple criteria:

http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

Screen Shots Attached

Thanks Bob, I can search for multiple criteria however I want it to stick on the first two if the third does not match I loose all three and the listbox goes blank.

I have attached a snapshot of my form and query design..

You will note that both records have different VIN numbers however if once i complete the VIN entry on the form and there is no match I want the names to remain in view...

Thanks.

Fen

The database I referenced will search by one, two, or three items and it doesn't matter which. If you leave any blank it will ignore those.

I am there, on this particular one there will always be data entered. The goal is to see if there is already an existing match across all three or any one of the three fields. Say this name matches an existing record however the VIN does not match, there for once I type in the VIN I can still see the name or The VIN matches but to a different name... then I could see the matching VIN with a different name..

In other words, If there are any matches whether all three fields or any one of the fields I want to see those records in the listbox...

At that point we decide what to do...

Thanks Bob..

Fen

fenhow said:

The question I am left with is this,

For each of the three fields on my QryDesign

I have this code as indicated:

Like "*" & [Forms]![frm NewOrderVINVerify]![VinToFindFen] & "*"

Do I put your example in the quotes beside Like "*" and where you say Criteria what do I place in there? Here is what I vision please tell me if I am wrong"

Like "what goes here" AND Like "what goes here" AND (Like "what goes here OR "what goes here" IS NULL)& [Forms]![frm NewOrderVINVerify]![VinToFindFen] & "*"

Click to expand...

Close!!

Fen, the SQL that should work for you is this...([c#] = relevant field being referenced by the criteria section's control) Do you understand now?

Another thing to add as well, for reference (assume you are searching for the cell value "Hello World")...

The asterisk symbol (*), when separated by the ampersand (&) and a string, returns all of the value on the asterisk side of the string being evaluated (the string in your criteria section).

Example
1) Like "*" & "orld" will return Hello World

The asterisk symbol (*), when connected to a string, returns the entire string value from which the asterisk is attached, but not necessarily the entire cell value.

Example
2) Like "*orld" will return World, but not Hello World

If you are doing this in VBA code then you will need to start the SQL string with double quotes and the very end will be in double quotes, and anything in between that you would normally use double quotes on (for example "*") will use single quotes (for example '*').

Brilliant! Thanks to both of you.

It is just what I needed.

Fen How