Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to choose more than one value in the same field using the
"AND" criteria. For example: I have a listing of store #'s in one field and
a map of that store number in another field. I want to type store # 5.6.7.8
and 9 and have the query pull all 5 of those stores. What do I type as the
criteria?
Thank you VERY much!
 
I think you want an OR rather than an AND, since you want the query to return
information if the store number is 5 or 6 or 7 or 8 or 9. You could also use
an In (set) constraint, such as In (5,6,7,8,9).

- M.L. Cottingham
 
Is it possible to choose more than one value in the same field using the
"AND" criteria. For example: I have a listing of store #'s in one field and
a map of that store number in another field. I want to type store # 5.6.7.8
and 9 and have the query pull all 5 of those stores. What do I type as the
criteria?
Thank you VERY much!
Click to expand...

AND and OR are *NOT* English language conjuctions: they are Boolean
logical operators. X AND Y is TRUE if both X and Y are TRUE
expressions; if either is FALSE then X AND Y evaluates to FALSE.

A query criterion is a logical expression; if it evaluates to TRUE
then the record is retrieved, and if it's FALSE the record is skipped.

So if you have [Store Number] = 5 AND [Store Number] = 6 you will
never retrieve any stores, because for any individual record, the
store number cannot possibly be equal to 5 and also equal to 6!

Two suggestions: you can use OR criteria (i.e. retrieve this record if
the store number is 5, OR the store number is 6); or you can use the
IN() operator. Put

IN (5, 6, 7, 8)

on the criteria line and it will (again using OR logic) return records
for any of the selected values.

John W. Vinson[MVP]
 
In the where clause set [store #] in ([CONDITION]). When you run the query,
MS-Access will pop up a window to allow you to assign a value to [condition].
Since it is inside an "in", this should allow you to a list of one or more
values.

- M.L. Cottingham
 
I want it to show 5,6,7,8 AND 9. I know how to do this in a query by ising
IN and listing the store numbers, but can I phrase it so that it will ask me
when the query is ran?
Thank you for your quick response!
 
Thank you very much for your help, however it's not working for me and I'm
not a computer goo roo at this stuff so thanks for being patient with me.
When I type in "In ([Store #?])" in the criteria, it will ask me for the
store # which is good, but I want it to let me type in a couple store
numbers. I tried typing 5.6.7.8 in the box, but that doesn't work. Is there
a way of typing the input with "."";" or "/" to determine different values?

John Vinson said:
Is it possible to choose more than one value in the same field using the
"AND" criteria. For example: I have a listing of store #'s in one field and
a map of that store number in another field. I want to type store # 5.6.7.8
and 9 and have the query pull all 5 of those stores. What do I type as the
criteria?
Thank you VERY much!
Click to expand...

AND and OR are *NOT* English language conjuctions: they are Boolean
logical operators. X AND Y is TRUE if both X and Y are TRUE
expressions; if either is FALSE then X AND Y evaluates to FALSE.

A query criterion is a logical expression; if it evaluates to TRUE
then the record is retrieved, and if it's FALSE the record is skipped.

So if you have [Store Number] = 5 AND [Store Number] = 6 you will
never retrieve any stores, because for any individual record, the
store number cannot possibly be equal to 5 and also equal to 6!

Two suggestions: you can use OR criteria (i.e. retrieve this record if
the store number is 5, OR the store number is 6); or you can use the
IN() operator. Put

IN (5, 6, 7, 8)

on the criteria line and it will (again using OR logic) return records
for any of the selected values.

John W. Vinson[MVP]
Click to expand...
 
idontgetit said:
I want it to show 5,6,7,8 AND 9. I know how to do this in a query by ising
IN and listing the store numbers, but can I phrase it so that it will ask me
when the query is ran?
Thank you for your quick response!
Click to expand...


I think you are asking for a condition like this:

WHERE InStr("," & Replace([enter store numbers to find],
" ", "") & "," , "," & storenum & ",") > 0

where storenum is the name of the table's field with the
store number.

Note that the comma in what you enter for the list of stores
to find is required. Just for safety's sake the Replace
function is used to remove any spaces. Without the Replace,
if you entered a list like 5, 6, 7, 8 the only one that it
would find is 5 because the others are all preceded by a
space.

I hope you are not doing this on large datasets because
indexing won't help this kind of criteria.
 
Marshall said:
idontgetit said:
I want it to show 5,6,7,8 AND 9. I know how to do this in a query by ising
IN and listing the store numbers, but can I phrase it so that it will ask me
when the query is ran?
Thank you for your quick response!

:
Click to expand...



I think you are asking for a condition like this:

WHERE InStr("," & Replace([enter store numbers to find],
" ", "") & "," , "," & storenum & ",") > 0

where storenum is the name of the table's field with the
store number.

Note that the comma in what you enter for the list of stores
to find is required. Just for safety's sake the Replace
function is used to remove any spaces. Without the Replace,
if you entered a list like 5, 6, 7, 8 the only one that it
would find is 5 because the others are all preceded by a
space.

I hope you are not doing this on large datasets because
indexing won't help this kind of criteria.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The reason entering "5,6,7,8" will only return 5 is because the values
are evaluated as ONE string. Since "5" is the first number in that
string (5 is a number ",6,7,8" is not because of the commas - same for
spaces) it is the only value returned when the string is converted to a
number. Type this into the Debug window:

? Val("5,6,7,8")

Indexes work fine for this type of criteria. Internally, JET converts
the list

from: storenum In (5,6,7,8)

to: storenum = 5 or storenum = 6 or storenum = 7 or storenum = 8

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjYC6oechKqOuFEgEQKKEwCfdA/5T3f6fmj+0R+LH//I0okc6P0AoOJV
npFe/AzYB95cdWNPbx9nrpxW
=OXc+
-----END PGP SIGNATURE-----
 
I GOT IT TO WORK!!!
Thank you very,very much!!! I never used this forum to post a question and
it worked pretty good.
Thanks Again!

Marshall Barton said:
idontgetit said:
I want it to show 5,6,7,8 AND 9. I know how to do this in a query by ising
IN and listing the store numbers, but can I phrase it so that it will ask me
when the query is ran?
Thank you for your quick response!
Click to expand...


I think you are asking for a condition like this:

WHERE InStr("," & Replace([enter store numbers to find],
" ", "") & "," , "," & storenum & ",") > 0

where storenum is the name of the table's field with the
store number.

Note that the comma in what you enter for the list of stores
to find is required. Just for safety's sake the Replace
function is used to remove any spaces. Without the Replace,
if you entered a list like 5, 6, 7, 8 the only one that it
would find is 5 because the others are all preceded by a
space.

I hope you are not doing this on large datasets because
indexing won't help this kind of criteria.
Click to expand...
 
Back
Top