selecting criteria from 2 columns

  • Thread starter Thread starter AH
  • Start date Start date
A

AH

Hello,

I need to choose criteria1 or criteria2 in this query.
For example, I want my query to select records based on
column1 or column2:
select from column1 any number bigger than 10
OR
select from column2 any number bigger than 20

When I put ">10" in the criteria box in column 1 and
put ">20" in the criteria box in column 2, I get records
that are bigger than 10 in column1 AND (not OR) bigger
than 20 in column2!

In other word, how can I make this show if I have a
record with 11 in column1 and 19 in column2?

Thanks for the help
 
Put your >20 on a different criteria row to the >10, from memory subsequent
rows have the heading OR rather than CRITERIA.

HTH
Sam
 
Hi AH,

put the second criteria into the OR row for the relevant
column. ie put it on the line below the first criteria
not on the same line.

hth

chas
 
Thanks alot guys for your replies but I have a new
problem now....

Now I am getting all correct records after I followed
your advice, but it is showing me unwanted criteria.

For the same example of having a record with 11 in
column1 and 19 in column2, I am supposed to have the 11
show in column1 since it matches the criteria, but have
19 of column2 of the same record not show because it does
NOT match the column2 criteria. But as you guessed it, it
shows the whole record as long as one criteria matches.
How can I fix that. Thanks
 
Dear Ahalim:

Criteria do not determine which columns appear in your query, but which rows
appear. It sounds like you now have the proper rows in the query, but want
to remove values in columns. This is not done with criteria at all.

Rather, you need to calculate the values in these columns. Do not do this
in the same column of cells that display the values from the table and
contain the criteria. Start a new query column that will display the
calculated values.

Each of these new columns in the query must be named. I'll do just one of
them here, and name it Column1Calc. Put this in the "Field" row of the new
column:

Column1Calc: IIf(Column1 > 10, Column1, NULL)

This will leave the calculated column to be null when the value in Column1
is not more than 10. Alternatively, you could put in 0 or some other
information.

I recommend leaving the Display for the actual values of Column1 and Column2
checked so you can quickly check the results of the query to see they are
working properly. Only after you are confident the query is perfected
should you uncheck Display for them, or you can just leave them so and not
use them in the rest of your software.
 
Thank you Tom, that worked like a charm!
-----Original Message-----
Dear Ahalim:

Criteria do not determine which columns appear in your query, but which rows
appear. It sounds like you now have the proper rows in the query, but want
to remove values in columns. This is not done with criteria at all.

Rather, you need to calculate the values in these columns. Do not do this
in the same column of cells that display the values from the table and
contain the criteria. Start a new query column that will display the
calculated values.

Each of these new columns in the query must be named. I'll do just one of
them here, and name it Column1Calc. Put this in the "Field" row of the new
column:

Column1Calc: IIf(Column1 > 10, Column1, NULL)

This will leave the calculated column to be null when the value in Column1
is not more than 10. Alternatively, you could put in 0 or some other
information.

I recommend leaving the Display for the actual values of Column1 and Column2
checked so you can quickly check the results of the query to see they are
working properly. Only after you are confident the query is perfected
should you uncheck Display for them, or you can just leave them so and not
use them in the rest of your software.
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts




.
Click to expand...
 
Back
Top