CODEWITHSUNDEEP
ms-access-2007
Azketta
Azketta

Reputation: 79

Query Using Input From A Form with A Checkbox

Thank you, ahead of time, to everyone, for your time and attention.

I am doing a very simple database for my job, that was too large to fit into an excel spreadsheet, which is what we usually use here. I apologize ahead of time, as I have very limited knowledge of access, but have to figure this out.

I have about 1,150,000 records and need to be able to search by the following criteria: Part Number (txtPK), Step (txtStep), Skipped Percentage (txtPer), and Bottleneck? (chkARD); ARD is in the database as "Y" or "N".

This code worked to show either only Y or only N:

IIf([Forms]![Skips_Form]![chkARD],"Y", "N")

However, when I tried to adapt it to show "*" instead of "N" it returns no records, instead of all records.

IIf([Forms]![Skips_Form]![chkARD],"Y", "*")

My desire is to show only the records with "Y" when the checkbox is checked, and to show all records when it is not checked.

Upvotes: 1

Views: 99

Answers (2)

Azketta
Azketta

Reputation: 79

Complete Working SQL Code Thank you Lee Mac

SELECT Data.POS, Data.PK, Data.[Step], Data.[ARD?], Skips.Skips, Skips.Total, 
Skips.[Skips %], Data.[OPERATION DESCRIPTION], Data.CHARGE_NUMBER, Data.[MDM_PN],
Data.[PlanVer], Data.[PlanRev], Data.[Task_Desc], Data.[Prod Version]

FROM Data INNER JOIN Skips ON Data.POS = Skips.POS

WHERE 
(((Data.POS) Like "*" & [Forms]![Skips_Form]![txtPK] & "*") 
AND ((Data.[Step]) Like "*" & [Forms]![Skips_Form]![txtStep] & "*") 
AND ((Data.[ARD?]) Like IIf([Forms]![Skips_Form]![chkARD],"Y","*")) 
AND ((Skips.[Skips %])>=[Forms]![Skips_Form]![txtPer])) 

OR 
(((Data.POS) Like "*" & [Forms]![Skips_Form]![txtPK] & "*") 
AND ((Data.[Step]) Like "*" & [Forms]![Skips_Form]![txtStep] & "*") 
AND ((Data.[ARD?]) Like IIf([Forms]![Skips_Form]![chkARD],"Y","*")))

ORDER BY Skips.[Skips %];

Upvotes: 0

Lee Mac
Lee Mac

Reputation: 16015

Presumably you are using these iif statements within the where clause of your query, in conjunction with the = operator - something like:

select *
from YourTable
where ARD = IIf([Forms]![Skips_Form]![chkARD],"Y", "*")

If this is indeed the case, then, when the else argument of the iif statement is returned, the SQL statement becomes:

select *
from YourTable
where ARD = "*"

Hence, this will return records for which the ARD field has a literal value of "*".

Instead, you should either use the like operator, which will allow wildcards such as the asterisk to match any value, e.g.:

select *
from YourTable
where ARD like IIf([Forms]![Skips_Form]![chkARD],"Y", "*")

Or, use some simple boolean logic, such as:

select *
from YourTable
where (not [Forms]![Skips_Form]![chkARD]) or ARD="Y"

Upvotes: 1

Related Questions