Multiple IIf's....

[Guest]

I know there is a ton of information out there about writing multiple IIf
statements, but I cannot write this statement correctly:

=Sum(IIf([ISSUEDTIME] Between #7:01:00 AM# And #3:00:00
PM#,((IIf([TRANSTYPE]="IC",[EXTENDEDCOST])-(IIf([TRANSTYPE="IR",[EXTENDEDCOST])

This is an IIf statement that I am writing within the Control Source of a
text box on a form. Basically, there are three fields in this statement. I
am trying to say that if the following criteria are met, to display a summed
total.

Criteria 1: The issued time needs to be between 7:01 AM and 3:00 PM.
Criteria 2: Take the summed value of the extended costs for each record that
has an IC and subtract them from the summed extended cost of all records that
have an IR in the TRANSTYPE field. Please, somebody help me. Thanks in
advance

 

[Guest]

General convention for nested iif statements:

 

[John Vinson]

I know there is a ton of information out there about writing multiple IIf
statements, but I cannot write this statement correctly:

=Sum(IIf([ISSUEDTIME] Between #7:01:00 AM# And #3:00:00
PM#,((IIf([TRANSTYPE]="IC",[EXTENDEDCOST])-(IIf([TRANSTYPE="IR",[EXTENDEDCOST])

This is an IIf statement that I am writing within the Control Source of a
text box on a form. Basically, there are three fields in this statement. I
am trying to say that if the following criteria are met, to display a summed
total.

Criteria 1: The issued time needs to be between 7:01 AM and 3:00 PM.
Criteria 2: Take the summed value of the extended costs for each record that
has an IC and subtract them from the summed extended cost of all records that
have an IR in the TRANSTYPE field. Please, somebody help me. Thanks in
advance

Your IIF statements have a True branch - the inner IIF - but no False
branch. IIF should have three arguments: yours have just two.

What do you want to see if the ISSUEDTIME is outside the working
hours?

John W. Vinson[MVP]


[

 

[Guest]

You don't have a "false" statement in any of your iif statements. I have
used zero below as the "false portion of the statement.

=Sum(IIf([ISSUEDTIME] Between #7:01:00 AM# And #3:00:00
PM#,IIf([TRANSTYPE]="IC",[EXTENDEDCOST],0)-IIf([TRANSTYPE="IR",[EXTENDEDCOST],0),0))


Hope it works for you,
RW