Syntax help summing part of a txtbox

[Gaspare]

Hi,
I am attempting to sum part of the entries in the detail section
of the report in a text box in the group header.
Txtbox1 is text, txtbox2 is a number.
This is the control source in the text box in the header.

=IIf([txtbox1]="text1" Or "text2" Or "text3",Sum([text2]),0)

I am getting a sum of all the entries, not just the desired.
Is the syntax wrong? Please help.
Thanks,
Bill

Hi,
look syntax of "iif" in your help file...

 

[Duane Hookom]

Where are Txtbox1 and why mention txtBox2 when there is no mention of it in
the expression. If I can guess your intentions correctly, your first
argument should be:
=IIf([txtBox1]="text1" or [txtBox1]="text2" or [txtBox1]="text3", ....)

 

[Marshall Barton]

Hi,
I am attempting to sum part of the entries in the detail section
of the report in a text box in the group header.
Txtbox1 is text, txtbox2 is a number.
This is the control source in the text box in the header.

=IIf([txtbox1]="text1" Or "text2" Or "text3",Sum([text2]),0)

I am getting a sum of all the entries, not just the desired.

You have written the condition incorrectly. To be legal,
that expression should be:

=IIf([txtbox1]="text1" Or [txtbox1]="text2" Or
[txtbox1]="text3",Sum([text2]),0)

but that could be shortened by using the IN operator.

It's not clear what result you are looking for here. Either
you want this adaption of your original expression:

=IIf([txtbox1] IN("text1","text2","text3"),Sum([text2]),0)

Or, more likely:

=Sum(IIf([txtbox1] IN("text1","text2","text3",[text2],0))

 

[Bill]

Hi,
I am attempting to sum part of the entries in the detail section
of the report in a text box in the group header.
Txtbox1 is text, txtbox2 is a number.
This is the control source in the text box in the header.

=IIf([txtbox1]="text1" Or "text2" Or "text3",Sum([text2]),0)

I am getting a sum of all the entries, not just the desired.
Is the syntax wrong? Please help.
Thanks,
Bill

 

[Bill]

been there, done that
no help

Hi,
I am attempting to sum part of the entries in the detail section
of the report in a text box in the group header.
Txtbox1 is text, txtbox2 is a number.
This is the control source in the text box in the header.

=IIf([txtbox1]="text1" Or "text2" Or "text3",Sum([text2]),0)

I am getting a sum of all the entries, not just the desired.
Is the syntax wrong? Please help.
Thanks,
Bill

Hi,
look syntax of "iif" in your help file...

 

[Bill]

Hi Duane,
So sorry for the error in the syntax I posted.
The syntax should have read:
=IIf([txtbox1]="text1" Or "text2" Or "text3",Sum([textbox2]),0)

I tried this and it worked
=Sum(IIF([txtbox1]="text1" OR [txtbox1]="text2" OR
[txtbox1]="text3",[txtbox2],0))

Marshall recommended another method a few minutes later.
=Sum(IIf([txtbox1] IN("text1","text2","text3"),[textbox2],0))
It is somewhat shorter so I used it.

I tried for hours in VBA help and Access help with NO success.
Thanks for your time and knowledge. Much appreciated!
Bill

Where are Txtbox1 and why mention txtBox2 when there is no mention of it in
the expression. If I can guess your intentions correctly, your first
argument should be:
=IIf([txtBox1]="text1" or [txtBox1]="text2" or [txtBox1]="text3", ....)


--
Duane Hookom
MS Access MVP

Hi,
I am attempting to sum part of the entries in the detail section
of the report in a text box in the group header.
Txtbox1 is text, txtbox2 is a number.
This is the control source in the text box in the header.

=IIf([txtbox1]="text1" Or "text2" Or "text3",Sum([text2]),0)

I am getting a sum of all the entries, not just the desired.
Is the syntax wrong? Please help.
Thanks,
Bill

 

[Bill]

Marsh,
Thanks for your assistance.
This is what I ended up using.
=Sum(IIf([txtbox1] IN("text1","text2","text3"),[textbox2],0))
Worked well.
One question, if I may.
If there are 6 different entries in txtbox1, and I want to eliminate
one of them in a syntax, is there a NOT IN process?
Maybe something like this
=Sum(IIf([txtbox1] NOT IN("text6"),[textbox2],0))

Thanks again for your time and knowledge.
Bill

Hi,
I am attempting to sum part of the entries in the detail section
of the report in a text box in the group header.
Txtbox1 is text, txtbox2 is a number.
This is the control source in the text box in the header.

=IIf([txtbox1]="text1" Or "text2" Or "text3",Sum([text2]),0)

I am getting a sum of all the entries, not just the desired.

You have written the condition incorrectly. To be legal,
that expression should be:

=IIf([txtbox1]="text1" Or [txtbox1]="text2" Or
[txtbox1]="text3",Sum([text2]),0)

but that could be shortened by using the IN operator.

It's not clear what result you are looking for here. Either
you want this adaption of your original expression:

=IIf([txtbox1] IN("text1","text2","text3"),Sum([text2]),0)

Or, more likely:

=Sum(IIf([txtbox1] IN("text1","text2","text3",[text2],0))

 

[Marshall Barton]

Yes, you can do that, though it's usually written
Not txtbox1 IN("text6")
just to be consistent with other uses of the Not operator.

However, using IN with only a single item may be confusing
since IN is normally used with a list of values. For a
single value, it's more straighforward to just use
Not txtbox1 = "text6"
or simply
txtbox1 <> "text6"
--
Marsh
MVP [MS Access]

This is what I ended up using.
=Sum(IIf([txtbox1] IN("text1","text2","text3"),[textbox2],0))
Worked well.
One question, if I may.
If there are 6 different entries in txtbox1, and I want to eliminate
one of them in a syntax, is there a NOT IN process?
Maybe something like this
=Sum(IIf([txtbox1] NOT IN("text6"),[textbox2],0))

I am attempting to sum part of the entries in the detail section
of the report in a text box in the group header.
Txtbox1 is text, txtbox2 is a number.
This is the control source in the text box in the header.

=IIf([txtbox1]="text1" Or "text2" Or "text3",Sum([text2]),0)

I am getting a sum of all the entries, not just the desired.

"Marshall Barton" wrote
You have written the condition incorrectly. To be legal,
that expression should be:

=IIf([txtbox1]="text1" Or [txtbox1]="text2" Or
[txtbox1]="text3",Sum([text2]),0)

but that could be shortened by using the IN operator.

It's not clear what result you are looking for here. Either
you want this adaption of your original expression:

=IIf([txtbox1] IN("text1","text2","text3"),Sum([text2]),0)

Or, more likely:

=Sum(IIf([txtbox1] IN("text1","text2","text3",[text2],0))

 

[Bill]

Thanks for the info.
Have a great one!
Bill

Yes, you can do that, though it's usually written
Not txtbox1 IN("text6")
just to be consistent with other uses of the Not operator.

However, using IN with only a single item may be confusing
since IN is normally used with a list of values. For a
single value, it's more straighforward to just use
Not txtbox1 = "text6"
or simply
txtbox1 <> "text6"
--
Marsh
MVP [MS Access]

This is what I ended up using.
=Sum(IIf([txtbox1] IN("text1","text2","text3"),[textbox2],0))
Worked well.
One question, if I may.
If there are 6 different entries in txtbox1, and I want to eliminate
one of them in a syntax, is there a NOT IN process?
Maybe something like this
=Sum(IIf([txtbox1] NOT IN("text6"),[textbox2],0))

Bill wrote:
I am attempting to sum part of the entries in the detail section
of the report in a text box in the group header.
Txtbox1 is text, txtbox2 is a number.
This is the control source in the text box in the header.

=IIf([txtbox1]="text1" Or "text2" Or "text3",Sum([text2]),0)

I am getting a sum of all the entries, not just the desired.

"Marshall Barton" wrote
You have written the condition incorrectly. To be legal,
that expression should be:

=IIf([txtbox1]="text1" Or [txtbox1]="text2" Or
[txtbox1]="text3",Sum([text2]),0)

but that could be shortened by using the IN operator.

It's not clear what result you are looking for here. Either
you want this adaption of your original expression:

=IIf([txtbox1] IN("text1","text2","text3"),Sum([text2]),0)

Or, more likely:

=Sum(IIf([txtbox1] IN("text1","text2","text3",[text2],0))