calculating Subtotals in Access Reports but under certain criteria.

[subs]

is there any way i can subtotal rows in Access report by certain
Criteria. for example this is the report i have


Company code Gl code Total paid amt
1002 10210 220 USD
1002 23454 10 USD
1002 23567 1000 USD
SubTotal 1010 USD
1005 32222 10 USD
1005 23467 12 USD
Sub Total 12 USD

Now the above report has subtotals for paid amt at the end of every
Company code. But as you can see the subtotal is calculated for only
GL codes starting with digit 2.

Is there any way to set criteria like that in design view of a Access
Report or even in an Underlying query. In the query, i am not even
calculating subtotals. Pls help thanks

 

[Krzysztof Naworyta]

subs wrote:
| is there any way i can subtotal rows in Access report by certain
| Criteria. for example this is the report i have
|
|
| Company code Gl code Total paid amt
| 1002 10210 220 USD
| 1002 23454 10 USD
| 1002 23567 1000 USD
| SubTotal 1010 USD
| 1005 32222 10 USD
| 1005 23467 12 USD
| Sub Total 12 USD
|
| Now the above report has subtotals for paid amt at the end of every
| Company code. But as you can see the subtotal is calculated for only
| GL codes starting with digit 2.
|
| Is there any way to set criteria like that in design view of a Access
| Report or even in an Underlying query. In the query, i am not even
| calculating subtotals. Pls help thanks


= Sum(iif(left([Gl code])="2";[Total paid amt];null))

 

[subs]

subs wrote:

| is there any way i can subtotal rows in Access report by certain
| Criteria. for example this is the report i have
|
|
| Company code      Gl code    Total paid amt
| 1002                      10210          220 USD
| 1002                      23454           10 USD
| 1002                      23567           1000 USD
|          SubTotal                          1010 USD
| 1005                        32222          10 USD
| 1005                         23467          12 USD
|           Sub Total                          12 USD
|
| Now the above report has subtotals for paid amt at the end of every
| Company code. But as you can see the subtotal is calculated for only
| GL codes starting with digit 2.
|
| Is there any way to set criteria like that in design view of a Access
| Report or even in an Underlying query. In the query, i am not even
| calculating subtotals. Pls help thanks

= Sum(iif(left([Gl code])="2";[Total paid amt];null))

donot know why this is not working- is showing syntax error.

 

[Krzysztof Naworyta]

subs wrote:
| On Aug 20, 5:56 am, "Krzysztof Naworyta" <[email protected]>

||| is there any way i can subtotal rows in Access report by certain
||| Criteria. for example this is the report i have
|||
|||
||| Company code Gl code Total paid amt
||| 1002 10210 220 USD
||| 1002 23454 10 USD
||| 1002 23567 1000 USD
||| SubTotal 1010 USD
||| 1005 32222 10 USD
||| 1005 23467 12 USD
||| Sub Total 12 USD
|||
||| Now the above report has subtotals for paid amt at the end of every
||| Company code. But as you can see the subtotal is calculated for only
||| GL codes starting with digit 2.
|||
||| Is there any way to set criteria like that in design view of a
||| Access Report or even in an Underlying query. In the query, i am
||| not even calculating subtotals. Pls help thanks
||
|| = Sum(iif(left([Gl code])="2";[Total paid amt];null))


| donot know why this is not working- is showing syntax error.


Where did you put it ?
This is expression in a calculated textbox in report footer

 

[Bob Barrows]

is there any way i can subtotal rows in Access report by certain
Criteria. for example this is the report i have


Company code Gl code Total paid amt
1002 10210 220 USD
1002 23454 10 USD
1002 23567 1000 USD
SubTotal 1010 USD
1005 32222 10 USD
1005 23467 12 USD
Sub Total 12 USD

Now the above report has subtotals for paid amt at the end of every
Company code. But as you can see the subtotal is calculated for only
GL codes starting with digit 2.

Is there any way to set criteria like that in design view of a
Access Report or even in an Underlying query. In the query, i am
not even calculating subtotals. Pls help thanks

= Sum(iif(left([Gl code])="2";[Total paid amt];null))

donot know why this is not working- is showing syntax error.

There are two problems I see, one of which is the syntax error with which
I'll deal first:
The Left function requires two arguments: the first telling it the string to
work on, and the second telling it how many characters you want. It appears
you want the first character so change it to:

left([Gl code],1)

The other potential issue is using null there. I would be safe and use zero
(0) instead, since adding values that include nulls can lead to
unpredictable results.

 

[Krzysztof Naworyta]

Bob Barrows wrote:

||| = Sum(iif(left([Gl code])="2";[Total paid amt];null))

|| donot know why this is not working- is showing syntax error.

| There are two problems I see, one of which is the syntax error with
| which I'll deal first:
| The Left function requires two arguments: the first telling it the
| string to work on, and the second telling it how many characters you
| want. It appears you want the first character so change it to:
|
| left([Gl code],1)

you're right! I omitted second parameter...

| The other potential issue is using null there. I would be safe and
| use zero (0) instead, since adding values that include nulls can lead
| to unpredictable results.

For Sum() you can use zero (0). There is no difference...
But if you use other functions (avg, count, stdev, max) only nulls can give
right values...

We can look for issues in this expression:
iif(left(Null,1),...

But
Left(null,1)
gives Null and iif() gives third parameter...