[irishgal]

Okay, so I'm a complete beginner to Access and am trying to get it to do something that resembles an if formula.
I have it set so that it's looking for an account number that appears in both tables, but I want it to list it only if the following columns do not equal 402 or 404 or 423:
Code1
Code2
Code3
Code4
I can get it to do it for the 1st column only, because there is always data in that column. If I copy that formula over to each criteria then it will only work if there is data in that column. Any help???

[slurpee55]

It would be easier to understand what you are trying to do if you would make a copy of your db (with dummy data), zip it and upload it.
E.g., 402, 404, 423 - are those column sums, values within the columns, or what?

[cavemanmac]

It sounds like you are trying to do this in a query. Are you using an IIF() formula? Works the same as an IF() formula in excel, except you can't imbed as many in Access as in excel.

[Chris_E]

sounds like a call for nested CASE statements?

[slurpee55]

Actually, it sounds like you are trying to do something in Access in a way more fit for Excel. If you could upload a zipped copy of your db (with fake info if needed) we can help you a lot more

[OBP]

I am also concerned that the data structure is not correct, it is possible that the "Code" fields should be seperate records in a Related table.

[slurpee55]

It sounds a bit like the OP is trying to set multiple filters on several fields. If they are all in the same table, that could be very tricky.

[Ziggy1]

I think OBP is suggesting the table is not "normalized"...there should not be 4 columns listed as Code1-4, but rather should eb a single column named Code containing the values 1 to 4 and for each corresponding "acct".

the values ...402 or 404 or 423 would be in another column by itself, so for each code there is one record and if a account code


name Code Acct
joe 1 401
joe 2 403
Steve 4 402
Kelly 1 404

[slurpee55]

Hi Ziggy...if we had more info from the OP, we could help more...kinda like shooting arrows in the dark and blindfolded right now.