IF statement in access query

Question

I have a report where I show product that has been ordered from a vendor and needs inspection once the "delivered" checkbox is true. The only issue I'm having is that I sometimes order from different vendors for the same project. What I would like to do is have the query check for the checkbox=true for both the ordered and delivered from Vendor 1 and then the IF statement to check the checkbox for the ordered for Vendor 2 and if true, then check if delivered for Vendor 2 is also true. If so, then the items ordered for the project need to be inspected.

I have the query written so that Vendor 1 is checked, but I'm not quite sure how the IF statement is used and the format for it.

Answer 1

Where a number of Boolean expressions need to be evaluated like this it would be best to write a little function, declared as Boolean.  You can then incorporate multiple If…End If constructs in the function to set the function's return value to TRUE or FALSE.

Answer 2

You talk about products, orders, projects and vendors so it is very unlikely that one table will suffice. Also you seem to have Vendor1, Vendor2. That sounds as bad design (repeating groups).

I think you should review your design before moving on.

Answer 3

Sorry, I thought it was clear what I'm doing.

First of all, all of the fields used in the Query are on one table, so no relationships needed. The fields are ID, Decedent, Vendor1Name, OrderedV1, DeliveredV1 and Inspected,

There is a 2nd query with ID, Decedent, Vendor2Name, OrderedV2, DeliveredV2 and Inspected.

Currently, there is one report that checks to see whether the checkbox for OrderedV1 and DeliveredV1 are true and the Inspected is false. If so, The ID and Decedent go on a "needs inspection" report.

I have a second report that has similar checks, but for Vendor2.

I want to use an IF statement on my query to include Vendor2 with Vendor1, because if we use two vendors to complete an order for a project, I want to show that on a single report. The only time we use Vendor2 is if we have to order from multiple places for a single project; therefore, the first query for Vendor1 is usually good enough.

I hope this clarifies my question.

Answer 4

You should post a sample file so we can check what you have done.

If not, you should at least show the image of the relation window with the tables showing all the fields and the Sql predicate of the query you should use.

Answer 5

Some sample data with the table/field names and expected results would be valuable to those who would like to help you. Help us help you.