UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Count If...., Access 2016    
 
   
joelkrywolt
post Aug 9 2018, 08:41 AM
Post#1



Posts: 81
Joined: 18-July 07
From: Leduc, Alberta


Thanks in advance,

I have a report and in the details there is a bit field. I am trying to count all 1 values using this to no avail:

=Count(IIf([XrayNo]=True,0,1))

Where Am i going wrong? This returns all values.

Go to the top of the page
 
JonSmith
post Aug 9 2018, 08:45 AM
Post#2



Posts: 3,958
Joined: 19-October 10



True = -1, if you want to count all the 1's you need to use =Count(IIf([XrayNo]=1,0,1))
Go to the top of the page
 
joelkrywolt
post Aug 9 2018, 09:12 AM
Post#3



Posts: 81
Joined: 18-July 07
From: Leduc, Alberta


Thanks for the Help. This still returns all rows regardless of True/ False.
=Count(IIf([XrayComplete]=True,0,1))
Go to the top of the page
 
RJD
post Aug 9 2018, 09:13 AM
Post#4


UtterAccess VIP
Posts: 9,212
Joined: 25-October 10
From: Gulf South USA


Hi: Actually, as counter-intuitive as it seems, you need to use Sum rather than Count here. Count counts everything. If you want the count of the True contents, then ...

=Sum(IIf([XrayNo]=True,1,0))

This will add up all the 1s produced when XrayNo is True.

If you want all the not True contents ...

=Sum(IIf([XrayNo]=True,0,1))

See if that gives you what you want.

HTH
Joe
Go to the top of the page
 
joelkrywolt
post Aug 9 2018, 09:50 AM
Post#5



Posts: 81
Joined: 18-July 07
From: Leduc, Alberta


Thanks very much. Works perfectly
Go to the top of the page
 
RJD
post Aug 9 2018, 09:53 AM
Post#6


UtterAccess VIP
Posts: 9,212
Joined: 25-October 10
From: Gulf South USA


You are very welcome.

Regards,
Joe
Go to the top of the page
 
NimishParikh
post Aug 10 2018, 08:16 AM
Post#7



Posts: 211
Joined: 30-November 10



Can't we use straight function Abs(Sum([XrayNo])) to arrive at the desired result.

Just a thought.

Nimish
Go to the top of the page
 
RJD
post Aug 10 2018, 09:36 AM
Post#8


UtterAccess VIP
Posts: 9,212
Joined: 25-October 10
From: Gulf South USA


Hi Nimish: Yes, that should, of course, work as well. And there are no doubt other options as well. There are at least two considerations in deciding which option to use ...

1. Efficiency (and that's a testable situation, and in this case may make no importance difference)
2. Understanding (if someone else will follow to do maintenance, and may struggle with understanding of one approach vs the other)

But you are certainly right to suggest this shorter alternative.

Just some thoughts ...

Regards,
Joe
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st November 2018 - 03:01 PM