iif statement

Hi there,

I have a small problem in the iif statement of a query.

Iif([officer]="a","good",[officer]) simple condition and it works.

iif instead of one officer as "a" we have a, b, c, d, e, f, g. How do i give the condition which would work as the first condition.

iif([officer]="a""b""c""d""e""f""g","good",[officer]) does not work. I know its wrong, can any one please give me the correct condition/statement.

Thanks for your help.

Raj

Raj,

Try something like this

IIf([officer]>= "a" Or [officer]<="g","good",[officer])

Didn't test, so just try for now and post back if that didn't work.
lwells

Iwells,

I am sorry, I was not clear. I just used a,b,c,d etc as an example. Infact the data consists of names of the officers (that are not in any sequence) but i want to group a set of officers from a list using a query under a separate department using the iif statement/or condition and then total the amount for that dept.

Thank you

Raj

Sorry Raj,

Still not clear of what you want then. The IIf statement looks for a name of officers and then returns "good" as the True part, and the the name of the "officer" if False, doesn't sound like a logical criteria to be put under the field name for officers for the filter. I understand about grouping officers together to get a sum, but why are you using the IIf statement. Your criteria under the field listing the officers name would simply be "Officer1" Or "Officer2" Or "Officer3" etc. using the names that are actually in the field. Better yet, if all the officers belong to the same department, shouldn't you just sort by the department?

lwells

Iwell,

Got your point. But to give you the idea behind it. we have around 12 loan officers out of which 6 are of our dept and the rest are from other dept. But even they some time chip in loans specific to our dept example consumer loan.

I have the data base..when i make cross tab query we are more concerned with our officers performance and want to pool all other dept officers loans in to one pool. We do not have segregation of dept as pointed out by you. So, my logic was in the query which has the fields like

name of the borrower
loan amount
date
officer

If i can give the iif criteria or any criteria you can think of, to have detail listing by name for our dept officers and MSC across the names of other dept officers there by cross tab query would reflect MSC for all other officers loans.

Hope that make sense.

Any other suggestion is welcome. We can not amend the database/form structure. We have to think only from query point of view.

Thank you

Raj

Hi Raj,

Okay, makes sense now. What you are wanting to do was to is create an expression (I thought you were trying to create a sort criteria) that will identify the officer name and if they belong to your department show them with their actual name, but if they belong to another department show them as MSC. That way in your cross tab you have the individual names and then the rest as MSC. Try this for your IIf statement and see if your cross tab will work like you want:

IIf([officer]="Name1" Or [officer]="Name2" Or [officer]="Name3","MSC",[officer])

In the above if Name1 for example didn't belong to your department it would be given the True of MSC.

It is too bad you couldn't make a change to the table and assign the officers to a department...would sure make things easier for you.

lwells

Thank you very much, Iwell. It helped.