Query criteria problem (1 Viewer)

[Chintsapete]

Hi all

I have a problem with query criteria which really doesn't make sense.
I have table with column PayPeriodId, 1-4, column PayPeriod 1= Daily, 2= Weekly, 3= Forthnightly, 4= Monthly
On a popup form I have ComboBox where I look up this values.

I want to use this ComboBox values in query as criteria, so it applies a filter. If I do forthnightly it selects only those employees and if I do monthly I want to have the forthnightly and monthly employees.

So below criteria works as such to select either or, but is not what I want.

IIf([Forms]![SalaryPrepPopup]![Payperiod]=3,3,IIf([Forms]![SalaryPrepPopup]![Payperiod]=4,4))

I tired the below, but all not working

IIf([Forms]![SalaryPrepPopup]![Payperiod]=3,3,IIf([Forms]![SalaryPrepPopup]![Payperiod]=4,>=3))

IIf([Forms]![SalaryPrepPopup]![Payperiod]=3,3,IIf([Forms]![SalaryPrepPopup]![Payperiod]=4,Is Null))

IIf([Forms]![SalaryPrepPopup]![Payperiod]=3,3,IIf([Forms]![SalaryPrepPopup]![Payperiod]=4,""))

IIf([Forms]![SalaryPrepPopup]![Payperiod]=3,3,IIf([Forms]![SalaryPrepPopup]![Payperiod]=4,3 And 4))

Anybody has any idea why? Sure doesn't make sense to me

Thanks

 

[Ranman256]

You don't need ANY IIF statements,
The query will look at the cboBox....
Select * from table where PayPd= forms!myForm!cboPayPd

You have a table with the payPd values,
1,daily
2, weekly
Etc

The cboBox will have 2columns,in it too from that table,but only 1 needs to be visible to the user. (The text is visible,the numeric not)
Set the cbo col widths: 0;1
And set the bound col =1

The user picks 'Weekly',but cbo value gets 2.

 

[Chintsapete]

Hi RanMan

thanks for the quick response. I have the cboBox set as you describe above.
I put the IIF statement in because of the table where I filter from the PayPeriod is either 1, 2, 3 or 4. But every second week I do basically the monthly and fortnightly wages together, so I would like to have them both together listed, but when I do fortnightly only then I would like to hide the monthly dudes.
With your solution it shows me either the monthly or the fortnightly only.

 

[Ranman256]

then what is the purpose of the combo box?
you can still pull data with many differnent PayPds w/o IIF.

 

[Chintsapete]

The purpose of the combo is to select between fortnightly and monthly.
Half the staff are fortnightly and the other monthly. When the fortnightly wages are due then it would hide all the guys are on monthly wages, eliminates errors for people don't do it all the time and when the monthly are due then it would show all of the guys.
I would like to go further even that I can select weekly for seasonal workers when I have to pay them on a weekly base.
I hope that makes sense.