I hope I can explain it well enough..... if not, let me know and I'll try

again. :O

Remember the syntax for the immediate IF() function:

IIF(Condition, Value_If_True, Value_If_False)

and, in Access, 0 = False and any other number = True.

You changed the option box "[OpenClosed]" values to 0=open 1=closed.

Lets assume that [OpenClosed] = 0 (closed). Looking at the immediate IF(),

you have

Sum(IIf([OpenClosed],1,0)) AS [Total Closed]

so [Total Closed]: Sum(IIf(0,1,0))

which evaluates to 0.

When [OpenClosed] = 1, you have

[Total Closed]: Sum(IIf(1,1,0))

which evaluates to 1.

Looking at [Total Open]: Sum(IIf([OpenClosed],0,1)),

when [OpenClosed] = 0, you have

[Total Open]: Sum(IIf(0,0,1))

which evaluates to 1

and when [OpenClosed] = 1 (closed)

[Total Open]: Sum(IIf(1,0,1))

which evaluates to 0

The difference is that I used an explicit test:

[OpenClosed]=1 and

[OpenClosed]=2

as the conditions, so my IIF() looked like

Sum(IIf([OpenClosed]= 1,1,0)) AS [Total Closed]

Sum(IIf([OpenClosed]= 2,1,0)) AS [Total Open]

Or, with 0=open 1=closed, I would write:

Sum(IIf([OpenClosed]= 1,1,0)) AS [Total Closed]

Sum(IIf([OpenClosed]= 0,1,0)) AS [Total Open]

You can use IIf([OpenClosed],... but unless [OpenClosed] is type YES/NO, it

is better to use an explicit test as the condition. If you use the explicit

test, You can test for strings:

Sum(IIf([OpenClosed]= "CLOSED",1,0)) AS [Total Closed]

Sum(IIf([OpenClosed]= "OPEN",1,0)) AS [Total Open]

HTH

--

Steve S

--------------------------------

"Veni, Vidi, Velcro"

(I came; I saw; I stuck around.)

blake7 said:

Hi I am using the following select query code, the 'OpenClosed' relates to an

Options Group box with the default set to 1=open 2= closed, the count is

working ok and showing me the number of entries between the two dates i enter

but the count to show open and closed will not show me the result i should be

seeing according to the data in my table, can anyone see where I have gone

wrong ?

Thanks Tony.

SELECT Count(*) AS [Total Entered], Count(IIf([OpenClosed],2,0)) AS [Total

Closed], Count(IIf([OpenClosed],0,1)) AS [Total Open]

FROM [Main Issue Entry Sheet]

WHERE ((([Main Issue Entry Sheet].[Date Reported]) Between [Enter start

date:] And [Enter end date:]));