If query

  • Thread starter Thread starter Faio
  • Start date Start date
F

Faio

I have created a query based on a crosstab query. Its
something like this.

PF, SickTot, SickBalance, CompTot, CompBalance, Status.

The SickBalance and CompBalance formula is like this
SickBalance=(10-[SickTot]) and CompBalance=(5-[CompTot]).
These formulae are working very well but I was then given
another problem and if anyone can help, I'll appreciate.

Problem. The fixed values above like 10 and 5 needs to be
changed depending on the [Status]. So I need something
like, If Status = Permanent then the value is 10, If
Status = Temporary the value is 5 else 3 if Contract.

Thanks in advance.
 
Perhaps something like this:

SickBalance=(IIf([Status]="Permanent",10,IIf([Status]="Temporary",5,3))-[Sic
kTot]) and
CompBalance=(IIf([Status]="Permanent",10,IIf([Status]="Temporary",5,3))-[Com
pTot]).
 
Back
Top