multiple query parameters on a single field

[Joseph Atie]

Ive got a form with a bunch of unbound combo boxes (12 to be exact) each
queries the same field in a table allowing the user to select upto 12
different search critera.

On completion this form will pass its data to another form that uses a list
box to display records where the decsription field matches ALL of the search
critera.

so the issue im having is how can i write a query that basicly looks
something like this

select * from table

where description = this & that & something & something else

ive tried using the expression builder to literally build the required
critera string (i.e [form].[field] and [form].[field1], but access wont allow
me to use more than one of the unbound fields, otherwise it just returns no
data

 

[Ken Snell \(MVP\)]

May I suggest that you use a multiselect listbox for the user's filter
selections, and then build the WHERE clause based on those multiple
selections? See this sample database for one way this can be done:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

Using a listbox eliminates the need to handle null values in various combo
boxes.

 

[Joseph Atie]

id gladly have a look at your example to see if it fits my needs, but your
domain has lapsed.

do you have another location i can see it.

May I suggest that you use a multiselect listbox for the user's filter
selections, and then build the WHERE clause based on those multiple
selections? See this sample database for one way this can be done:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

Using a listbox eliminates the need to handle null values in various combo
boxes.
--

Ken Snell
<MS ACCESS MVP>

Ive got a form with a bunch of unbound combo boxes (12 to be exact) each
queries the same field in a table allowing the user to select upto 12
different search critera.

On completion this form will pass its data to another form that uses a
list
box to display records where the decsription field matches ALL of the
search
critera.

so the issue im having is how can i write a query that basicly looks
something like this

select * from table

where description = this & that & something & something else

ive tried using the expression builder to literally build the required
critera string (i.e [form].[field] and [form].[field1], but access wont
allow
me to use more than one of the unbound fields, otherwise it just returns
no
data

 

[Joseph Atie]

sorry to double post but i had another thought about using the list box, the
reason i didnt do that in the first place is because there is 300 odd entries
to choose from, as you can imagine with some of our less talented users
scrolling and selecting multiples is at best tricky for them.

May I suggest that you use a multiselect listbox for the user's filter
selections, and then build the WHERE clause based on those multiple
selections? See this sample database for one way this can be done:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

Using a listbox eliminates the need to handle null values in various combo
boxes.
--

Ken Snell
<MS ACCESS MVP>

Ive got a form with a bunch of unbound combo boxes (12 to be exact) each
queries the same field in a table allowing the user to select upto 12
different search critera.

On completion this form will pass its data to another form that uses a
list
box to display records where the decsription field matches ALL of the
search
critera.

so the issue im having is how can i write a query that basicly looks
something like this

select * from table

where description = this & that & something & something else

ive tried using the expression builder to literally build the required
critera string (i.e [form].[field] and [form].[field1], but access wont
allow
me to use more than one of the unbound fields, otherwise it just returns
no
data

 

[KARL DEWEY]

to display records where the decsription field matches ALL of the search
critera.
Unless I am missreading this the criteria for the description field must
have all the word, numbers, and phrases that the 12 combo boxes picks.

Just use a long WHERE clause like this ---
Like "*" & [Forms]![YourFormName]![Combo1] & "*" AND Like "*" &
[Forms]![YourFormName]![Combo2] & "*" AND Like "*" &
[Forms]![YourFormName]![Combo3] & "*" AND .... AND Like "*" &
[Forms]![YourFormName]![Combo12] & "*"

 

[Joseph Atie]

the structure is basicly a a table full of training records each record is
set against an employee id no. each record in the table is 1 training that
has been completed.

the query is basicly search for ppl with a certain skillset, so basicly all
i want is the emp ids that meet the selected critera (upto 12 possible).

as i said if i try the query for 1 of the combo boxes its fine and gives me
what im looking for when i try and add a second search parameter i get 0
records. the problem im having is how do i correctly write the where
condition so i can use all 12.

this is the code i tried for when i added the second parameter

SELECT Inddata.Description, Empdata.[Employee No], Empdata.[Employee Name],
Empdata.[Job Title], Empdata.selected
FROM Empdata INNER JOIN Inddata ON Empdata.[Employee No] = Inddata.[Employee
No]
WHERE (((Inddata.Description)=[Forms]![ind_selector]![Indcode5] And
(Inddata.Description)=[Forms]![ind_selector]![Indcode1]));

please excuse the poor sql, it was written by the query builder (im so lazy
some times)

critera.
Unless I am missreading this the criteria for the description field must
have all the word, numbers, and phrases that the 12 combo boxes picks.

Just use a long WHERE clause like this ---
Like "*" & [Forms]![YourFormName]![Combo1] & "*" AND Like "*" &
[Forms]![YourFormName]![Combo2] & "*" AND Like "*" &
[Forms]![YourFormName]![Combo3] & "*" AND .... AND Like "*" &
[Forms]![YourFormName]![Combo12] & "*"
--
KARL DEWEY
Build a little - Test a little

Ive got a form with a bunch of unbound combo boxes (12 to be exact) each
queries the same field in a table allowing the user to select upto 12
different search critera.

On completion this form will pass its data to another form that uses a list
box to display records where the decsription field matches ALL of the search
critera.

so the issue im having is how can i write a query that basicly looks
something like this

select * from table

where description = this & that & something & something else

ive tried using the expression builder to literally build the required
critera string (i.e [form].[field] and [form].[field1], but access wont allow
me to use more than one of the unbound fields, otherwise it just returns no
data

 

[KARL DEWEY]

i want is the emp ids that meet the selected critera (upto 12 possible).
Your words are confusing to me. How would you have 12 EMP IDs in a
description field?

WHERE (((Inddata.Description)=[Forms]![ind_selector]![Indcode5] And

(Inddata.Description)=[Forms]![ind_selector]![Indcode1]));
Maybe change to use an OR like this ---
WHERE (((Inddata.Description)=[Forms]![ind_selector]![Indcode5] OR
(Inddata.Description)=[Forms]![ind_selector]![Indcode1]));

--
KARL DEWEY
Build a little - Test a little

the structure is basicly a a table full of training records each record is
set against an employee id no. each record in the table is 1 training that
has been completed.

the query is basicly search for ppl with a certain skillset, so basicly all
i want is the emp ids that meet the selected critera (upto 12 possible).

as i said if i try the query for 1 of the combo boxes its fine and gives me
what im looking for when i try and add a second search parameter i get 0
records. the problem im having is how do i correctly write the where
condition so i can use all 12.

this is the code i tried for when i added the second parameter

SELECT Inddata.Description, Empdata.[Employee No], Empdata.[Employee Name],
Empdata.[Job Title], Empdata.selected
FROM Empdata INNER JOIN Inddata ON Empdata.[Employee No] = Inddata.[Employee
No]
WHERE (((Inddata.Description)=[Forms]![ind_selector]![Indcode5] And
(Inddata.Description)=[Forms]![ind_selector]![Indcode1]));

please excuse the poor sql, it was written by the query builder (im so lazy
some times)

to display records where the decsription field matches ALL of the search

critera.
Unless I am missreading this the criteria for the description field must
have all the word, numbers, and phrases that the 12 combo boxes picks.

Just use a long WHERE clause like this ---
Like "*" & [Forms]![YourFormName]![Combo1] & "*" AND Like "*" &
[Forms]![YourFormName]![Combo2] & "*" AND Like "*" &
[Forms]![YourFormName]![Combo3] & "*" AND .... AND Like "*" &
[Forms]![YourFormName]![Combo12] & "*"
--
KARL DEWEY
Build a little - Test a little

Ive got a form with a bunch of unbound combo boxes (12 to be exact) each
queries the same field in a table allowing the user to select upto 12
different search critera.

On completion this form will pass its data to another form that uses a list
box to display records where the decsription field matches ALL of the search
critera.

so the issue im having is how can i write a query that basicly looks
something like this

select * from table

where description = this & that & something & something else

ive tried using the expression builder to literally build the required
critera string (i.e [form].[field] and [form].[field1], but access wont allow
me to use more than one of the unbound fields, otherwise it just returns no
data

 

[Joseph Atie]

2 separate fields in the table, what im saying is i want the employee id of
the employees that have a match with the desired description critera

Your words are confusing to me. How would you have 12 EMP IDs in a
description field?

WHERE (((Inddata.Description)=[Forms]![ind_selector]![Indcode5] And

(Inddata.Description)=[Forms]![ind_selector]![Indcode1]));
Maybe change to use an OR like this ---
WHERE (((Inddata.Description)=[Forms]![ind_selector]![Indcode5] OR
(Inddata.Description)=[Forms]![ind_selector]![Indcode1]));

--
KARL DEWEY
Build a little - Test a little

the structure is basicly a a table full of training records each record is
set against an employee id no. each record in the table is 1 training that
has been completed.

the query is basicly search for ppl with a certain skillset, so basicly all
i want is the emp ids that meet the selected critera (upto 12 possible).

as i said if i try the query for 1 of the combo boxes its fine and gives me
what im looking for when i try and add a second search parameter i get 0
records. the problem im having is how do i correctly write the where
condition so i can use all 12.

this is the code i tried for when i added the second parameter

SELECT Inddata.Description, Empdata.[Employee No], Empdata.[Employee Name],
Empdata.[Job Title], Empdata.selected
FROM Empdata INNER JOIN Inddata ON Empdata.[Employee No] = Inddata.[Employee
No]
WHERE (((Inddata.Description)=[Forms]![ind_selector]![Indcode5] And
(Inddata.Description)=[Forms]![ind_selector]![Indcode1]));

please excuse the poor sql, it was written by the query builder (im so lazy
some times)

to display records where the decsription field matches ALL of the search
critera.
Unless I am missreading this the criteria for the description field must
have all the word, numbers, and phrases that the 12 combo boxes picks.

Just use a long WHERE clause like this ---
Like "*" & [Forms]![YourFormName]![Combo1] & "*" AND Like "*" &
[Forms]![YourFormName]![Combo2] & "*" AND Like "*" &
[Forms]![YourFormName]![Combo3] & "*" AND .... AND Like "*" &
[Forms]![YourFormName]![Combo12] & "*"
--
KARL DEWEY
Build a little - Test a little


:

Ive got a form with a bunch of unbound combo boxes (12 to be exact) each
queries the same field in a table allowing the user to select upto 12
different search critera.

On completion this form will pass its data to another form that uses a list
box to display records where the decsription field matches ALL of the search
critera.

so the issue im having is how can i write a query that basicly looks
something like this

select * from table

where description = this & that & something & something else

ive tried using the expression builder to literally build the required
critera string (i.e [form].[field] and [form].[field1], but access wont allow
me to use more than one of the unbound fields, otherwise it just returns no
data

 

[Ken Snell \(MVP\)]

Ack! So it has. My apologies. I'll need to move the info to a new location.
If you'd like a zip file of the sample database, send me an email message at
my reply email address (but remove the words THIS IS NOT REAL from the email
address first), and I'll send you a copy of the file.
--

Ken Snell
<MS ACCESS MVP>

id gladly have a look at your example to see if it fits my needs, but your
domain has lapsed.

do you have another location i can see it.

May I suggest that you use a multiselect listbox for the user's filter
selections, and then build the WHERE clause based on those multiple
selections? See this sample database for one way this can be done:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

Using a listbox eliminates the need to handle null values in various
combo
boxes.
--

Ken Snell
<MS ACCESS MVP>

Ive got a form with a bunch of unbound combo boxes (12 to be exact)
each
queries the same field in a table allowing the user to select upto 12
different search critera.

On completion this form will pass its data to another form that uses a
list
box to display records where the decsription field matches ALL of the
search
critera.

so the issue im having is how can i write a query that basicly looks
something like this

select * from table

where description = this & that & something & something else

ive tried using the expression builder to literally build the required
critera string (i.e [form].[field] and [form].[field1], but access wont
allow
me to use more than one of the unbound fields, otherwise it just
returns
no
data

 

[Joseph Atie]

sorry, you've lost me i cant find any email address, my email is
(e-mail address removed)

im sure you can see what you need to remove

Ack! So it has. My apologies. I'll need to move the info to a new location.
If you'd like a zip file of the sample database, send me an email message at
my reply email address (but remove the words THIS IS NOT REAL from the email
address first), and I'll send you a copy of the file.
--

Ken Snell
<MS ACCESS MVP>

id gladly have a look at your example to see if it fits my needs, but your
domain has lapsed.

do you have another location i can see it.

May I suggest that you use a multiselect listbox for the user's filter
selections, and then build the WHERE clause based on those multiple
selections? See this sample database for one way this can be done:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

Using a listbox eliminates the need to handle null values in various
combo
boxes.
--

Ken Snell
<MS ACCESS MVP>



Ive got a form with a bunch of unbound combo boxes (12 to be exact)
each
queries the same field in a table allowing the user to select upto 12
different search critera.

On completion this form will pass its data to another form that uses a
list
box to display records where the decsription field matches ALL of the
search
critera.

so the issue im having is how can i write a query that basicly looks
something like this

select * from table

where description = this & that & something & something else

ive tried using the expression builder to literally build the required
critera string (i.e [form].[field] and [form].[field1], but access wont
allow
me to use more than one of the unbound fields, otherwise it just
returns
no
data

 

[KARL DEWEY]

Do you mean that you have employee IDs stored in the description field?
--
KARL DEWEY
Build a little - Test a little

2 separate fields in the table, what im saying is i want the employee id of
the employees that have a match with the desired description critera

i want is the emp ids that meet the selected critera (upto 12 possible).

Your words are confusing to me. How would you have 12 EMP IDs in a
description field?

WHERE (((Inddata.Description)=[Forms]![ind_selector]![Indcode5] And

(Inddata.Description)=[Forms]![ind_selector]![Indcode1]));
Maybe change to use an OR like this ---
WHERE (((Inddata.Description)=[Forms]![ind_selector]![Indcode5] OR
(Inddata.Description)=[Forms]![ind_selector]![Indcode1]));

--
KARL DEWEY
Build a little - Test a little

the structure is basicly a a table full of training records each record is
set against an employee id no. each record in the table is 1 training that
has been completed.

the query is basicly search for ppl with a certain skillset, so basicly all
i want is the emp ids that meet the selected critera (upto 12 possible).

as i said if i try the query for 1 of the combo boxes its fine and gives me
what im looking for when i try and add a second search parameter i get 0
records. the problem im having is how do i correctly write the where
condition so i can use all 12.

this is the code i tried for when i added the second parameter

SELECT Inddata.Description, Empdata.[Employee No], Empdata.[Employee Name],
Empdata.[Job Title], Empdata.selected
FROM Empdata INNER JOIN Inddata ON Empdata.[Employee No] = Inddata.[Employee
No]
WHERE (((Inddata.Description)=[Forms]![ind_selector]![Indcode5] And
(Inddata.Description)=[Forms]![ind_selector]![Indcode1]));

please excuse the poor sql, it was written by the query builder (im so lazy
some times)


:

to display records where the decsription field matches ALL of the search
critera.
Unless I am missreading this the criteria for the description field must
have all the word, numbers, and phrases that the 12 combo boxes picks.

Just use a long WHERE clause like this ---
Like "*" & [Forms]![YourFormName]![Combo1] & "*" AND Like "*" &
[Forms]![YourFormName]![Combo2] & "*" AND Like "*" &
[Forms]![YourFormName]![Combo3] & "*" AND .... AND Like "*" &
[Forms]![YourFormName]![Combo12] & "*"
--
KARL DEWEY
Build a little - Test a little


:

Ive got a form with a bunch of unbound combo boxes (12 to be exact) each
queries the same field in a table allowing the user to select upto 12
different search critera.

On completion this form will pass its data to another form that uses a list
box to display records where the decsription field matches ALL of the search
critera.

so the issue im having is how can i write a query that basicly looks
something like this

select * from table

where description = this & that & something & something else

ive tried using the expression builder to literally build the required
critera string (i.e [form].[field] and [form].[field1], but access wont allow
me to use more than one of the unbound fields, otherwise it just returns no
data

 

[Ken Snell \(MVP\)]

I've moved the sample databases to a new website. Try this:
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

--

Ken Snell
<MS ACCESS MVP>

sorry, you've lost me i cant find any email address, my email is
(e-mail address removed)

im sure you can see what you need to remove

Ack! So it has. My apologies. I'll need to move the info to a new
location.
If you'd like a zip file of the sample database, send me an email message
at
my reply email address (but remove the words THIS IS NOT REAL from the
email
address first), and I'll send you a copy of the file.
--

Ken Snell
<MS ACCESS MVP>

id gladly have a look at your example to see if it fits my needs, but
your
domain has lapsed.

do you have another location i can see it.

:

May I suggest that you use a multiselect listbox for the user's filter
selections, and then build the WHERE clause based on those multiple
selections? See this sample database for one way this can be done:
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

Using a listbox eliminates the need to handle null values in various
combo
boxes.
--

Ken Snell
<MS ACCESS MVP>



Ive got a form with a bunch of unbound combo boxes (12 to be exact)
each
queries the same field in a table allowing the user to select upto
12
different search critera.

On completion this form will pass its data to another form that uses
a
list
box to display records where the decsription field matches ALL of
the
search
critera.

so the issue im having is how can i write a query that basicly looks
something like this

select * from table

where description = this & that & something & something else

ive tried using the expression builder to literally build the
required
critera string (i.e [form].[field] and [form].[field1], but access
wont
allow
me to use more than one of the unbound fields, otherwise it just
returns
no
data