Criteria help

[MattU2]

I am using criteria for multiple columns in my query and they are not limiting the query. The criteria's are the following:

Column 1
"P01"

Column 2
2016

Column 3
>0 and <11

Column 4
>=0.01

These four columns are not properly working in my database. I only have one column that is properly using the criteria to sort the database. I was why my criteria might not be working?

Any help would help
Thanks

 

[Joe4]

Are you entering these in using the visual Query Builder?
If so, are all these Criteria entered on the same row/line, or on different ones?
Note that if they are all entered on the same row, all the conditions are treated as ANDs (meaning, it will only return records that meet all four of your criteria).
If they are entered on different rows, they will be treated as ORs (meaning, it will return any records that meet any one of the four criteria).

 

[MattU2]

Yes, I'm currently using the visual Query Builder. I currently have them all on the same row and it does not sort. I have another column, Column 5, where I put "L70" "L75" "L80" starting at the criteria field then moving down vertically to the "or" section. This is my only field that is currently sorting the data. All of my other columns are on the Criteria line. I've tried to getting rid of all my criteria and trying them one at a time and the criteria still doesn't work. Not sure if this is a query problem or a criteria problem.

 

[MattU2]

I tried to do it one at a time and started to get it working. The problem seems to be when I need to restrict a column with multiple criteria. After working on it for a little bit this is my current problem. For column 1, I need restrictions to F01, F02, F03, F04, F05, F06, and F07. I currently have everything else is the Criteria: line, the issue arises when I have the multiple conditions.

Thanks

 

[Joe4]

OK. Let's just clarify a few things first. Criteria and sorting are two totally unrelated things.

Criteria: These are filters that tell the query which records to return. This has NO affect on Sorting

Sorting: If this row is filled out, it will sort whatever records are returned in that query. This has NO affect on which records are returned. If multiple fields are selected for sorting, it sorts by the left-most one first, working its way to the right.

If you are still having problems, please do the following:
1. Post the SQL code of your query. Simply change the query to SQL View and Copy and Paste the code here.
2. Give us an example of what is not working correctly (with actual data) so we can try to recreate it and test it our for ourselves. Note that you can highlight and Copy and Paste what the query returns and paste it here.

 

[MattU2]

Sorry, very bad choice in my words. I'm only dealing with the criteria only.

Here is my SQL:
SELECT PRDDAT10_ME100M.WDEPT, PRDDAT10_SF120BP1.CZWCNO, PRDDAT10_SF120BP1.IAYRP, PRDDAT10_SF120BP1.IAPER, PRDDAT10_SF120BP1.CZORNO, PRDDAT10_SF120BP1.CZOPNO, PRDDAT10_SF120BP1.CZOPLN, [LRQQTY]*[LRPRSC] AS [Earned Hours], PRDDAT10_SF120BP1.CZTRAN, PRDDAT10_SF120BP1.LRQQTY
FROM PRDDAT10_ME100M, PRDDAT10_SF120BP1 INNER JOIN PRDDAT10_IC125M ON (PRDDAT10_SF120BP1.CZORNO = PRDDAT10_IC125M.LRMONO) AND (PRDDAT10_SF120BP1.CWHNO = PRDDAT10_IC125M.LRPLNT)
WHERE (((PRDDAT10_ME100M.WDEPT)="F01")) OR (((PRDDAT10_ME100M.WDEPT)="F02") AND ((PRDDAT10_SF120BP1.IAYRP)=2016) AND ((PRDDAT10_SF120BP1.IAPER) Between 1 And 11) AND (([LRQQTY]*[LRPRSC])>=0.01) AND ((PRDDAT10_SF120BP1.CZTRAN)="L65") AND ((PRDDAT10_SF120BP1.LRQQTY)>0)) OR (((PRDDAT10_ME100M.WDEPT)="F03") AND ((PRDDAT10_SF120BP1.CZTRAN)="L70")) OR (((PRDDAT10_ME100M.WDEPT)="F04") AND ((PRDDAT10_SF120BP1.CZTRAN)="L75")) OR (((PRDDAT10_ME100M.WDEPT)="F05") AND ((PRDDAT10_SF120BP1.CZTRAN)="L90")) OR (((PRDDAT10_ME100M.WDEPT)="F06")) OR (((PRDDAT10_ME100M.WDEPT)="F07"));


Example of my current data:

WDEPT CZWCNO IAYRP IAPER CZORNO CZOPNO CZOPLN Earned Hours CZTRAN LRQQTY
F01 F85 2017 1 M798000 030 00 24 L60 1
F06 F85 2017 1 M798000 030 00 24 L60 1
F07 F85 2016 12 NZ02933 050 00 0.000001 L60 1
F01 F85 2016 12 NZ02933 050 00 1.93 L60 1
F01 XMP 0 0 M420869 002 00 6.75 L10 3
F07 IN1 0 0 M260999 086 00 2 L10 2
F06 DB0 0 0 M362757 030 00 4.44 L60 12


If you need me to clarify anything please let me know!

 

[Joe4]

OK. Can you let me know what isn't working right, based on your data?
I.e., specify a specific data record that isn't showing up, but should be or vice versa.

 

[MattU2]

I actually got it working now. It had to do with what you initially said about AND and OR's. I initially had a weird combination between AND and OR criteria's. I did not have a few of my criteria's properly defined and not in the same row. Thanks for all the help, work, and patience with me!

 

[Joe4]

I figured it was something like that, but needed a good understanding of what exactly you were trying to accomplish and how it differs from your code.
The devil is always in the details!

Anyway, glad you got it all figured out.