Checkbox Query - ANY vs ALL vs SOME? 1

[hende10]

I have a checkbox selection form, where the user searches for any vendor that matches any selected machine:

SELECT qrySelectMach.VendNo, qrySelectMach.VendName, qrySelectMach.VendMachMattGrind, qrySelectMach.VendMachThrdGrind, qrySelectMach.VendMachCylGrind, qrySelectMach.VendMachIDGrinder, qrySelectMach.VendMachBlanchGrind, qrySelectMach.VendMach15Lat, qrySelectMach.VendMach35Lat, qrySelectMach.VendMach3ALat, qrySelectMach.VendMachAxisLat, qrySelectMach.VendMachAxisMC, qrySelectMach.VendMachWireEDM, qrySelectMach.VendMachPlunEDM, qrySelectMach.VendMachKT, qrySelectMach.VendMachBull, qrySelectMach.VendMachT20, qrySelectMach.VendMach1015V, qrySelectMach.VendMachV655, qrySelectMach.VendMachMeuser, qrySelectMach.VendMachZL15, qrySelectMach.VendMachZL35, qrySelectMach.VendMachWeld
FROM qrySelectMach
WHERE (((qrySelectMach.VendMachMattGrind)=True And (qrySelectMach.VendMachMattGrind)=[Forms]![frmSelectMach]![VendMachMattGrind])) OR
.
.
.
OR (((qrySelectMach.VendMachWeld)=True And (qrySelectMach.VendMachWeld)=[Forms]![frmSelectMach]![VendMachWeld]));

While this works for any vendor that matches any selected machine, how can I select only the vendors that match all of the selected machines? I tried replacing the ORs with ANDs, but that returns nothing unless all of the checkboxes are checked, and a vendor has all of it's machines checked. Is this possible through a straight query, or do I need to build a temp file to query against?

Also, is there a way to rate the success of a match? If three checkboxes are checked, a vendor matching all 3 would rate 100, 2 out of 3 rates 67, etc..

Thanks,

Hende

 

[hende10]

For a little less detail, here is a reduced example:

I have a query that will select each vendor that matches any of the two checkboxes, VendMachMattGrind and VendMachThrdGrind. The SQL follows:

SELECT qrySelectMach.VendNo, qrySelectMach.VendName, qrySelectMach.VendMachMattGrind, qrySelectMach.VendMachThrdGrind
FROM qrySelectMach
WHERE (((qrySelectMach.VendMachMattGrind)=True And (qrySelectMach.VendMachMattGrind)=[Forms]![frmSelectMach]![VendMachMattGrind]))
OR (((qrySelectMach.VendMachThrdGrind)=True And (qrySelectMach.VendMachThrdGrind)=[Forms]![frmSelectMach]![VendMachThrdGrind]));

I want to convert this to only select each vendor that matches both of the criteria, but have had no luck. Can this be done in simple SQL, or do I need to build a temp table off of the checkbox selections?

Any help would be appreciated.

Thanks,

Hende

 

[PHV]

You wanted this criteria ?
WHERE qrySelectMach.VendMachMattGrind=[Forms]![frmSelectMach]![VendMachMattGrind]
AND qrySelectMach.VendMachThrdGrind=[Forms]![frmSelectMach]![VendMachThrdGrind];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hende10]

PHV,

Thanks for the reply.

I did a poor job explaining what I want to do. I have a checkbox selection form, with 21 different selections. Using the SQL statement in the first post in this thread, I can select all vendors that have any of the checkboxes checked in this selection form. If checkboxes 1, 4, 5, 6, and 8 are selected, it reports any vendor that has any of those 5 checkboxes selected. This works well.

However, I am now trying to only select vendors that have all of the checkboxes selected. When I try to use AND instead of OR in the SQL, the query only returns vendors that have all 21 checkboxes selected, not just 1, 4, 5, 6, and 8.

I'm sorry I am stumbling through trying to explain this. Please ask me any questions you may have.

Thanks,

Hende

 

[PHV]

Have you tried my suggestion ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hende10]

PHV,

I did try your example, and it returned nothing, even though there is one vendor that has both MattGrind and ThrdGrind checked. If I changed your example from AND to OR, it returned two vendors, the one with both MattGrind and ThrdGrind and one with only ThrdGrind checked.

Thanks for you help so far.

Hende.

 

[PHV]

one vendor that has both MattGrind and ThrdGrind checked
In the SAME row ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hende10]

PHV,

Yes, in the same row. The two vendors that come up with the OR query look like this:

VendNo VendName MattGrind ThrdGrind
--------- ------------- -------------- --------------
45 Acme Co. Y Y
55 Inco Inc. N Y

The main table contains the Vendor Name, and is linked to the Machine table through the Vendor Number.

Thanks,

Hende

 

[traingamer]

What about:

... WHERE ((qrySelectMach.VendMachMattGrind=True) [b]or[/b] ([Forms]![frmSelectMach]![VendMachMattGrind] = false)) 
[b]and[/b] ((qrySelectMach.VendMachThrdGrind=True) [b]or[/b]([Forms]![frmSelectMach]![VendMachThrdGrind] = false));

That is, select the record if the VendMachMattGrind = true in the record OR if VendMachMattGrind checkbox is false. etc. etc.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[hende10]

Greg,

Thanks, that did work for the example. However, this query will be ran on 21 different checkboxes, which may make it more complicated then I can handle.

My goal is to have a select screen where the user will check the box next to every machine capability he wants to see from a vendor. The user then can click one of two buttons, either to find vendors that match any of the selected machines, or to find vendors that match all of the selected machine.

Thanks,

Hende

 

[traingamer]

You can always build your where clause in your form and only include clauses for those checkboxes that are actually checked. e.g.

...
strWhere = "where 1=1"
if  ([VendMachMattGrind] = true) then
   strWhere = strWhere & " and (qrySelectMach.VendMachMattGrind=True)"
end if
if  ([VendMachThrdGrind] = true) then
   strWhere = strWhere & " and (qrySelectMach.VendMachThrdGrind=True)"
end if
...

For your 'any' query

...
strWhere = "where 1=2"
if  ([VendMachMattGrind] = true) then
   strWhere = strWhere & " [b]or[/b] (qrySelectMach.VendMachMattGrind=True)"
end if
if  ([VendMachThrdGrind] = true) then
   strWhere = strWhere & " [b]or[/b] (qrySelectMach.VendMachThrdGrind=True)"
end if
...

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[hende10]

Greg,

Only including the checked checkboxes sounds a lot cleaner. How do I build the WHERE clause in the form? Would it be in the On Click event of a button?

Thanks,

Hende

 

[traingamer]

That would be the ideal place, given that you mentioned two different buttons.

 

[hende10]

Thanks Greg. Between your post and a little research I now have this:

strSQL = ""
For intI = 1 To 21
  If Me("chkBox" & intI).Value Then
    strSQL = strSQL & "(" & Me("chkBox" & intI).Tag & "=True) AND "
  End If 
Next intI
strSQL = Left(strSQL, Len(strSQL) - 5)
If strSQL <> "" Then
  strSQL = "SELECT VendNo FROM qrySelectMach WHERE " & strSQL & ";"
End If

Set dbs = CurrentDb
dbs.QueryDefs.Delete "tmpAllMach"
Set qdf = dbs.CreateQueryDef("tmpAllMach", strSQL)
DoCmd.OpenReport "rptAllMachMatch", acViewPreview

It's doing exactly what I need. Thanks!

Hende