Find all records that contain certain value.

[James]

I am trying to find all records that contain one of the values from the query
below. Currently if contact = true and the other two are false on the
sub-form it will only find records where contact = true and the others are
false. I would like to find all records that contain contact = true no
matter what the values are on the form are for the other two boleans.
Basically If contact = true then find all records where contact = true and
the other two are wildcards. Any ideas?

SELECT CustomerInfo.*, CustomerInfo.Contact, CustomerInfptOut,
CustomerInfo.Archive
FROM CustomerInfo
WHERE (((CustomerInfo.Contact)=[forms].[report options].[contact]) AND
((CustomerInfptOut)=[forms].[report options].[optout]) AND
((CustomerInfo.Archive)=[forms].[report options].[archive]));

 

[Todd Shillam]

Here's an idea...create a couple more queries; however, bound whichever fields you would like to the form's controls. For example, in a query, you could bound the 'contact' field to your form's contact combobox.

Then just use the DLookup function to 'test' and determine if such record(s) exist.

Best regards,

Todd

I am trying to find all records that contain one of the values from the query
below. Currently if contact = true and the other two are false on the
sub-form it will only find records where contact = true and the others are
false. I would like to find all records that contain contact = true no
matter what the values are on the form are for the other two boleans.
Basically If contact = true then find all records where contact = true and
the other two are wildcards. Any ideas?

SELECT CustomerInfo.*, CustomerInfo.Contact, CustomerInfptOut,
CustomerInfo.Archive
FROM CustomerInfo
WHERE (((CustomerInfo.Contact)=[forms].[report options].[contact]) AND
((CustomerInfptOut)=[forms].[report options].[optout]) AND
((CustomerInfo.Archive)=[forms].[report options].[archive]));

 

[John Spencer (MVP)]

Your requirements are a bit unclear.

You may be able to solve this by changing the ANDs to ORs.

Or you may need to change the query.

So, If Forms![Report Options]!Contact is True then you wish to ignore the other
criteria.

What do you want to have happen if OptOut or Archive is True?

So here are some of the possible outcomes you want.

Contact = True; OptOut=False; Archive = False
Then return All records where Contact is True.

Contact = False; OptOut=False; Archive = False
Then return only records where all are False

Contact = False; OptOut=True; Archive = False
Then return ??? Only those with OptOut = True and Contact = False and
Archive=false Or some other thing - all records where OptOut = True and ignore
the other two.


The following Where clause should return records where any one checkbox is set
to true or if two checkboxes are set to true then it will find all those for
each of the checkboxes.

SELECT *
FROM CustomerInfo
Where (CustomerInfo.Contact = Forms![Report Options]!Contact AND
Forms![Report Options]!Contact = True)
OR
(CustomerInfptOut=[forms]![report options]![optout] AND
[forms]![report options]![optout]=True)
OR
(CustomerInfo.Archive=[forms]![report options]![archive] AND
[forms]![report options]![archive] = True)