Select Query Question

[blake7]

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:]));

 

[Steve Sanford]

Hi Tony,

The problem is with the immediate IF() function. The syntax is:

IIF(Condition, Value_If_True, Value_If_False)

where condition evaluates to TRUE or FALSE.

for the OPEN, use:
IIF([OpenClosed] = 1 , 1, 0)

and for CLOSED, use
IIF([OpenClosed] = 2 , 1, 0)


Try using this:

SELECT Count(*) AS [Total Entered], Count(IIf([OpenClosed]=2,1,0)) AS [Total
Closed], Count(IIf([OpenClosed]=1,1,0)) AS [Total Open] FROM [Main Issue
Entry Sheet] WHERE ((([Main Issue Entry Sheet].[Date Reported]) Between
[Enter start date:] And [Enter end date:]));






Also, you shouldn't use spaces in object names. It causes you headaches...
see "The Ten Commandments of Access" at

http://www.mvps.org/access/tencommandments.htm




HTH

 

[blake7]

Hi Steve, Thanks for your help, it still will not run the query, it now comes
back with the following message: "This expression is typed incorrectly or it
is too complex to be evaluated, for example a numeric expression may contain
too many complicated elements, try to simplify the expression by assigning
parts of the expression to variables"

Thanks Tony.

Hi Tony,

The problem is with the immediate IF() function. The syntax is:

IIF(Condition, Value_If_True, Value_If_False)

where condition evaluates to TRUE or FALSE.

for the OPEN, use:
IIF([OpenClosed] = 1 , 1, 0)

and for CLOSED, use
IIF([OpenClosed] = 2 , 1, 0)


Try using this:

SELECT Count(*) AS [Total Entered], Count(IIf([OpenClosed]=2,1,0)) AS [Total
Closed], Count(IIf([OpenClosed]=1,1,0)) AS [Total Open] FROM [Main Issue
Entry Sheet] WHERE ((([Main Issue Entry Sheet].[Date Reported]) Between
[Enter start date:] And [Enter end date:]));






Also, you shouldn't use spaces in object names. It causes you headaches...
see "The Ten Commandments of Access" at

http://www.mvps.org/access/tencommandments.htm




HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

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:]));

 

[Steve Sanford]

Tony,

I made a table with fields:

Total Entered :Text
OpenClosed : Integer
Date Reported : Date/Time


I entered 7 records, 5 Open and 2 Closed. I ran the query... it worked, but
it returned

[Total Entered] = 7, [Total Closed] = 7, [Total Open] = 7.

<smack> This is right because of the Count() for [OpenClosed]. The IIF()
function returns 0 or 1, so Sum() should be used instead of Count().


Using this query:

SELECT Count(*) AS [Total Entered], SUM(IIf([OpenClosed]=2,1,0)) AS [Total
Closed], SUM(IIf([OpenClosed]=1,1,0)) AS [Total Open] FROM [Main Issue Entry
Sheet] WHERE ((([Main Issue Entry Sheet].[Date Reported]) Between [Enter
start date:] And [Enter end date:]));


returned

[Total Entered] = 7, [Total Closed] = 2, [Total Open] = 5


If you still get an error, check field names for spelling errors. If it
still won't run, create a new query. Add the table. Drag down the date field,
then type in "Count(*) AS [Total Entered]". Run the query.

When that runs without an error, add "SUM(IIf([OpenClosed]=2,1,0)) AS [Total
Closed]" in the next column of the grid.

When the query runs without errors, add the last field
"SUM(IIf([OpenClosed]=1,1,0)) AS [Total Open]".


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Steve, Thanks for your help, it still will not run the query, it now comes
back with the following message: "This expression is typed incorrectly or it
is too complex to be evaluated, for example a numeric expression may contain
too many complicated elements, try to simplify the expression by assigning
parts of the expression to variables"

Thanks Tony.

Hi Tony,

The problem is with the immediate IF() function. The syntax is:

IIF(Condition, Value_If_True, Value_If_False)

where condition evaluates to TRUE or FALSE.

for the OPEN, use:
IIF([OpenClosed] = 1 , 1, 0)

and for CLOSED, use
IIF([OpenClosed] = 2 , 1, 0)


Try using this:

SELECT Count(*) AS [Total Entered], Count(IIf([OpenClosed]=2,1,0)) AS [Total
Closed], Count(IIf([OpenClosed]=1,1,0)) AS [Total Open] FROM [Main Issue
Entry Sheet] WHERE ((([Main Issue Entry Sheet].[Date Reported]) Between
[Enter start date:] And [Enter end date:]));






Also, you shouldn't use spaces in object names. It causes you headaches...
see "The Ten Commandments of Access" at

http://www.mvps.org/access/tencommandments.htm




HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

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:]));

 

[blake7]

Hi Steve, Thanks for all your help I appreciate it, between our last
communications I got it working by using the code below, I had to change the
open and closed values in the group box to 0=open 1=closed and it did the
trick, I not sure why changing the values would have an effect on the count
or sum feature? what do you think? Best regards Tony.

SELECT Count(*) AS [Total Entered], Sum(IIf([OpenClosed],1,0)) AS [Total
Closed], Sum(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:]));

Tony,

I made a table with fields:

Total Entered :Text
OpenClosed : Integer
Date Reported : Date/Time


I entered 7 records, 5 Open and 2 Closed. I ran the query... it worked, but
it returned

[Total Entered] = 7, [Total Closed] = 7, [Total Open] = 7.

<smack> This is right because of the Count() for [OpenClosed]. The IIF()
function returns 0 or 1, so Sum() should be used instead of Count().


Using this query:

SELECT Count(*) AS [Total Entered], SUM(IIf([OpenClosed]=2,1,0)) AS [Total
Closed], SUM(IIf([OpenClosed]=1,1,0)) AS [Total Open] FROM [Main Issue Entry
Sheet] WHERE ((([Main Issue Entry Sheet].[Date Reported]) Between [Enter
start date:] And [Enter end date:]));


returned

[Total Entered] = 7, [Total Closed] = 2, [Total Open] = 5


If you still get an error, check field names for spelling errors. If it
still won't run, create a new query. Add the table. Drag down the date field,
then type in "Count(*) AS [Total Entered]". Run the query.

When that runs without an error, add "SUM(IIf([OpenClosed]=2,1,0)) AS [Total
Closed]" in the next column of the grid.

When the query runs without errors, add the last field
"SUM(IIf([OpenClosed]=1,1,0)) AS [Total Open]".


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Steve, Thanks for your help, it still will not run the query, it now comes
back with the following message: "This expression is typed incorrectly or it
is too complex to be evaluated, for example a numeric expression may contain
too many complicated elements, try to simplify the expression by assigning
parts of the expression to variables"

Thanks Tony.

Hi Tony,

The problem is with the immediate IF() function. The syntax is:

IIF(Condition, Value_If_True, Value_If_False)

where condition evaluates to TRUE or FALSE.

for the OPEN, use:
IIF([OpenClosed] = 1 , 1, 0)

and for CLOSED, use
IIF([OpenClosed] = 2 , 1, 0)


Try using this:

SELECT Count(*) AS [Total Entered], Count(IIf([OpenClosed]=2,1,0)) AS [Total
Closed], Count(IIf([OpenClosed]=1,1,0)) AS [Total Open] FROM [Main Issue
Entry Sheet] WHERE ((([Main Issue Entry Sheet].[Date Reported]) Between
[Enter start date:] And [Enter end date:]));






Also, you shouldn't use spaces in object names. It causes you headaches...
see "The Ten Commandments of Access" at

http://www.mvps.org/access/tencommandments.htm




HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

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:]));

 

[Steve Sanford]

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

 

[blake7]

Hi Steve, yes I can see your reasoning now, I am new to writing code and i am
just discovering all the numerous way to string code together, its very
confusing !!. I am looking for a book to introduce me to the world of writing
code, but have not yet found one that explains it in a way that a newbie like
me can get to grips with, any suggestions ???
Once again thnaks for all your help. Regards Tony

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.)

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:]));