Filter combo box to get "kansas" but not "arkansas"

[johandenver]

Hello!

I have a combo box containing names of certain US states.
When selecting a state in this box it filters results in another combo box using the selection as a criteria in a query.
My problem is that when selecting "kansas" I also get all results containing "arkansas".

I've tried using the following as criteria in my query:
Like "*" & " " & [Forms]![formname]![State_Combo] & "*"

The table has this format:
1st Kansas Infantry Regiment
1st Arkansas Infantry Regiment
1st Maine Infantry Regiment
etc. etc.

I'm supposing that I get all "arkansas" as well as "Kansas" is because I use the "*" wildcard but I really don't know how else to do it.

 

[NigelShaw]

Hi

You are also using the 'Like' which is why you get the return result. Kansas the word is in Arkansas so the are similar or 'like' each other.

Why are you using Like? If you used a standard 'Select' you would your results

Cheers

Nigel

 

[johandenver]

I've tried removing Like earlier but it made no difference.
Could you give me an example for how the line should look?

 

[NigelShaw]

Hi,

I'll do a sample later but in the mean time, the second combo needs a wry based on the first-

"Select * From MyQuery Where " & Forms!FormName!State_Combo & " = MyOtherCombo;"

Or

Create a query and add

Forms!MyForm!State_Combo

In the criteria. Then add a combo using the wizard and use the option to look up a value in a table or query. Then just point to the query made. That would work just fine.


Cheers

Nigel

 

[gemma-the-husky]

you are searching a string for the characters "kansas", and that's what you get - it just happens that kansas is a substring of arkansas.

so - how do you avoid this - one way would be to have a "state" field in your table - so you can select a state, and immediately find all regiments/battalions attached to that state. This would also be quicker than searching a text field.