Using Criteria in Query

  • Thread starter Thread starter bridgemonkey
  • Start date Start date
B

bridgemonkey

Should be simple, table with 5 columns that can contain #'s 0-9 or "N" (this
is the issue). Trying to create query with Critieria, actually using the
"Or" option to return the primary key for any record containing a # <=5.
This should be quick but it reads "N" as <=5 ... suggestions?

Thanks in advance
 
Thanks, that's what I thought ... for some reason that doesn't want to work,
it pulls all records. This should be so simple. this is the set up.
Table
Key Value 1 Value 2 Value 3 Value 4
1 N N N 4
2 6 6 7 N
3 N N N 6
4 5 8 4 N

Putting <>"N" And <="5" in the "or" criteria line for all "Values" a select
query should pull records 1 & 4???
 
by the way, "Values" are stored as text since "N" is the default value, not
sure if that makes a difference.
thanks again for any input.
 
You could use
<="5"
OR
Like "[0-5]"

Those should both work. Alphabetically, "N" is greater than "5". And the like
criteria returns any record where the value is 0,1,2,3,4, or 5. Your original
query should also work UNLESS there is a leading space (or other character) in
the field.

"Space 6" is less than "5" and "Space N" is not "N"

You might try
LIKE "*[0-5]*"
That should return records that have 0,1,2,3,4, or 5 anywhere in the value and
ignore any leading or trailing spaces or other invisible characters.

If you are linking to non-Access (JET) tables they can contain leading and
trailing characters that are not visible.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
You should not be using a spreadsheet in a database but this table structure --
Key Value Record
1 N 1
1 N 2
1 N 3
1 4 4
2 6 1
2 6 2
2 7 3
2 N 4

Use this query --
SELECT Key
FROM YourTable
WHERE Val([Value 1]) <=5 OR [Value 1] ="N" OR Val([Value 2]) <=5 OR [Value
2] ="N" OR Val([Value 3]) <=5 OR [Value 3] ="N" OR Val([Value 4]) <=5 OR
[Value 4] ="N";
 
Thanks John, but I must have something fundamentally wrong here. I'm eating
some serious humble pie over here. Pulling the identifying column value out
of a large table when any of 4 values for that record are 5 or less shouldn't
be that difficult. They all have to be in the "or" line of the select query?


I put the table and query in a blank db on ftp if interested in seeing the
details ... otherwise thanks again for the effort.
ftp://s1107102645:[email protected]

John Spencer said:
You could use
<="5"
OR
Like "[0-5]"

Those should both work. Alphabetically, "N" is greater than "5". And the like
criteria returns any record where the value is 0,1,2,3,4, or 5. Your original
query should also work UNLESS there is a leading space (or other character) in
the field.

"Space 6" is less than "5" and "Space N" is not "N"

You might try
LIKE "*[0-5]*"
That should return records that have 0,1,2,3,4, or 5 anywhere in the value and
ignore any leading or trailing spaces or other invisible characters.

If you are linking to non-Access (JET) tables they can contain leading and
trailing characters that are not visible.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Thanks, that's what I thought ... for some reason that doesn't want to work,
it pulls all records. This should be so simple. this is the set up.
Table
Key Value 1 Value 2 Value 3 Value 4
1 N N N 4
2 6 6 7 N
3 N N N 6
4 5 8 4 N

Putting <>"N" And <="5" in the "or" criteria line for all "Values" a select
query should pull records 1 & 4???
Click to expand...
Click to expand...
 
HERE is the SQL that you should be using. Enter this in the SQL view window
and switch back to the Design window.

SELECT Inspection_Data.SFN, Inspection_Data.Date
, Inspection_Data.[NBI 58 (Deck)]
, Inspection_Data.[NBI 59 (Superstructure)]
, Inspection_Data.[NBI 60 (Substructure)]
, Inspection_Data.[NBI 62 (Culvert)]
FROM Inspection_Data
WHERE (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 58 (Deck)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 59 (Superstructure)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 60 (Substructure)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 62 (Culvert)])<="5"))
ORDER BY Inspection_Data.SFN;


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks for taking a look at that for me. Never used the "or" statement like
that but now I know.

Thanks again!

John Spencer said:
HERE is the SQL that you should be using. Enter this in the SQL view window
and switch back to the Design window.

SELECT Inspection_Data.SFN, Inspection_Data.Date
, Inspection_Data.[NBI 58 (Deck)]
, Inspection_Data.[NBI 59 (Superstructure)]
, Inspection_Data.[NBI 60 (Substructure)]
, Inspection_Data.[NBI 62 (Culvert)]
FROM Inspection_Data
WHERE (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 58 (Deck)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 59 (Superstructure)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 60 (Substructure)])<="5"))
OR (((Inspection_Data.Date) Between #1/1/2008# And #12/31/2008#) AND
((Inspection_Data.[NBI 62 (Culvert)])<="5"))
ORDER BY Inspection_Data.SFN;


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Thanks John, but I must have something fundamentally wrong here. I'm eating
some serious humble pie over here. Pulling the identifying column value out
of a large table when any of 4 values for that record are 5 or less shouldn't
be that difficult. They all have to be in the "or" line of the select query?


I put the table and query in a blank db on ftp if interested in seeing the
details ... otherwise thanks again for the effort.
ftp://s1107102645:[email protected]
Click to expand...
Click to expand...
 
Back
Top