Conditional criteria

[Bioman]

Hi!

I have to put a conditional criteria in a query. If the value of a listbox in a form is "My value" then don't put criteria, else the criteria must be :like "listbox value".

How may I do that?
Thanks!

 

[Rob.Mills]

iif(Forms!FormName!ListBoxName="My Value","",Forms!FormName!ListBoxName)

 

[Bioman]

oups... problem

Thanks, but...

It work fine if Forms!FormName!ListBoxName <> "My value". But when Forms!FormName!ListBoxName="My value", the query select nothing. I have an empty result. Instead of "", I tried "*", like "*", like '*', not null, "not null" but any seems to do the job! Suggestion ?

 

[Rob.Mills]

Man, you stumped me! Not that that's hard. But I don't know how to accomplish that. I know how you can set it up so that if a textbox or listbox is null then show all records but that's not what you're trying to accomplish. Sorry about that. Let me know if you find out, I'd like to know the answer to this one.

 

[Bioman]

What is your set up to show all records when a listbox is null? Could I try to replace the "My value" string by a null item?

 

[Rob.Mills]

If you have nothing selected in the listbox then the value will be null.

Then in the query setup the field this way:

Criteria line 1: [Forms]![FormName]![ListBoxName]
Criteria line 2: [Forms]![FormName]![ListBoxName] is null

It will reformat it for you when you close it but it will still work fine.

 

[Jon K]

Try this criteria in the query grid (replacing with the correct field name):-

IIf(Forms!FormName!ListBoxName="My Value", [FieldName], Forms!FormName!ListBoxName)

[FieldName] in the iif() will return every record except records with Null values in the field.


If you need to return also records with Null value in the field, set the column in the query grid as follows:-

Field: IIf(Forms!FormName!ListBoxName="My Value", True, [FieldName]=Forms!FormName!ListBoxName)
Show: uncheck
Criteria: <>False

True in the iif() will return every record including Null values in the field.

Hope this helps.

 

[Rob.Mills]

Thanks Jon. I was wondering how to solve this problem.

 

[Bioman]

Is it normal that it pop me a prompt box to enter a value for FieldName instead of return records?

 

[Bioman]

ok, it pop me because the [FieldName] is an alias
Year:Year([Activity].[DateEnd])

When I use Year([Activity].[DateEnd]) instead of Year, it work fine! Thanks!!!