IIF function

[Denver]

I have a checkbox field name Include:Sum(IIF([Include]=0,0,1)) and in have
this expression in my QBE
i want to count the number of TRUE value or the Yes value in my checkbox.
in my queries i've checked the data it is correct or it counts will the true
value
but when I pass it to the report it doesnt give me the correct count.
for example I have 12,330 records so when i uncheck 5 records it should
subtract
5 so it appears in my report as 12,325. but what appears i the report is
12,328. Do i miss something on my queries or do i mis something in my reports.



Thanks for any help i appreciate

 

[John Spencer]

I would try a slightly different expression.
Include: Abs(Sum([TableName].[Include]))

Also, Access sometimes has problems if you alias a field with the same name as
a field.

Hope this helps.

Oh, if the above does give you the correct count, check the data in the
Include field to see if it is causing the problem. Don't do an aggregate
query, do a select query

SELECT Include
FROM SomeTable
WHERE Include <> 0
(How many records are returned)

SELECT Include
FROM SomeTable
WHERE Include = 0
(How many records are returned)

SELECT Include
FROM SomeTable
(How many records are returned)

The sum of the first two should equal the sum of the last. If it doesn't then
you have a data problem.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Denver]

Thanks, John Spencer

I would try a slightly different expression.
Include: Abs(Sum([TableName].[Include]))

Also, Access sometimes has problems if you alias a field with the same name as
a field.

Hope this helps.

Oh, if the above does give you the correct count, check the data in the
Include field to see if it is causing the problem. Don't do an aggregate
query, do a select query

SELECT Include
FROM SomeTable
WHERE Include <> 0
(How many records are returned)

SELECT Include
FROM SomeTable
WHERE Include = 0
(How many records are returned)

SELECT Include
FROM SomeTable
(How many records are returned)

The sum of the first two should equal the sum of the last. If it doesn't then
you have a data problem.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a checkbox field name Include:Sum(IIF([Include]=0,0,1)) and in have
this expression in my QBE
i want to count the number of TRUE value or the Yes value in my checkbox.
in my queries i've checked the data it is correct or it counts will the true
value
but when I pass it to the report it doesnt give me the correct count.
for example I have 12,330 records so when i uncheck 5 records it should
subtract
5 so it appears in my report as 12,325. but what appears i the report is
12,328. Do i miss something on my queries or do i mis something in my reports.



Thanks for any help i appreciate