iif statement to read fields from form not returning values

[CrArC]

Hey there!
I've been searching about this forum and found a lot of threads on this sort of issue but haven't been able to figure it out from those alone, so feeling a bit useless long winded explanation by the way... I'm useless at explaining!

Well basically I have a query that is supposed to power a flexible search form, where you choose the fields you want to search by and enter your criteria. The form contains labelled fields with checkboxes next to them, which you tick if you want to search for something in that field. The results are displayed in a subform which reads from the query.

The problem is with the query! My solution seemed to work fine when I put my criteria in just one field when testing (that is the fields in query design builder, not the form), but when I applied the same to two fields it didn't. Instead, it would return nothing unless I specified exact and correct criteria for BOTH fields I had set up with my solution. So whereas before I could tick "Customer" and search for a customer account number and it would display ALL relevant records, now if i just ticked "Customer" and entered the same number, it would display nothing, unless I ALSO ticked "Booking" and entered a correct booking number that matched a record for that customer in the tables.

I've heard iif statements don't do too well in the criteria fields in the query design builder?? But I know nothing about using SQL really.

For example for the invoices field have used:
IIf([Forms]![frmFindInvoices]![ChkInvNo]=True,[Forms]![frmFindInvoices]![InvNo])

So:
IIf(form checkbox = true, form text box value)

However it seems to think that if the checkbox is not ticked, I want it to only display EMPTY invoicing fields. This is never the case and so no records are shown! If the tick box is not shown, there is not supposed to be criteria for this field, in other words it should allow ANYTHING through.

What I don't know how to do is tell the bloody thing that if the box is NOT ticked, it is NOT to check for anything in this field, because the user does not want to specify any criteria for this field! Things like "Is Null" produce the same results, probably because the query still thinks I want it to find records that have empty fields.

Any suggestions to specify to the query what I want? I really need to get this done! Thanks!

 

[Brianwarnock]

I don't really understand what you are doing with tick boxes, surely you just enter data in the fields that you want to search on and leave the others blank.
Have you read this.

Brian

 

[gemma-the-husky]

the query design is quite simple in its approach. it tests the value of the column you are using by reference to some formula. This can be equal to a value, or between a value, or even equal to or different from another column, or from a field on a form.

But what can't do directly is select it, depending on the setting of another column.

However you can add another column, say called check and set to true or false depending on the setting of other columns, and then pick THAT column by a true or false result.

At the bottom of the form in the criteria row, you can have MULTIPLE rows of criteria which are or'd together.

Therefore if you have 1 check box, you can have a criteria rowselecting where rows the targetfield equals the checkfield, and another row where you select all rows if the checkbox is ticked, by putting the checkbox test in a separate column.

If you have 2 check boxes you need 4 criteria rows, if you have 3 check boxes you need 8 criteria rows, and so on. It can quickly become too comlex for Access.

The other way therefore is to go back to the original design without criterias, and have a single column includefunction(with various fieldparameters), and put true in the criteria column.

You then need a module boolean function which will examine the table/query values, and test them against your form, returning a true or false setting for whether you want to include the row or not.

There are probably other ways to get what you want, but this will work.

 

[EMP]

The IIf() function takes three arguments:
IIf(condition, truepart, falsepart)


You can put each criterion in a new column in the query grid like this (using the correct field name):-
-----------------------------------
Field: IIf([Forms]![frmFindInvoices]![ChkInvNo]=True, [InvoiceFieldName]=[Forms]![frmFindInvoices]![InvNo], True)

Show: uncheck

Criteria: True
-----------------------------------
See Jon K's thread (as linked to in Brian's post above) for explanations.


However, I agree with Brian. The check boxes are unnecessary as you can simply enter data in the fields that you want to search on and leave the others blank (and use Is Null in the criteria as in Jon K's sample.)

^

 

[CrArC]

Thanks a lot people, that's some great info! I looked at the example database in the thread mentioned by Brianwarnock

I don't really understand what you are doing with tick boxes, surely you just enter data in the fields that you want to search on and leave the others blank.
Have you read this.

Brian

And that seems to have done the trick. I might try EMP's advice if I want to reimplement the checkboxes (they're quite useful compared to just clearing a field because it's easier to de-check a box than to select text and hit backspace!), I never knew sticking "true" in the end of the statement was an option! Thanks again!

 

[Brianwarnock]

You have to do what you feel is right for your design, but I always TAB through a form's controls , in which case all of the content is highlighted and deleted with one stroke, good typists, not me I hasten to add, find switching between mouse and keyboard a pain in the ar.. neck.

EMP you may have noticed that I learnt from that previous thread where you trumped my cumbersome suggestion and pointed the poster at Jon's thread, I'm pleased to see that I'm not too old to learn.

Brian

 

[gemma-the-husky]

Yes, but its not easy if you are trying to use optional parmaters from a form as parameters in a query.

ie if in a query you put "[myformfield]", you cannot then easily adapt this to pick every record if the user leaves myformfield blank.

Or can you?

 

[Brianwarnock]

In the example, I also included the using of an IIF expression as the criteria for the calculated Age field. When using an IIF expression as criteria, we can use the word True in the IIF to return all the records when the combo box or text box is left blank

.

The above quote is from Jon's thread, the one I linked to, thus I have to believe the answer to your question is "yes you can "

brian

 

[gemma-the-husky]

Thanks Brian,

i hadn't thought of doing it like that - i'll have a look becuase i will find that construct useful. I was trying to set the comparison in the criteria row, rather than using the field definition.

 

[Brianwarnock]

Hi
Just tried it, all be it with one combo only, and it works a treat.

Brian

PS happy to help you with the amount of help you are giving on this forum I'm amazed you have time for your own work.

 

[evanscamman]

I was stuck on the very same problem. I don't understand why the iif statement can't contain 'NULL' as one of the return values. But I used the statement as shown here and it worked!

Thank you!
Evan

 

[Brianwarnock]

I'm pleased to see that some forum users use the search facility, as you must have done, it saves time and frustration, congrats.

Brian