Filtering at report level?

[morg59jeep]

I have a report that has various grouping for subtotals and a grand total at the bottom. Is there a way to total a range of items at the bottom of the page that is not influenced by my grouping? The range of items i need a total for falls into many of the different groups.

 

[morg59jeep]

Here is a little more detail.

I have a report for items that the detail shows me the color, size, serial number and price. My report groups the items by color and sorts them by size and subtotals them. At the bottom of my report I get a total price for all records. What I need is for my report to be able to give me a total price for a range of serial numbers.

 

[dhookom]

If I understand correctly, you should be able to use an expression like:
=Sum(Abs([SerialNumber] Between 100 and 200) * [Price])


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[morg59jeep]

I tried that but when I run the report I get the following error:
The expression is typed incorrectly or is too complex to be evaluated....

here is exactly what I have entered:
=Sum(Abs([costCode] Between 4000 And 4999)*[Amount])

Let me clairify that each serial number(cost code) has a different price. Im guessing that your expression is trying to multiply it by a single one?

 

[dhookom]

Is your CostCode text or numeric? Maybe you need to clarify "total price for a range of serial numbers". Where did [Amount] come from since your earlier posting mentioned a [Price] field? What do you want to total?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[morg59jeep]

CostCode is a numeric field. The range I need is from 4099 to 4999z

For example some are labled 4021, 4021a, 4021b

[Amount] and [Price] are the same thing... I just used the word price to make it easier to understand my question. I am sorry I didnt make myself more clear at first.

Thank you for your help.

 

[dhookom]

CostCode can't be a numeric field if you are storing values like [red]4021a[/red]. Your expression would need to be something like:

=Sum(Abs([costCode] Between "4000" And "4999z")*[Amount])

Keep in mind that "42" is between "4000" and "4999z".


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[morg59jeep]

Worked like a charm!.. Thank you very much. You were also correct on the cost code not being numeric.