Criteria and Queries IIf... (1 Viewer)

[lrAsIgo]

Any help would be greatly appreciated!

The following IIF statement almost works as I would like it to. However, it only filters the records which have data contained in the particular field For instance, (Criteria: "*"). I need it to show all records, for instance, (Criteria: Like "*" Or Is Null ), If the condition is false.

So, how could I include something like this [Like "*" Or Is Null] into the falsepart of anIIF statement?

Here is the IIF statement in question:

Like IIf([Forms]![QueryRunnerHWForm]![Combo7] Is Not Null Or ([Forms]![QueryRunnerHWForm]![Combo7])<>"",([Forms]![QueryRunnerHWForm]![Combo7]),"*")

To further explain: I have a form with a combo box with the name combo7. When the user selects from the combo box the subquery is filtered accordingly. However, if the combo box is blank or null the subquery display only record which actually contain data, and does not show records which do not contain data. I hope this is clear. Thanks in advance for the help.

[This message has been edited by lrAsIgo (edited 06-06-2002).]

 

[RichMorrison]

You posted in the "Queries" section so I assume you are talking about criteria in a query.

You can enter 2 or more conditions in succesive rows in the "Criteria" boxes under a field. The conditions are treated as an "Or" combination.

You can enter
Like YourForm![YourControl]&"*"
in the first row and
Is Null
in the second row.

RichM

 

[lrAsIgo]

Thanks for the reply, RichMorrison.
Yes, I am talking about criteria in queries.

OK this works. The same as if the criteria were just.. Like "*" Or Is Null
on the first row

However, How can I get all records which

are Like "*" or Is Null...
when Like YourForm![YourControl] Is Null

In other words, rather than just have "*" returned, I would like to have "*" Or Is Null returned when the condition is true based on the expression below.

This idea does not work in the following IIF statement.

Like IIf([Forms]![QueryRunnerHWForm]![Combo7] Is Null Or ([Forms]![QueryRunnerHWForm]![Combo7])="","*" or Is Null,[Forms]![QueryRunnerHWForm]![Combo7])

Thanks again for all help!

 

[RichMorrison]

you wrote
<<
However, How can I get all records which

are Like "*" or Is Null...
when Like YourForm![YourControl] Is Null
>>

I don't think so. For that you want:
Like YourForm![YourControl]&"*" or Is Null

So when YourControl is in fact Null, the value resolves to "*".

This can give incorrect results if the control value is 10, for example, and you get records with 100, 101, etc.

The best solution is to do something in the form to replace the control value with "*" when the control is Null. Do this before the query is run.

RichM

 

[lrAsIgo]

First, I really appreciate your help.

You wrote:
So when YourControl is in fact Null, the value resolves to "*".

The desired resutl is... So when YourControl is in fact Null, the value resolves to "*" or Is Null.

In other words, All records resolved. not just "*" records, but "*" or Is Null or "".

Second, I may be unclear as to my intentions and hopefully this post will clarify my problem and sought after solution. Sense, I obviously have not solved/figured the problem yet.

In the form if the users selects from a combo box, say, [Combo0], the query will filter on the selection. For instance: If the user selects Information Technologies then all record which have Information Technologies in the perspective field in which the criteria is based (i.e.the expression below are present) will be returned/resolved. If the user then removes the selection then the combobox[combo0] will be "" Or Null then, the query should return/resolve all records (i.e. "*" Or Is Null).

When the following examples are tested. (Expression placed in Criteria in query for perspective field as fore mentioned)

This expression does not work? Based on your post I tried this expression, first, which include your solution

Like IIf([Forms]![TestForm]![Combo0] Is Null Or ([Forms]![TestForm]![Combo0])="",Like [Forms]![TestForm]![Combo0] & "*" Or Is Null,[Forms]![TestForm]![Combo0])

This expression works but does not produce the desire results
- because it does not utilize the combobox in the form, in which a user will select what is to be filtered. For instance: If the user selected Information technologies. Obviously nothing will happen when the combobox- afterupdate- testsubquery.requery me.refresh is run.

Like [Forms]![TestForm]![Combo0] & "*" Or Is Null


This expression is snytactically correct but does not produce the desired results. It only returns/resolve records in which data is contained i.e. "*"

Like IIf([Forms]![TestForm]![Combo0] Is Null Or ([Forms]![TestForm]![Combo0])="","*",[Forms]![TestForm]![Combo0])


These expression do not work at all?
Like IIf([Forms]![TestForm]![Combo0] Is Null Or ([Forms]![TestForm]![Combo0])="",Is Null,[Forms]![TestForm]![Combo0])

Like IIf([Forms]![TestForm]![Combo0] Is Null Or ([Forms]![TestForm]![Combo0])="",([Group].[Group]Is Null),[Forms]![TestForm]![Combo0])

 

[RV]

If I understand your topic correctly,
you want to retrieve specified rows (in case a selection is made in your combobox) or retrieve all records (in case no selection is made)
You don't need the IIF function at all!

Use this WHERE clause:

WHERE YourColumnName LIKE "*"&[Forms]![TestForm]![Combo0]&"*"
OR [Forms]![TestForm]![Combo0] Is Null;

>If the user then removes the selection then the combobox[combo0] will be "" Or Null<

As far as I know, a ComboBox Field is always Null when there is no selection made (when a selection is removed there is also no selection made.....)
So you needn't don't bother about ""...

 

[RichMorrison]

you wrote
<<
Like [Forms]![TestForm]![Combo0] & "*" Or Is Null

This expression is snytactically correct but does not produce the desired results. It only returns/resolve records in which data is contained i.e. "*"
>>

Well, it works for me.
I use Access 2.0 and Access 97 under Windows 95 and Windows NT 4.0

The field in the query must be "text" to do this. Are you using this criteria for a text field in a table ?

If the control on the form is Null, I get all records.

If the control contains "Se", I get all all records in the table with Null plus all records that contain 'Seattle', 'Selma', etc.

HTH,
RichM

 

[lrAsIgo]

RV - this works, and gives me some ideas. There is a problem. I have several combo boxes in my form. If I add this multiple times (for each combo box) it does not work and also becomes a bit compliated (at least for me)in writing out the sql but I giving it a try and have made some progress?

RichM- I am using Access XP. Also, as mentioned above I have several comboboxes and some are text, some are number.


Like [Forms]![TestForm]![Combo0] & "*" Or Is Null

works when included in the criteria alone. But should it also work in an IIF statement?

Again, thanks to all.

Please continue with the ideas and helpful suggestions!

Ideally - if my combo is null, then show all records, otherwise filter on the selection.