help with iif statement or Choose statement

[alfred67]

Hello

Need help with an iif statement or maybe a Choose statement in a Query or what ever works

I have the following data

There are 12 pay periods Jan to Dec now some pay period may get a bonus (see below)

Pays
Jan--$1200,feb--$3000,mar --$2500,apr-$2900,may--$6700,june-$2300,july--$5000, aug--$6100,sept--$9000,oct--$2400, nov--8900,dec$4600

Bonus:
June --$100,Sep---$300

Looking the following results
Jan--$1200,feb--$3000,mar --$2500,apr-$2900,may--$6700,june-$2400,july--$5100, aug--$6200,sept--$9300,oct--$2700, nov--9200,dec$4900
Need the bonus to apply from June to Aug but then apply the Sep bonus only from Sept to Dec

Please keep in mine the bonus months will change(based on the user input)

Thanks

Alfred67

 

[vbaInet]

It depends on how the Date is stored? Are they just three letter words as a text string or an actual date where the month is extracted from?

 

[alfred67]

Hello

The date is stored as a Medium Date --13-Oct-10 (d/m/yr)

Alfred67

 

[vbaInet]

So, something like:

IIF(Month([DateField]) In (6,7,8), [SalaryField] + 100, IIF(Month([DateField]) > 8, [SalaryField] + 300, [SalaryField]))

 

[alfred67]

Hello

Thanks for the relpy

The code works great accept the the bonus month could change from year to year does this mean I would have to rewrite the code too

Alfred67

 

[vbaInet]

If you create a table with all the month numbers, 1 to 12 (Number field), with another field with the bonus amount. The month numbers will be the Primary Key and ID for this table.

In the query create an alias field to get the month number of the Date field, like:
DateMonth: Month([DateField])

You can then join that DateMonth field to the ID field from the new table. So you can then add whatever the bonus field returns to the Salary field.

Did you get it?

 

[alfred67]

Wow!! Great thinking!!

I believe, I understanding what your saying. Going to give it a try

alfred67