Query Help or SQL Help not really sure...I'm Lost

[Sanguivore]

I am not sure if this should be written in SQL, or in the query or in the table. First time access database builder.

In my table[App&Rate], I have two fields I need to get my result. The first field is [Large/Small] and the second is [#Employ].

I have three different comparisions or cases where I need the result to be place in a field[AdminFee] on my query table for a report.

These are my cases:

CASE 1
IF [App&Rate]![Large/Small] = "Large Group" THEN [AdminFee] = 0

CASE 2
IF [App&Rate]![Large/Small] = "Small Group" AND (2.50*[App&Rate]![#Employ])>= 15 THEN [AdminFee] = 15

CASE 3
IF [App&Rate]![Large/Small] = "Small Group" AND (2.50*[App&Rate]![#Employ])< 15 THEN [AdminFee] = (2.50*[App&Rate]![#Employ])

I've tryed placing this in SQL and in the expression builder and I've gotten no where....I'm lost can someone please guide me.


Fred

 

[Liv Manto]

Use the iif function

First condition:
if a=x then g= 15

Second Condition:
if a=y then g = 20 else g= 60

Syntax: iif(condition, true, false)

Double IIF:

GValue: IIF ( a=x, g=15, iif(a=y, g=20, g=60)

 

[Sanguivore]

Thanks for the insight!!!!!

This is my final expression that works perfect.

AdminFee: IIf([App&Rate]![Large/Small]="Small Group",IIf((2.5*[App&Rate]![#Employ])>=15,15,(2.5*[App&Rate]![#Employ])),0)


Fred