IIF statement with and/or (1 Viewer)

I have the following situation

Select query with tons of formulas and calculations goes sluggish
I started some optimization, and I am trying to remove as much calculated fields as I can

One field of the query contains info about personal ID code. The first character in the code comes from gender> 1,3,5 for male and 2,4,6 for women
The form that calls the query provides info about gender in a Gender field
If gender is not selected, parameter is 0
If male > 1
If female > 2

For 0 condition, all records should be dispalyed
For 1, only codes that starts with 1,3 or 5
For 2, only records that starts with 2,4,6

I can easily do it using an Expression field, but I would like to simplify the query and remove the calculated field and use a filterin formula in the ID field [CNP]

I tried this, but it doesn't work

Like IIf([Forms]![Cautare_Membru_Frm].[Gender]=0,"*",
IIf([Forms]![Cautare_Membru_Frm].[Gender]=1,("1" & "*") Or ("3" & "*") Or ("5" & "*"),("2" & "*") Or ("4" & "*") Or ("6" & "*"))

What am I doing wrong here?

Cross posted here: http://www.accessforums.net/showthread.php?t=62119

Romio68 said:

What am I doing wrong here?

Click to expand...

It's not wrong but an expression like "1" & "*" can be simplified. It's pointless to concatenate literals. Just write "1*" to start with then the expression


is the same as


but that results in a type mismatch. You can't Or strings. But even if you changed it to


You'd be trying to produce something like

and you can't do that either what you really need is

but you cannot use IIF to return something like that (Edit: See next post). In the attached database you can find a query (qryTest) that has criteria that does what you are trying to do. The SQL in this query is:


You will probably understand this better if you open the database and look at this query in design view.

Please let us know if changing this helps the performance of the query.

In my previous post I asserted that you could not return something like


with IIF. You can however return something like


So the expression

IIF([Forms]![Cautare_Membru_Frm].[Gender]=0, PersonalID Like "*" , IIF([Forms]![Cautare_Membru_Frm].[Gender]=1, PersonalID Like "1*" Or PersonalID Like "3*" Or PersonalID Like "5*" ,PersonalID Like "2*" Or PersonalID Like "4*" Or PersonalID Like "6*"))

works too and is demonstrated in the attached database.

My workaround was this
I created an expression filed in the query
Sex: IIf(Left([Membri_Partid]![CNP],1) Mod 2=0,2,1)
and filtered it with
Like IIf([Forms]![Cautare_Membru_Frm].[Gender]=0,"*",[Forms]![Cautare_Membru_Frm].[Gender])

Nice and smooth, without those tons of IIf's I am trying now.

[Membri_Partid] is the table and [CNP] is the PersonalID code

Witch way is faster, since my problem is a time consuming query that runs on over 100k records.
I thought that if i will reduce the calculated fields will speed up the process, therefore i tried to filter directly the [CNP] field.

Your code does create a new field? Is it faster than my approach?
(and by the way, if I click on the formula on the field in query design mode, it automatically add the "Expr1:" label before the formula)