Iff statements

[Kat]

My report is driven by a query which has a field titled [Amount Paid] and a
check box field to mark if the amount has been paid. My report is seperated
into [Amount Paid] Yes and [Amount Paid] No. In my report I want a total for
the field [Sales] but only when the amount has been paid, meaning the [Amount
Paid] = YES.

I'm thinking it should look something like this:

iif([Amount Paid]="yes", Sum([Sales])

There's a lot more to the report but this is where I'm having trouble and so
I tried to just explain this section. Any ideas? Thanks.

 

[Douglas J. Steele]

If I'm understanding you correctly, try

Sum(IIf([Amount Paid] = "yes", [Sales], 0))

 

[Marshall Barton]

My report is driven by a query which has a field titled [Amount Paid] and a
check box field to mark if the amount has been paid. My report is seperated
into [Amount Paid] Yes and [Amount Paid] No. In my report I want a total for
the field [Sales] but only when the amount has been paid, meaning the [Amount
Paid] = YES.

I'm thinking it should look something like this:

iif([Amount Paid]="yes", Sum([Sales])

I think that should be:

Sum(IIf([Amount Paid], Sales, 0))

 

[Kat]

Thanks that may work but I keep getting an error that says there is an extra
) in the expression. I've tried writing it different ways but can't get
through without an error. Any idea what I'm doing wrong?

If I'm understanding you correctly, try

Sum(IIf([Amount Paid] = "yes", [Sales], 0))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

My report is driven by a query which has a field titled [Amount Paid] and
a
check box field to mark if the amount has been paid. My report is
seperated
into [Amount Paid] Yes and [Amount Paid] No. In my report I want a total
for
the field [Sales] but only when the amount has been paid, meaning the
[Amount
Paid] = YES.

I'm thinking it should look something like this:

iif([Amount Paid]="yes", Sum([Sales])

There's a lot more to the report but this is where I'm having trouble and
so
I tried to just explain this section. Any ideas? Thanks.

 

[Douglas J. Steele]

There's certainly no extra parenthesis in what I typed.

Exagerated for clarity, it's

Sum ( IIf ( [Amount Paid] = "yes", [Sales], 0 ) )

How (and where) are you attempting to use it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks that may work but I keep getting an error that says there is an
extra
) in the expression. I've tried writing it different ways but can't get
through without an error. Any idea what I'm doing wrong?

If I'm understanding you correctly, try

Sum(IIf([Amount Paid] = "yes", [Sales], 0))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

My report is driven by a query which has a field titled [Amount Paid]
and
a
check box field to mark if the amount has been paid. My report is
seperated
into [Amount Paid] Yes and [Amount Paid] No. In my report I want a
total
for
the field [Sales] but only when the amount has been paid, meaning the
[Amount
Paid] = YES.

I'm thinking it should look something like this:

iif([Amount Paid]="yes", Sum([Sales])

There's a lot more to the report but this is where I'm having trouble
and
so
I tried to just explain this section. Any ideas? Thanks.

 

[Kat]

Thanks but there is not an extra parenthesis in what I typed either so I am
not sure why it is not accepting the expression. I have it in the group
footer. I'm not sure what you are asking by "How" I'm attempting to use it.
I need the report to distinquish between paid and not paid, which it does.
Then I need to only add the Sales when they have been paid. I cannot
seperate into different report for other reasons. There should be a simple
expression to do this, I just can't think of it. It should equate to "IIf
[Amount Paid] is yes, then sum [Sales]"

Thanks for you help though. I'll keep trying.

There's certainly no extra parenthesis in what I typed.

Exagerated for clarity, it's

Sum ( IIf ( [Amount Paid] = "yes", [Sales], 0 ) )

How (and where) are you attempting to use it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks that may work but I keep getting an error that says there is an
extra
) in the expression. I've tried writing it different ways but can't get
through without an error. Any idea what I'm doing wrong?

If I'm understanding you correctly, try

Sum(IIf([Amount Paid] = "yes", [Sales], 0))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My report is driven by a query which has a field titled [Amount Paid]
and
a
check box field to mark if the amount has been paid. My report is
seperated
into [Amount Paid] Yes and [Amount Paid] No. In my report I want a
total
for
the field [Sales] but only when the amount has been paid, meaning the
[Amount
Paid] = YES.

I'm thinking it should look something like this:

iif([Amount Paid]="yes", Sum([Sales])

There's a lot more to the report but this is where I'm having trouble
and
so
I tried to just explain this section. Any ideas? Thanks.

 

[Kat]

Thanks but this didn't work either.

My report is driven by a query which has a field titled [Amount Paid] and a
check box field to mark if the amount has been paid. My report is seperated
into [Amount Paid] Yes and [Amount Paid] No. In my report I want a total for
the field [Sales] but only when the amount has been paid, meaning the [Amount
Paid] = YES.

I'm thinking it should look something like this:

iif([Amount Paid]="yes", Sum([Sales])

I think that should be:

Sum(IIf([Amount Paid], Sales, 0))

 

[Kat]

Got it. This is what I finally figured out:

=Sum(IIF([Amount Paid]=-1,[Sales]))

Thanks for your help!!

Thanks but this didn't work either.

My report is driven by a query which has a field titled [Amount Paid] and a
check box field to mark if the amount has been paid. My report is seperated
into [Amount Paid] Yes and [Amount Paid] No. In my report I want a total for
the field [Sales] but only when the amount has been paid, meaning the [Amount
Paid] = YES.

I'm thinking it should look something like this:

iif([Amount Paid]="yes", Sum([Sales])

I think that should be:

Sum(IIf([Amount Paid], Sales, 0))