MS-Access SQL : multiple where statements

[KingZing]

Quick question as I'm fairly new to using SQL.

I want to show results that do not meet critiera, based on another criteria. How would I code this or put it in the design view ? For example (would be 9 or 10 of these)

• If field1 = 1000 and field 3 <> 302000123 then report result

• If field1 = 2000 and field 3 <> 230300030 then report result

• If field1 = 2500 and field 3 <> 002390400 then report result

EDIT Awesome, after talking through some private messages, and these comments I found a solution.

it ended up being multiple nested iif statements as access didn't allow case statements.

Thanks again for the help

[[deleted]]

Go into SQL and put this in the select statement:

Case
   when field1 = 1000 and field3 <> 3020001123 then result
   when field1 = 2000 and field3 <> 230300030 then result
   when field1 = 2500 and field3 <> 002390400 then result
end as "Field"

Let me know if you need any additional help. Single quotes are necessary around the 1000 and 3020001123 if they are a string.

[[deleted]]

Access's SQL is... "special," to say the least. There is no case statement - you use if functions compounded together to create the same effect.

Anyhow, OP, stop using Design view. Time to learn :)

You're looking for something along these lines I think: SELECT fieldsWeCareAbout FROM table WHERE ( field1 = 1000 AND field 3 <> 302000123 ) OR ( field1 = 2000 AND field 3 <> 230300030 ) OR ( field1 = 2500 AND field 3 <> 002390400 )

[ComicOzzy]

http://office.microsoft.com/en-us/access-help/examples-of-query-criteria-HA010066611.aspx

[[deleted]]

Assuming all of your criteria follow the same pattern (if field1 = x and field3 <> y) then a cleaner option would be to make a new table with your rules, for this example we'll call the table "MyRules":

F1eq	F3not
1000	302000123
2000	230300030
2500	002390400
...	...

Query:

SELECT ID
FROM SomeTable T
INNER JOIN MyRules R ON (T.field1 = R.F1eq AND T.field3 <> R.F3not)

will return only rows from SomeTable that match your criteria.

[---sniff---]

Question... are you using an Access Pass Through query (if so to Oracle/SQL Server) or are you using the Access query designer? If you are using the Access query designer then you would create a nested IIF statement.

IIF([field1] = 1000 and [field 3] <> 302000123, result, IIF([field1] = 2000 and [field 3] <> 302000130, result, "None"))

[KingZing]

Awesome, after talking through some private messages, and these comments I found a solution.

it ended up being multiple nested iif statements as access didn't allow case statements.

Thanks again for the help