Code Help. IIFs where best to put them

[mvital]

Hi All,

I have a form where several filter options are presented to the user to filter a report (Access 2003). I have two listboxes that depending on the combination a calculation is run. Right now, I have the calculation being performed in a query, but now we have added other elements to it and it is a little more difficult.

If the user selects the combination of AHSO, HSOIN, OR HIGCA with LETS then the calculation is result*duration/15

If the user selects AHSO and LEP OR HIGCA and LVT the calculation is result*duration/480

However, if the user selects HSOIN and LER the calculation is result*duration/600

I hope it makes sense. Would this be better to put in a VBA function? Not sure. How would I start it and would it be a calculated control on the report?

thanks in advance,

MV

 

[mvital]

More info..Here is what the if...then would look like:

if [source] AHSO or HSOIN or HIGCA
and [limittype] LES
result*duration/15

if [source] AHSO
and [limittype] LEP
result*duration/480

if [source] HIGCA
and [limittype] VLT
result*duration/480

if [source] HSOIN
and [limittype] LER
result*duration/600

thanks!

 

[Remou]

I think the best bet is a Select Case statement called from the After Update event of each of the boxes, unless you expect these options to change. If changes are likely, a table of possibilities may be a better option.

 

[mvital]

Thank you Remou. I am not too familiar with the VBA Select Case syntax. So, I posted in the queries forum the IIf statements I tested in a query. I am getting too few arguments message.

thanks!

 

[Remou]

What are these statement?

 

[mvital]

IIf([source]="ACGIH" Or "NIOSH" Or "OSHA" And [limit_type]="STEL",CDbl(Nz([RESULT]*[TIME_MINUTES]/IIf([TIME_MINUTES]<=15,15),0)


IIf([source]="ACGIH" Or "OSHA" And [LIMIT_TYPE]="TLV" Or "PEL",CDbl(nz([result]*[time_minutes]/480)))


IIf([source]= "NIOSH" AND [LIMIT_TYPE]= "REL", CDbl(nz([result]*[time_minutes]/600)))

IIf([source]= "NIOSH" Or "OSHA" or "ACGIH" And [LIMIT_TYPE] = "CEILING", [RESULT])

 

[Remou]

Your second thread seems more active. Let's drop this one.
This thread continues: Help with IIF statements (nested)
thread701-1301676