excluding certain values from a query

[Guest]

I am trying to exclude text data that originates in a lookup field in the
table, in a query (in other words there are about 6 things in the lookup and
I want to design a query that will only contain 3 or 4 of the values in the
lookup). I have tried:

"x"; "y"; "z" in the criteria line of the query design as well as
"x"&"y"&"z" and x&y&z

Nothing is working. I can include or exclude one value alone[such as: <>x or
"x"], but it refuses to accept more than one. I know there is a way to
include or exclude more than one criteria in a query. What am I doing wrong?

Thank you.

Debbie

 

[Michel Walsh]

It is not clear if you look for a string having 2 or 3 delimiters:

"12, 34, 56, 78, 90, 11" has 6 values, and 5 comas

Or if you are looking to something like: find all workers that have 3 or 4
of the required skills (given).


In the first case,

len(yourString) - len(replace(YourString, "," ,""))

returns the number of coma present in your string, so,

(len(yourString) - len(replace(YourString, "," ,""))) BETWEEN 2 AND 3

should do.



In the second case, I assume each given skill is in a different records (NOT
a string list). Something like


SELECT w.workerID
FROM workersSkills AS w INNER JOIN wantedSkills AS s
ON w.skill = s.skill
GROUP BY w.workerID
HAVING COUNT(*) BETWEEN 2 AND 3


would do. The table WorkersSkills is like

WorkerID, Skill ' field names
John, FirstAid
John, Welding
John, Security
....
Mary, FirstAid
.....


and WITHOUT duplicated rows.

while the table wantedSKills is like:

skill ' field name
Assembly
Welding
Machine01A ' data sample


without duplicated values, the list of all the required skills.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Sorry but I do not understand your response. What is "len" and "replace"? I
don't want to replace anything. I have a field called "status." In the status
field I have a drop down menu from which a user can choose different phrases
or words to indicate the status. In the same field in the query, I want to
limit the query to show only certain status indicators from the status drop
down. I can put in one status indicator alone, but not more than one. For
example, if my drop down values are "completed," "needs action," and " needs
a phone call" and I only want to show the records that need action and need a
phone call in the query, how do I do that. Does this make more sense?

It is not clear if you look for a string having 2 or 3 delimiters:

"12, 34, 56, 78, 90, 11" has 6 values, and 5 comas

Or if you are looking to something like: find all workers that have 3 or 4
of the required skills (given).


In the first case,

len(yourString) - len(replace(YourString, "," ,""))

returns the number of coma present in your string, so,

(len(yourString) - len(replace(YourString, "," ,""))) BETWEEN 2 AND 3

should do.



In the second case, I assume each given skill is in a different records (NOT
a string list). Something like


SELECT w.workerID
FROM workersSkills AS w INNER JOIN wantedSkills AS s
ON w.skill = s.skill
GROUP BY w.workerID
HAVING COUNT(*) BETWEEN 2 AND 3


would do. The table WorkersSkills is like

WorkerID, Skill ' field names
John, FirstAid
John, Welding
John, Security
....
Mary, FirstAid
.....


and WITHOUT duplicated rows.

while the table wantedSKills is like:

skill ' field name
Assembly
Welding
Machine01A ' data sample


without duplicated values, the list of all the required skills.



Hoping it may help,
Vanderghast, Access MVP

I am trying to exclude text data that originates in a lookup field in the
table, in a query (in other words there are about 6 things in the lookup
and
I want to design a query that will only contain 3 or 4 of the values in
the
lookup). I have tried:

"x"; "y"; "z" in the criteria line of the query design as well as
"x"&"y"&"z" and x&y&z

Nothing is working. I can include or exclude one value alone[such as: <>x
or
"x"], but it refuses to accept more than one. I know there is a way to
include or exclude more than one criteria in a query. What am I doing
wrong?

Thank you.

Debbie

 

[Marshall Barton]

I am trying to exclude text data that originates in a lookup field in the
table, in a query (in other words there are about 6 things in the lookup and
I want to design a query that will only contain 3 or 4 of the values in the
lookup). I have tried:

"x"; "y"; "z" in the criteria line of the query design as well as
"x"&"y"&"z" and x&y&z

Nothing is working. I can include or exclude one value alone[such as: <>x or
"x"], but it refuses to accept more than one. I know there is a way to
include or exclude more than one criteria in a query. What am I doing wrong?

In the design grid's criteria line you can use one of these:

"x" Or "y" Or "z"
or
="x" Or ="y" Or ="z"
or
IN("x", "y", "z")

 

[Michel Walsh]

len and replace are VBA function working on arguments, and a little bit like
addition in 2+3 does not alter neither 2, neither 3, it was not modifying
any data STORED in a table.


But from what you mention, it is a matter to use an OR, or an IN:


status IN( "needs action", "needs a phone call")


Sure, if you want not hard coded constant, you can use a control, on a form,
but since a combo box can only select one item, you may need to use
something else if more than one value is intended.


status = FORMS!formName!ComboBoxName


as example, CAN be used as a where condition to open a form (or as a WHERE
clause in a query).


status = FORMS!formName!ComboBoxName1 OR status =
FORMS!formName!ComboBoxName2


is a possible solution, for two values, but not very elegant. For something
requiring less form-space and allowing a variable amount of 'values' to be
selected, a list box allowing multiple selection is probably a better
avenue. An example is available at
http://www.mvps.org/access/reports/rpt0005.htm



Hoping it may help,
Vanderghast, Access MVP

Sorry but I do not understand your response. What is "len" and "replace"?
I
don't want to replace anything. I have a field called "status." In the
status
field I have a drop down menu from which a user can choose different
phrases
or words to indicate the status. In the same field in the query, I want to
limit the query to show only certain status indicators from the status
drop
down. I can put in one status indicator alone, but not more than one. For
example, if my drop down values are "completed," "needs action," and "
needs
a phone call" and I only want to show the records that need action and
need a
phone call in the query, how do I do that. Does this make more sense?

It is not clear if you look for a string having 2 or 3 delimiters:

"12, 34, 56, 78, 90, 11" has 6 values, and 5 comas

Or if you are looking to something like: find all workers that have 3 or
4
of the required skills (given).


In the first case,

len(yourString) - len(replace(YourString, "," ,""))

returns the number of coma present in your string, so,

(len(yourString) - len(replace(YourString, "," ,""))) BETWEEN 2 AND
3

should do.



In the second case, I assume each given skill is in a different records
(NOT
a string list). Something like


SELECT w.workerID
FROM workersSkills AS w INNER JOIN wantedSkills AS s
ON w.skill = s.skill
GROUP BY w.workerID
HAVING COUNT(*) BETWEEN 2 AND 3


would do. The table WorkersSkills is like

WorkerID, Skill ' field names
John, FirstAid
John, Welding
John, Security
....
Mary, FirstAid
.....


and WITHOUT duplicated rows.

while the table wantedSKills is like:

skill ' field name
Assembly
Welding
Machine01A ' data sample


without duplicated values, the list of all the required skills.



Hoping it may help,
Vanderghast, Access MVP

I am trying to exclude text data that originates in a lookup field in
the
table, in a query (in other words there are about 6 things in the
lookup
and
I want to design a query that will only contain 3 or 4 of the values in
the
lookup). I have tried:

"x"; "y"; "z" in the criteria line of the query design as well as
"x"&"y"&"z" and x&y&z

Nothing is working. I can include or exclude one value alone[such as:
<>x
or
"x"], but it refuses to accept more than one. I know there is a way to
include or exclude more than one criteria in a query. What am I doing
wrong?

Thank you.

Debbie

 

[Guest]

THANK YOU!

I am trying to exclude text data that originates in a lookup field in the
table, in a query (in other words there are about 6 things in the lookup and
I want to design a query that will only contain 3 or 4 of the values in the
lookup). I have tried:

"x"; "y"; "z" in the criteria line of the query design as well as
"x"&"y"&"z" and x&y&z

Nothing is working. I can include or exclude one value alone[such as: <>x or
"x"], but it refuses to accept more than one. I know there is a way to
include or exclude more than one criteria in a query. What am I doing wrong?

In the design grid's criteria line you can use one of these:

"x" Or "y" Or "z"
or
="x" Or ="y" Or ="z"
or
IN("x", "y", "z")