CODEWITHSUNDEEP
ms-accessms-access-2016
Sean Reilly Wood
Sean Reilly Wood

Reputation: 3

Access Report #Error Text Message

I'm attempting to build a summary report in Access with some custom statistics based on a daily query. However, I'm running into some difficulties. When I use a report built off said query, I get the same exact number regardless of the formula.

I'm building a formula to count text values in a field or count all responses in a field but the formula will always return 18 as a value (the total number of records) even when I know it to be false for the formula. Alternatively, I will create a blank report and no matter what I do either receive an #Error or #Name? value in the textbox. I have checked and the name from the control is not any name in formula or anywhere else - I normally rename it Text0 or Demo.

The formula I've been attempting to use is 

=Count(IIf([Daily_Numbers_Query].[Signed_Card] Is Not Null,1,0));

Where Daily_Numbers_Query refers to the query and Signed_Card refers to the field I want to examine from that query. I want it to tell my the number of records for which Signed_Card has a value and is not null.

Any help would be extremely appreciated.

Upvotes: 0

Views: 186

Answers (2)

June7
June7

Reputation: 21370

You said the query is in the report's RecordSource, then just need to use the correct false return. Also, probably don't need the query name prefix.

=Count(IIf([Signed_Card] Is Not Null, 1, Null))

or

=Count(IIf(Not IsNull([Signed_Card]), 1, Null))

or

=Sum(IIf([Signed_Card] Is Not Null, 1, 0))

Upvotes: 0

Andre
Andre

Reputation: 27634

Count is an aggregate function in SQL, but you can't use it directly in an expression.

For this you use Domain functions: DCount, DSum, ...

In this case the easiest would be:

=DCount("*", "Daily_Numbers_Query", "[Signed_Card] Is Not Null")

If you wanted to use IIf, you'd have to use DSum:

=DSum(IIf([Daily_Numbers_Query].[Signed_Card] Is Not Null,1,0))

Upvotes: 0

Related Questions