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.
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"