AbsSum Won't Work With Null Values

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

Hi. I have a query that is using several Abs functions to
come up with column values. All are working fine as far as
I can tell except one. The query pulls from a table with
the following fields:

SAPNumber,Customer Name, InvoiceDate, DaysElapsed

The problem expression is:

InitialOrders: Count((Abs([DaysElapsed] Is Null))*
[SAPNumber])

This is supposed to count the number of times an account,
represented by SAPNumber, shows up with a DaysElapsed
value that is Null. For some reason it's counting all the
rows in the table. Any thoughts would be much appreciated.
Thanks!
 
Still no luck.
-----Original Message-----
Try this:

InitialOrders: Count((Abs(IsNull([DaysElapsed] )))* [SAPNumber])

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hi. I have a query that is using several Abs functions to
come up with column values. All are working fine as far as
I can tell except one. The query pulls from a table with
the following fields:

SAPNumber,Customer Name, InvoiceDate, DaysElapsed

The problem expression is:

InitialOrders: Count((Abs([DaysElapsed] Is Null))* [SAPNumber])

This is supposed to count the number of times an account,
represented by SAPNumber, shows up with a DaysElapsed
value that is Null. For some reason it's counting all the
rows in the table. Any thoughts would be much appreciated.
Thanks!
Click to expand...


.
Click to expand...
 
Try summing instead of counting.
InitialOrders: Abs(SUM(IsNull(DaysElapsed))
Still no luck.
-----Original Message-----
Try this:

InitialOrders: Count((Abs(IsNull([DaysElapsed] )))* [SAPNumber])

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hi. I have a query that is using several Abs functions to
come up with column values. All are working fine as far as
I can tell except one. The query pulls from a table with
the following fields:

SAPNumber,Customer Name, InvoiceDate, DaysElapsed

The problem expression is:

InitialOrders: Count((Abs([DaysElapsed] Is Null))* [SAPNumber])

This is supposed to count the number of times an account,
represented by SAPNumber, shows up with a DaysElapsed
value that is Null. For some reason it's counting all the
rows in the table. Any thoughts would be much appreciated.
Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks! That seems to have worked but I don't see why. The
DaysElapsed fields have number values in them so I would
think it would be summing the null field values. What I
was trying to do, and your suggestion effectively did
this, was just count the number of records that have null
values. If you have the time to explain please do but, if
you don't, thanks again for the suggestion. It was a BIG
help.

-----Original Message-----
Try summing instead of counting.
InitialOrders: Abs(SUM(IsNull(DaysElapsed))
Still no luck.
-----Original Message-----
Try this:

InitialOrders: Count((Abs(IsNull([DaysElapsed] )))* [SAPNumber])

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hi. I have a query that is using several Abs
Click to expand...
functions
to
come up with column values. All are working fine as
Click to expand...
far
as
I can tell except one. The query pulls from a table with
the following fields:

SAPNumber,Customer Name, InvoiceDate, DaysElapsed

The problem expression is:

InitialOrders: Count((Abs([DaysElapsed] Is Null))* [SAPNumber])

This is supposed to count the number of times an account,
represented by SAPNumber, shows up with a DaysElapsed
value that is Null. For some reason it's counting all the
rows in the table. Any thoughts would be much appreciated.
Thanks!




.
Click to expand...
Click to expand...
.
Click to expand...
 
Your expression returned one of two values 0 or -1 (False or True).

When you Count you count any values that are not null. When you Sum the zeroes
don't increase the value. The Abs function removes the negative sign if there
is one.
Thanks! That seems to have worked but I don't see why. The
DaysElapsed fields have number values in them so I would
think it would be summing the null field values. What I
was trying to do, and your suggestion effectively did
this, was just count the number of records that have null
values. If you have the time to explain please do but, if
you don't, thanks again for the suggestion. It was a BIG
help.
-----Original Message-----
Try summing instead of counting.
InitialOrders: Abs(SUM(IsNull(DaysElapsed))
Still no luck.

-----Original Message-----
Try this:

InitialOrders: Count((Abs(IsNull([DaysElapsed] )))*
[SAPNumber])

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

message
Hi. I have a query that is using several Abs functions
to
come up with column values. All are working fine as far
as
I can tell except one. The query pulls from a table with
the following fields:

SAPNumber,Customer Name, InvoiceDate, DaysElapsed

The problem expression is:

InitialOrders: Count((Abs([DaysElapsed] Is Null))*
[SAPNumber])

This is supposed to count the number of times an
account,
represented by SAPNumber, shows up with a DaysElapsed
value that is Null. For some reason it's counting all
the
rows in the table. Any thoughts would be much
appreciated.
Thanks!




.
Click to expand...
.
Click to expand...
Click to expand...
 
John said:
Your expression returned one of two values 0 or -1 (False or True).

When you Count you count any values that are not null. When you Sum the zeroes
don't increase the value. The Abs function removes the negative sign if there
is one.
Click to expand...

The sum is reliably negative, right? Wouldn't a negation be sufficient?
 
The table I'm querying looks like this:

SAPNumber DaysElapsed
0000011481
0000011481 2
0000011609
0000011609 23
0000011611
0000011611 139
0000011669
0000011669 70
0000011669 46

Your expression is summing the number of times a record
shows up with a null value under DaysElapsed; this is
exactly what I'd hoped for. So thanks again.

However, I'm not sure how the program knows that it is
supposed to count the number of records with Null values.
I didn't think I could use the Sum function to mimick the
Count function. I'd have thought that your
expression...InitialOrders: Abs(Sum(IsNull
([DaysElapsed])))...would try to Sum all the Null values
which would give a zero or no value at all. I was
surprised that it actually counted the number of records
having Null values.

At any rate, I'm glad it works and it was very helpful
indeed. I'm just trying to understand the intracacies of
Count versus Sum as the demands I place on my databases
become more complex. Thanks!

-----Original Message-----
Your expression returned one of two values 0 or -1 (False or True).

When you Count you count any values that are not null. When you Sum the zeroes
don't increase the value. The Abs function removes the negative sign if there
is one.
Thanks! That seems to have worked but I don't see why. The
DaysElapsed fields have number values in them so I would
think it would be summing the null field values. What I
was trying to do, and your suggestion effectively did
this, was just count the number of records that have null
values. If you have the time to explain please do but, if
you don't, thanks again for the suggestion. It was a BIG
help.
-----Original Message-----
Try summing instead of counting.
InitialOrders: Abs(SUM(IsNull(DaysElapsed))

mike wrote:

Still no luck.

-----Original Message-----
Try this:

InitialOrders: Count((Abs(IsNull([DaysElapsed] )))*
[SAPNumber])

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

message
Hi. I have a query that is using several Abs functions
to
come up with column values. All are working fine
Click to expand...
as
far
as
I can tell except one. The query pulls from a
Click to expand...
table
with
the following fields:

SAPNumber,Customer Name, InvoiceDate, DaysElapsed

The problem expression is:

InitialOrders: Count((Abs([DaysElapsed] Is Null))*
[SAPNumber])

This is supposed to count the number of times an
account,
represented by SAPNumber, shows up with a DaysElapsed
value that is Null. For some reason it's counting all
the
rows in the table. Any thoughts would be much
appreciated.
Thanks!




.

.
Click to expand...
Click to expand...
.
Click to expand...
 
In Access + Jet yes. However, there are other databases that can be linked to
and they may store true as a positive value.
 
Back
Top