conditional calculations in a field

[Guest]

hi, im building a student housing databse which holds information about
tenants and have a query which is used as the source of a mailmerge document
and includes the following field:

Pay1:
nz(IIf(students!duration=42,(students!weeks1*studentrooms![06weeklyrent])+(payoption!FuelRatioWeek1*rentaltarrif.[42wkadditionalcost]),(students!weeks1*studentrooms![06weeklyrent])+(payoption!FuelRatioWeek1*rentaltarrif.[52wkadditionalcost])),0)

which more or less says that if the duration of the students tenancy is 42
then do a calculation, otherwise it defaults to a 52 week tenancy, i need to
add a calculation for a 26week tenancy in this field too so that in the mail
merge it can look at the duration and figure out what we should be charging
our tenants. on its own this would be calculated as

(students!weeks1*studentrooms![06weeklyrent])+(payoption!fuelRatioWeek1
*rentaltarrif.[26wkadditionalcost])

how can i do this?

with much thanks for your help,

Amit

 

[John Vinson]

hi, im building a student housing databse which holds information about
tenants and have a query which is used as the source of a mailmerge document
and includes the following field:

Pay1:
nz(IIf(students!duration=42,(students!weeks1*studentrooms![06weeklyrent])+(payoption!FuelRatioWeek1*rentaltarrif.[42wkadditionalcost]),(students!weeks1*studentrooms![06weeklyrent])+(payoption!FuelRatioWeek1*rentaltarrif.[52wkadditionalcost])),0)

which more or less says that if the duration of the students tenancy is 42
then do a calculation, otherwise it defaults to a 52 week tenancy, i need to
add a calculation for a 26week tenancy in this field too so that in the mail
merge it can look at the duration and figure out what we should be charging
our tenants. on its own this would be calculated as

(students!weeks1*studentrooms![06weeklyrent])+(payoption!fuelRatioWeek1
*rentaltarrif.[26wkadditionalcost])

The Switch() function may be simpler than a complex nested IIF. It
takes arguments in pairs: if the first member of a pair is true it
returns the second member of that pair and quits. E.g. you could use

Switch(Students.Duration = 26, <some expression>,
Students.Duration = 42, <another expression>,
Students.Duration = 52, <yet another expression>,
True, 0) <<<< to return 0 if none of the expressions is true


John W. Vinson[MVP]