Syntax help summing part of a txtbox

  • Thread starter Thread starter Gaspare
  • Start date Start date
G

Gaspare

Bill said:
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
Click to expand...
Hi,
look syntax of "iif" in your help file...
 
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", ....)
 
Bill said:
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.
Click to expand...


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))
 
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
 
been there, done that
no help

Gaspare said:
Bill said:
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
Click to expand...
Hi,
look syntax of "iif" in your help file...
Click to expand...
 
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

Duane Hookom said:
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


Bill said:
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
Click to expand...
Click to expand...
 
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

Marshall Barton said:
Bill said:
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.
Click to expand...


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))
Click to expand...
 
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 said:
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.
Click to expand...

"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))
Click to expand...
Click to expand...
 
Thanks for the info.
Have a great one!
Bill

Marshall Barton said:
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))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top