Help with criteria.

[Mike]

I have a query with 3 criteria. I want all records with a
ReasonCode of "Large Truck" AND (Currentcode of "LL" OR
PreviousCode of "LL")to be displayed.

The SQL looks like this: WHERE (((ReasonCode)="Large
Truck") AND ((CurrentCode)="LL")) OR (((PreviousCode)
="LU")).

I'm getting results and shouldn't because there are no
Large Trucks in this table.

I've tried putting a (at the beginning of the CurrentCode
and at the end of the PreviousCode sections but I'm still
getting results.

Can anyone help and tell me what I am doing wrong when I'm
setting up the criteria in design view?

Reason = Large Truck (on criteria line)
CurrentCode = LL ( on 'or" line)
PreviousCode = LL ( on the line below the 'or' line).

Thanks for the help.

 

[Evi]

Do you mean that you want a record that contains Large Truck in ReasonCode
AND EITHER LL in Current Code OR LL in Previous code (in which case you
won't get any records in a table that doesn't have LargeTruck under
ReasonCode in any of the records.

Or do you mean

I'll accept a record if it has LL in Previous code, even if it doesn't have
ReasonCode =LargeTruck or CurrentCode = LL

Your code says
I will accept any record that has in it ReasonCode=LargeTruck, CurrentCode =
LL

I will also accept any record that has LL under Previouscode, even if it
doesn't have LargeTruck in the ReasonCode.

If you want it to only have records that have large truck in it then add
LargeTruck a second time, add it in the Or row of ReasonCode as well as in
the Criterial row.

Evi

 

[John Vinson]

I have a query with 3 criteria. I want all records with a
ReasonCode of "Large Truck" AND (Currentcode of "LL" OR
PreviousCode of "LL")to be displayed.

The SQL looks like this: WHERE (((ReasonCode)="Large
Truck") AND ((CurrentCode)="LL")) OR (((PreviousCode)
="LU")).

I'm getting results and shouldn't because there are no
Large Trucks in this table.

The parenthesis nesting is the problem. Try editing the SQL to

WHERE ReasonCode="Large Truck" AND (CurrentCode)="LL" OR
PreviousCode="LL")

(getting rid of Access' extra parentheses and changing your apparent
typo of LU).

I've tried putting a (at the beginning of the CurrentCode
and at the end of the PreviousCode sections but I'm still
getting results.

Can anyone help and tell me what I am doing wrong when I'm
setting up the criteria in design view?

Reason = Large Truck (on criteria line)
CurrentCode = LL ( on 'or" line)
PreviousCode = LL ( on the line below the 'or' line).

Equivalent to my suggestion above - put "Large Truck" on BOTH criteria
lines so that it's combined using AND logic with both the CurrentCode
and PreviousCode.

 

[Chris]

The reason you are getting results where you shouldn't is
the position of the ()'s. Chnage your SQL to show what
you have written:

WHERE ReasonCode="Large Truck" AND (CurrentCode="LL" OR
PreviousCode="LU")

That should do it.


Chris