IIF Statement and Groups

[razorkat99]

Well, they scrapped the whole other form and went a somewhat different direction, but still an issue at hand for my IIf statement calculating correctly.

*I have two Account Groups: MHTL and MHTLCM
*I need to count the number of installs and subtract the number of disconnects.
*The following statement in my form footer gives me a -1 even though there are 2 installs under MHTL and 1 disconnect under MHTLCM. Theoretically I would expect my total to be 1 (2 installs - 1 disconnect = 1).

=IIf([Service_Type]="Install",1,IIf([Service_Type]="Disconnect",-1,0))

That's my initial dilemma. The second one is, what if I want to only count the number of installs/disconnects for Account Group MHTLCM? I tried adding =IIf([Account_Group]="MHTLCM" AND [Service_Type]... in my statement, but obviously I don't have it set up correctly as it didn't work.

Thanks.

 

[pbaldy]

It's probably just looking at 1 record. Try

=Sum(IIf([Service_Type]="Install",1,IIf([Service_Type]="Disconnect",-1,0)))

Your proposed method for the second would probably work. Post the whole thing and we'll figure out what's wrong.

 

[razorkat99]

Soooooooooo close.

Expression:
=Sum(IIf([Service_Type]="Install",1,IIf([Service_Type]="Disconnect",-1,0)))
works correctly by bringing back 0 from both Account Groups (3 Installs - 3 Disconnects=0)

Expression:
=Sum(IIf([Account_Group]="MHTLCM" And [Service_Type]="Install",1,IIf([Service_Type]="Disconnect",-1,0)))
Incorrect as it brings back -2. S/b (1 Install - 2 Disconnects = -1). It appears that only the Disconnects are being calculated in this statement.

 

[pbaldy]

I don't see anything wrong with the formula right off, so I'd wonder if the data matches up. Can you post a sample db?

 

[razorkat99]

IIF statement with several parameters

Here is the db with the report.... I almost had it, then I had ot add another parameter of <> "POTS*" to count anything not containing the word POTS.

 

[pbaldy]

I think you want

Not Like "POTS*"

 

[razorkat99]

I think I tried that, but will try it again. After breaking the code down I still couldn't get it to calculate correctly even without the POTS parameter. I'm going to work on it this weekend and see if I can figure it out. Thanks!

 

[pbaldy]

I got it to work that way. I will point out what might be a logical flaw. You have all the extra criteria in the first part of the formula, but not in the second. Therefore, ALL disconnects will be subtracted, regardless of account group, etc. If you want an apples to apples comparison, you'd have to add the same criteria to the second IIf.

 

[razorkat99]

Thank you Paul! I ended up with the following and it calculates correctly:

=Sum(IIf([Account_Group]="MHTL" And [Equipment_Type] Not Like "POTS*" And [Service_Type]="Install",1,(IIf([Account_Group]="MHTL" And [Equipment_Type] Not Like "POTS*" And [Service_Type]="Disconnect",-1,0))))

 

[pbaldy]

Glad we got it sorted out for you.