Abs function causing +- errors when summed

  • Thread starter Thread starter michael c
  • Start date Start date
M

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!
 
michael c said:
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!
Click to expand...

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

MTDDollarsShipped:
Abs(Sum(([InvoiceDate]>=Forms!Dashboard!FirstDayOfMonth))*DollarsShipped
])
 
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!
Click to expand...

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)


.
Click to expand...
 
michael c said:
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?
Click to expand...

Ah, okay. Try this:

MTDDollarsShipped:
Sum(Abs(([InvoiceDate]>=[Forms]![Dashboard]![FirstDayOfMonth]))*[Dollars
Shipped])
 
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?
Click to expand...

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)


.
Click to expand...
 
michael c said:
Cool. That worked...and so did this:

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

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.
 
Back
Top