Query Using Input From A Form with A Checkbox

Question

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.

Lee Mac · Accepted Answer · 2019-02-14 18:24:38Z

1

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"

answered Feb 14, 2019 at 18:24

Lee Mac

16.1k6 gold badges38 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Azketta Over a year ago

Thank you so much; it's amazing that just adding Like before that statement made everything work perfectly. After 2 days of struggling with that. I appreciate it. I'm sure that was easy peasy for you, and I appreciate you taking your time to help me understand not only how to fix it, but why it makes a difference.

Azketta Over a year ago

Posted Complete, Working Code; Thank You Again

Lee Mac Over a year ago

@Azketta You're most welcome! Thank you for your appreciation of my time.

Azketta · Accepted Answer · 2019-02-15 15:49:45Z

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 %];

Collectives™ on Stack Overflow

Query Using Input From A Form with A Checkbox

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related