Problem with zeros

[jtm013]

I am looking at frmCountOverdue. It has one textbox bound to CountOfRecordID.

This expression in textbox should work: =Nz([CountOfrecordID],0)
However, I get error "There is an error compiling this function. The Visual Basic module contains a syntax error. Check the code, and then recompile it."
Your VBA has procedures with API calls that won't run on my 64-bit computer. I simply deleted the two procedures and then Access compiles properly and the expression is accepted.



If there is a chance this db will run on 64-bit machines, need to research PtrSafe parameter in API declarations.

I entered "=Nz([CountofRecordID],0)" (without quotes) into the text box. It still displays nothing when it should show zero.

[June7]

That's because there are no records that meet the WHERE and HAVING filter criteria. The query dataset is empty so there is no data to aggregate and no value for the textbox to evaluate in an expression.

Personally, I wouldn't build a query and a form just to return a single value for display. I would use domain aggregate.

=Nz(DCount("*", "tblDataPrime", "[toolNextCalDate]<Date() AND [toolActive]=True AND [toolLocat]<>'Scrap'"),0)

[jtm013]

Okay domain aggregate- never used this before.
I'm inferring that I would input the expression into a text box directly on the desired form?

[jtm013]

Aggregate functions- I had never used now I love them. This will save a lot of time vs my previous methodology.

Works great!

[June7]

Yes, they can be handy but also slow performers in queries and textboxes. Will be able to see the recalc take place.

Sorry took me so long to grasp what you were doing and to finally present the alternative. Glad it works.