IIF STATEMENTS IN REPORTS

[JWJ]

I HAVE AN APPLICATION THAT REQUIRES THE USE OF 7 DIFFERENT CATEGORIES OF RECORDS - I WOULD LIKE TO SUMMARIZE THE 7 CATEGORIES ON THE BOTTOM OF THE REPORT. WOULD AN IFF STATEMENT SIMULAR TO THE FOLLOWING DO THE JOB? IF NOT, IS THERE ANY WAY I CAN ACCOMPLISH THIS? ALL SUGGESTIONS WOULD BE APPRECIATED.

" IIF([CATEGORY]= "1",SUM([FIELD A),0) "

" IIF([CATEGORY]= "2",SUM([FIELD B),0) "

JWJ
jozwiakj@earthlink.net
(630) 682-9878

 

[petermeachem]

you mean in the footer? i dont think so if you consider what access is up to when it gets to the footer. you will have to create a recordset using something like the contents of your iif's. in fact you could just do 1 recordset and group by category.

note the amusing use of lower case throughout. Peter Meachem
peter@accuflight.com

 

[Aivars]

Hi!

You may define terms for each case.
If you use VBA for formating your report you can use Select Case or If...ElseIf...End If instructions.
If you would like to use function IIF you may put in this function in another one.

In your case:
1)
select case CATEGORY
case 1
MyValue = SUM([FIELD A)
case 2
MyValue = SUM([FIELD B)
case 3
MyValue = SUM([FIELD C)
case 4
MyValue = SUM([FIELD D)
case 5
MyValue = SUM([FIELD E)
case 6
MyValue = SUM([FIELD F)
case 7
MyValue = SUM([FIELD G)
case else
MyValue = 0
End Select

2)
If CATEGORY = 1 Then
MyValue = SUM([FIELD A)
ElseIf CATEGORY = 2 Then
MyValue = SUM([FIELD B)
ElseIf CATEGORY = 3 Then
MyValue = SUM([FIELD C)
ElseIf CATEGORY = 4 Then
MyValue = SUM([FIELD D)
ElseIf CATEGORY = 5 Then
MyValue = SUM([FIELD E)
ElseIf CATEGORY = 6 Then
MyValue = SUM([FIELD F)
ElseIf CATEGORY = 7 Then
MyValue = SUM([FIELD G)
else
MyValue = 0
End If

3)
MyValue = IIF([CATEGORY]= "1",SUM([FIELD A),IIF([CATEGORY]= "2",SUM([FIELD B),IIF([CATEGORY]= "3",SUM([FIELD C),IIF([CATEGORY]= "4",SUM([FIELD D),IIF([CATEGORY]= "5",SUM([FIELD E),IIF([CATEGORY]= "6",SUM([FIELD F),IIF([CATEGORY]= "7",SUM([FIELD G),0)))))))

Aivars

 

[petermeachem]

I was assuming the table was

category fielda fieldb etc
1 100 101
2 200 10
3 200 11
4 300 121
2 400 1441
2 500 101
2 600 1551

in which case, that wouldn't work would it? Peter Meachem
peter@accuflight.com