Solved Use wildcard search with multiple words (1 Viewer)

[dwaynesworld]

I am attempting to run a query where I search for records with similar values and create boolean values for these entries. A sample of one of my fields is listed below:

I would like to create a field with the values from this field combined by using a wildcard. Here is an example of my expression:

Fed_Gov: IIF([source_data]![funding_source]="Federal Government", 1, 0)

I would like to include all values starting with "Federal Government" in this expression. I tried adding a wildcard character "*" with no results. Please help!!!!

 

[theDBguy]

Have you tried using the Like operator? I'm not sure I understand your question, actually. Sorry.

 

[plog]

First, each distinct value should go into its own field. You don't subdivide data in fields by delimiting them with a character (e.g Value1|Value2|VAlue3|etc..). Instead you need to build your tables to accomodate that data properly.

I would like to include all values starting with "Federal Government" in this expression.

Going off that, you need this expression:

Fed_Gov: IIF([source_data]![funding_source] Like "Federal Government*", 1, 0)


That works with what you explicitly stated. It does not work for anything that doesn't start with "Federal Government" which means it will return 0 for the record with the value "Internal Funding | Federal Government"

Again though, this should be avoided entirely by properly structuring your data.

 

[MajP]

Solved - Search all words in field against all words in another field

I kind of disagree in a db because you can probably build a much better search using sql Like search. This is based on a pretty thorough demonstration of a couple of interfaces using (Soundex, Simil, Levenshtein, and just find as you type) Like?? Like won't find similarities in strings with...

www.access-programmers.co.uk

See if something like this is what you mean.
I can type
Internal Funding | Other | No Funding
then from there do lots of combinations to match one of those, all of those, some of those.
You probably just need to modify one line of code to split it on "|" or whatever your delimiter is. I was splitting each word. Then I add the words to my listbox. Then I choose AND or OR comparisons. Then pick one, more, or all.

 

[dwaynesworld]

Have you tried using the Like operator? I'm not sure I understand your question, actually. Sorry.

What I would like is to have all variables starting with "Federal Government" included in the statement. For instance, "Federal Government", "Federal Government | Other", "Federal Government | Private Industry" etc would be included. However, "Internal Funding | Federal Government" would not be included as it will reside under the "Internal Funding" variable. I hope this helps.

 

[dwaynesworld]

First, each distinct value should go into its own field. You don't subdivide data in fields by delimiting them with a character (e.g Value1|Value2|VAlue3|etc..). Instead you need to build your tables to accomodate that data properly.



Going off that, you need this expression:

Fed_Gov: IIF([source_data]![funding_source] Like "Federal Government*", 1, 0)


That works with what you explicitly stated. It does not work for anything that doesn't start with "Federal Government" which means it will return 0 for the record with the value "Internal Funding | Federal Government"

Again though, this should be avoided entirely by properly structuring your data.

Thanks for your reply. Unfortunately I do not have access to the source tables to change these fields. I did try what you suggested and this was exactly what I was looking for! I appreciate your help with this. Have a great day!!

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.