Trying to count records (1 Viewer)

dkmoreland · Mar 15, 2018

I'm using a query to try to count the number of records in another query that have a value of zero.

But, when I try to run the query, I get a message that the expression is too complex to be evaluated.

Code:

NumZero: Count(IIf([Quantity Scrapped]= 0,0))

This doesn't seem that complex to me. What am I missing?

Thanks in advance.

isladogs · Mar 15, 2018

You're missing the fact that it doesn't make any sense at all
IIf statements have 3 parts:
criteria: IIf([Quantity Scrapped]= 0
true part: 0
false part: missing

BUT how can you count that anyway?
Try using DCount

Code:

DCount("*","MyQueryName","[Quantity Scrapped]=0")

However using DCount in a query may be slow - other methods such as a UDF may be better

MarkK · Mar 15, 2018

The IIF() function takes three parameters...

Code:

IIF(<boolean expression>, <truepart>, <falsepart>)

...but what I usually do in this case, knowing that the boolean true is equal to numeric -1, I would sum a boolean expression, and then negate it, like this...

Code:

NumZero: -Sum([Quantity Scrapped] = 0)

...so for every row where that expression is true, a -1 is summed, effectively counting the rows where the expression is true, and then you negate it, because it will be negative.
hth
Mark

dkmoreland · Mar 15, 2018

I did miss that 3rd parameter - forest for the trees, I guess.

I tried this:

Code:

NumZero: -Sum([Quantity Scrapped]=0)

and got the same expression too complex error message. I'll see what I can do with a UDF instead of a query.

Thanks

1268 · Mar 15, 2018

You could make function and create a record set equal to your query and loop though them. I do the same to compare downloaded files with todays date to the count of needed files. It's easy and runs fast..
Function nameoffunction() as integer
Dim rs as DOA.recordset
Set rs = DBEngine(0)(0).openrecordset("nameofyourquery")
I = 0

Do while not rs.eof

'Some code to further filter them

I = I +1
Rs.movenext
Loop
End function

Sub getcount()
Dim account as integer
Fcount = nameoffunction()
End sub

Just make sure your query is o my the records you want to count or do more evaluation on each record and choose to count it or not.

Sent from my SM-G950U using Tapatalk

isladogs · Mar 15, 2018

Maybe I'm being dense here but why can't you just do:

Code:

Count([Quantity Scrapped] = 0)

Mark_ · Mar 15, 2018

something like

Code:

Dim rs As DAO.Recordset
Dim asReturn As String
Set rs = CurrentDb.OpenRecordset("SELECT * FROM TABLE WHERE [QUANTITY SCRAPPED]=0")
rs.MoveLast
rs.MoveFirst
MSGBOX "There are " &  rs.RecordCount & " records"

MarkK · Mar 15, 2018

I would never open a full recordset and populate it just to count the records. This is way faster...

Code:

SELECT Count(*) FROM Table WHERE QuantityScrapped = 0

...but the reason you would do it like this in the first place...

Code:

NumZero: IIF(SomeCondition, 1, 0)
'or
NumZero: -Sum(SomeCondition)

...is if you were doing a count like this on multiple conditions. Then, if you want to the put the condition in the WHERE clause you'd have to open multiple recordsets to get your result. If you do something like this, you can calculate three (or more) different conditions in a single query...

Code:

NoScrap: -Sum(Scrapped = 0)  |  MediumScrap: -Sum(Scrapped < 10)  |  OutOfControlScrap: -Sum(Scrapped > 100)

My 2c,
Mark

CJ_London · Mar 15, 2018

the error may be being generated because Quantity Scrapped is null

try using the nz function and set it to -1 if null, otherwise it will default to 0

e.g.

nz([Quantity Scrapped],-1)=0

Mark_ · Mar 16, 2018

MarkK said:
I would never open a full recordset and populate it just to count the records. This is way faster...

Code:

SELECT Count(*) FROM Table WHERE QuantityScrapped = 0

...but the reason you would do it like this in the first place...

Code:

NumZero: IIF(SomeCondition, 1, 0) 'or NumZero: -Sum(SomeCondition)

...is if you were doing a count like this on multiple conditions. Then, if you want to the put the condition in the WHERE clause you'd have to open multiple recordsets to get your result. If you do something like this, you can calculate three (or more) different conditions in a single query...

Code:

NoScrap: -Sum(Scrapped = 0) | MediumScrap: -Sum(Scrapped < 10) | OutOfControlScrap: -Sum(Scrapped > 100)

My 2c,
Mark

Much better idea. Must have been an attack of cranial flatulence on my part.

MarkK · Mar 16, 2018

Mark_ said:
cranial flatulence

Lol, that's funny.
Mark

Trying to count records (1 Viewer)

dkmoreland

Registered User.

isladogs

MVP / VIP

MarkK

bit cruncher

dkmoreland

Registered User.

1268

Registered User.

isladogs

MVP / VIP

Mark_

Longboard on the internet

MarkK

bit cruncher

CJ_London

Super Moderator

Mark_

Longboard on the internet

MarkK

bit cruncher

Similar threads

Users who are viewing this thread