MS Access Equivalent to AND/OR Excel Functions

Question

MS Access Equivalent to AND/OR Excel Functions

Anonymous

Hello,

I have a MS Access database with a table of Product Codes (numbers) and the equivalent Product Category (text). (e.g., 111 = "Cap")

Another table contains a list of products by SKU with their Product Codes. (e.g., SKU 7070 "Blue Cap" = 111)

I use a query to combine the two and show the categories for each SKU rather than the number.

There is one instance where two categories carry the same code. 111 = Cap and 111 = Hat. However, because the Product Code is the key field, I can't have two records, so Access only sees Cap for 111. Unfortunately, I can't change the code for Hat.

I need to write a query that looks in the database for products that don't have the proper code so I can fix them. I hit a snag with this one combination. Every Hat gets flagged for update.

MS Excel has the AND/OR functions where you can specify more than one criteria. Is there an equivalent MS Access function?

What I tried was: IIF(AND(OR([Product Category]="Cap",[Product Category]="Hat"),[Product Code]=111),"Ok","Fix")))

I get an error dialog telling me I have an expression with invalid syntax.

Is there a way to exclude Hat from showing up in the query as incorrect, yet still allow it to be flagged if the record has any code other than 111? I tried using "&" but that didn't work either.

Any help/suggestion is greatly appreciated. I'm using MS Office 2013.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Answer 1

Access is not "Excel on Steroids". It's quite different in its storage and logic! Applying Excel techniques (as in this case) will be an exercise in frustration!

That said, Access does AND and OR logic, actually IMO better than Excel does: try this query (copy and paste it into the SQL window of a new Query):

SELECT * FROM yourtable WHERE [Product Code] <> 111 AND [Product Category] IN ("Hat", "Cap")

The real solution, though, is to correctly normalize your tables, If the [Product Category] value depends on the [Product Code] you really should have a ProductCodes table with the Product Code as the Primary Key, and just store the Product Code without the Category in your SKU table; use a Query to link to the ProductCodes table to look up what a 111 should be. You're using a relational database, not a spreadsheet; life will be much easier if you use it as such!

Answer 2

Thanks for your insights. I fully understand the differences having worked with both since the early 90s, but may have misstated my problem. The IIF expression is part of a query that uses several linked tables, and is created using the Expression Builder in the query GUI. So, it's not a stand alone SELECT statement but one part of a larger statement.

I tried applying your statement logic to the expression, but haven't been successful yet. But I'll keep trying.

My Access database "tables" are mostly linked Excel spreadsheets that are derived from exports from an ERP system using a proprietary query engine. Normalization might be an option. I created the Product Code table to link two ERP SKU tables, one of which has SKU-Category, and the other has SKU-Code. I have yet to find the ERP table that defines category by code. Using the code as the key field in my table was a choice, not a requirement. The table has two fields, Code and Category. I populated the table by extracting all possible combinations of code and category from my exports. It's the use of a key field that keeps me from having two categories share a code. Defining both fields as key doesn't work either. Having no key field creates worse problems.

Again, thanks, and I'll keep plugging away!

Share via

MS Access Equivalent to AND/OR Excel Functions

2 answers