Using * to search with parameter query in forms

[Guest]

I am using a form (frmCrit) as a user interface for a parameter query. My
database contains students names in the first column, and the language that
they speak in the second, separated by ~~

For example

Joe English~~French
Mary French

I want to be able to search, from the form, which students speak French. I
know it should be something like

Like [Forms]![frmCrit]![language]

but where should I put the * so that the search will return both Joe and Mary?

 

[Dirk Goldgar]

I am using a form (frmCrit) as a user interface for a parameter
query. My database contains students names in the first column, and
the language that they speak in the second, separated by ~~

For example

Joe English~~French
Mary French

I want to be able to search, from the form, which students speak
French. I know it should be something like

Like [Forms]![frmCrit]![language]

but where should I put the * so that the search will return both Joe
and Mary?

Try

Like "*" & [Forms]![frmCrit]![language] & "*"

It's not perfect, as it stands, because it would return languages whose
names *contain* the string specified in [Forms]![frmCrit]![language].
There's a way around that, too -- using a SQL WHERE condition like this:

WHERE ("~" & Language & "~")
Like "*~" & [Forms]![frmCrit]![language] & "~*"

You realize that these sort of gyrations are forced by the fact that you
are storing multiple data in a single field? It would have been better
to store the languages in a separate, related table, with one row in the
table per student/language combination, as for example

Joe English
Joe French
Mary French

The query to find all the students who speak French would then start
with this table, and would be a lot more efficient.

 

[JulieD]

Hi Joanna

to answer your question set your query criteria up as follows

Like "*" & [Forms]![frmCrit]![language] & "*"

HOWEVER, i am concerned at the structure of your database if it is necessary
to append all the languages spoken in one field like you have explained.
You would be better off designing your database so that each entry is a
separate record - this will make counting / sorting & reporting easier for
you.

For example if you have a PEOPLE table and a LANGUAGES table you could have
a LANGUAGES_SPOKEN table where you match up the people with the languages
out of the list that they actually speak. Your database will then have the
following entries

Joe......English
Joe......French
Mary...French

this will make life easier for you in the long run.

cheers
JulieD