IIf statment in query

[wdenboer]

Hi all,

I have the following problem.

In the expression builder I have some IIf code for a filter criteria.

When the value of an option group "OG_status" is equal to 1, there should be no filter applied.
If the value of OG_status is unequal to 1 the criteria should be the value in the field "Sel_st".

The code used for the criteria is as follows:

IIf([Forms]![FST_main]![OG_status]=1;Null;[Forms]![FST_main]![Sel_st])

With this approach the filter does not function at all.

A little bit of help would be highly appreciated.

Kind regards

 

[KenHigg]

IIf([Forms]![FST_main]![OG_status]=1,"*",[Forms]![FST_main]![Sel_st])

??

 

[wdenboer]

Hi KenHigg,

Thanks a lot. I couldn't find the statement for an empty query criteria. I thought that only "" would do. But apparently * was needed.

I have given it a few tries, but it doesn't work.

The filter returns no records at all. But if I put only "*" as a criteria it returns all records as it should.

Can the same be achieved is a different way.

Kind regards Wouter

 

[Ron_dK]

You could try changing it to :

IIf([Forms]![FST_main]![OG_status]=1,"0",[Forms]![FST_main]![Sel_st])


Hth

 

[wdenboer]

Hi Ron,

It does not work. In this way it uses 0 as a criteria and I need an empty criteria.

thanks anyway

 

[Ron_dK]

OK, what if you try this :

IIf([Forms]![FST_main]![OG_status]=1,true,[Forms]![FST_main]![Sel_st])

 

[wdenboer]

Is not working. access in not accepting such an expression. It returns that the expression is too complex.

Is there an other solution, or different approach. Maybe with VBA?

thanks

 

[DCrake]

What you need is the Like Operator in your statement

IIF(Condition = 1,"Like '*'",Condition)

 

[wdenboer]

If have used this code now:

IIf([Forms]![FST_main]![OG_status]=1;"Like '*'";[Forms]![FST_main]![Sel_st])

but it still returns nothing when OG_status=1. All other conditions do work as they should.
Somehow it seems impossible to get an empty criteria for the TRUE situation.

 

[DCrake]

Slight Modification

IIf([Forms]![FST_main]![OG_status]=1;"Like '*' Or Is Null";[Forms]![FST_main]![Sel_st])

David

 

[wdenboer]

No result again.

I think I will try to change over to coding the criteria in VBA. I might need some help with that, but will come back on that.

thanks anyway

 

[Brianwarnock]

The Where clause should be
Where ((([Forms]![FST_main]![OG_status]=1)=True) or [filteredfield]=[Forms]![FST_main]![Sel_st])


Brian