Abs function causing +- errors when summed

[michael c]

Hi. I have a function that sums the orders in a table
based on the invoice date like so:

MTDDollarsShipped: Abs(Sum(([InvoiceDate]>=Forms!Dashboard!
FirstDayOfMonth)*[DollarsShipped]))

The problem is that if, at the very beginning of the month
I have negative orders for some reason, which sometimes
happens with some product lines, then the number will show
up positive on reports. Is there any way to correct for
this? Any recommendations to modify the code would be
great. THANKS!

 

[Dirk Goldgar]

Hi. I have a function that sums the orders in a table
based on the invoice date like so:

MTDDollarsShipped: Abs(Sum(([InvoiceDate]>=Forms!Dashboard!
FirstDayOfMonth)*[DollarsShipped]))

The problem is that if, at the very beginning of the month
I have negative orders for some reason, which sometimes
happens with some product lines, then the number will show
up positive on reports. Is there any way to correct for
this? Any recommendations to modify the code would be
great. THANKS!

It seems to me you have your parentheses in the wrong place. Try this:

MTDDollarsShipped:
Abs(Sum(([InvoiceDate]>=Forms!Dashboard!FirstDayOfMonth))*DollarsShipped
])

 

[michael c]

Dirk, Thanks for the response. That function gives me an
error that I'm trying to execute a query that does not
include the specified expression as part of an aggregate
function:

MTDDollarsShipped: Abs(Sum(([InvoiceDate]>=[Forms]!
[Dashboard]![FirstDayOfMonth]))*[DollarsShipped])

Any thoughts?

-----Original Message-----

Hi. I have a function that sums the orders in a table
based on the invoice date like so:

MTDDollarsShipped: Abs(Sum(([InvoiceDate]>=Forms! Dashboard!
FirstDayOfMonth)*[DollarsShipped]))

The problem is that if, at the very beginning of the month
I have negative orders for some reason, which sometimes
happens with some product lines, then the number will show
up positive on reports. Is there any way to correct for
this? Any recommendations to modify the code would be
great. THANKS!

It seems to me you have your parentheses in the wrong place. Try this:

MTDDollarsShipped:
Abs(Sum(([InvoiceDate]>=Forms!Dashboard!FirstDayOfMonth)) *DollarsShipped
])

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Dirk, Thanks for the response. That function gives me an
error that I'm trying to execute a query that does not
include the specified expression as part of an aggregate
function:

MTDDollarsShipped: Abs(Sum(([InvoiceDate]>=[Forms]!
[Dashboard]![FirstDayOfMonth]))*[DollarsShipped])

Any thoughts?

Ah, okay. Try this:

MTDDollarsShipped:
Sum(Abs(([InvoiceDate]>=[Forms]![Dashboard]![FirstDayOfMonth]))*[Dollars
Shipped])

 

[michael c]

Cool. That worked...and so did this:

MTDDollarsShipped: -(Sum(([InvoiceDate]>=[Forms]!
[Dashboard]![FirstDayOfMonth])*[DollarsShipped]))

This is a huge help! Thanks, Dirk.

-----Original Message-----

Dirk, Thanks for the response. That function gives me an
error that I'm trying to execute a query that does not
include the specified expression as part of an aggregate
function:

MTDDollarsShipped: Abs(Sum(([InvoiceDate]>=[Forms]!
[Dashboard]![FirstDayOfMonth]))*[DollarsShipped])

Any thoughts?

Ah, okay. Try this:

MTDDollarsShipped:
Sum(Abs(([InvoiceDate]>=[Forms]![Dashboard]! [FirstDayOfMonth]))*[Dollars
Shipped])

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Cool. That worked...and so did this:

MTDDollarsShipped: -(Sum(([InvoiceDate]>=[Forms]!
[Dashboard]![FirstDayOfMonth])*[DollarsShipped]))

Yes, that will work -- obviously! <g> I tend to keep away from relying
on the value of True being -1, because that isn't always so on other
systems.