Count Function

Hi all, I have a options group on my form with 4 option buttons, there values
are 1,2,3 & 4 being saved in my table column Currentstatus, I am trying to
count the various input using the code below, but it does not add up whats in
my main table, it is sometimes doubling the amounts it finds, any help?b
Thanks.

Total Entered: Count(*)
Total approved: Sum(IIf([currentstatus],1,0))
Total Reject: Sum(IIf([currentstatus],2,0))
Total NA: Sum(IIf([currentstatus],3,0))
Total TA: Sum(IIf([currentstatus],4,0))

Your IIf statements are incorrect. Try

Total approved: Sum(IIf([currentstatus]=1,1,0))
Total Reject: Sum(IIf([currentstatus]=2,1,0))
Total NA: Sum(IIf([currentstatus]=3,1,0))
Total TA: Sum(IIf([currentstatus]=4,1,0))


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"blake7" wrote in message
...
Hi all, I have a options group on my form with 4 option buttons, there
values
are 1,2,3 & 4 being saved in my table column Currentstatus, I am trying to
count the various input using the code below, but it does not add up whats
in
my main table, it is sometimes doubling the amounts it finds, any help?b
Thanks.

Total Entered: Count(*)
Total approved: Sum(IIf([currentstatus],1,0))
Total Reject: Sum(IIf([currentstatus],2,0))
Total NA: Sum(IIf([currentstatus],3,0))
Total TA: Sum(IIf([currentstatus],4,0))

Count needs to include filed name:
Count([yourfieldname])
IIF statements require something defined like:
sum(IIF([yourfieldname]=something,1,0)
If the 1,2,3,4 are text they must be in quotes.
--
Milton Purdy
ACCESS
State of Arkansas


"blake7" wrote:

Hi all, I have a options group on my form with 4 option buttons, there values
are 1,2,3 & 4 being saved in my table column Currentstatus, I am trying to
count the various input using the code below, but it does not add up whats in
my main table, it is sometimes doubling the amounts it finds, any help?b
Thanks.

Total Entered: Count(*)
Total approved: Sum(IIf([currentstatus],1,0))
Total Reject: Sum(IIf([currentstatus],2,0))
Total NA: Sum(IIf([currentstatus],3,0))
Total TA: Sum(IIf([currentstatus],4,0))

Hi Both, thanks for the reply, I tried both suggestions and I get the same
error message for both "Data type mismatch in criteria expression" ? any
other suggestions guys. Thanks

"golfinray" wrote:

Count needs to include filed name:
Count([yourfieldname])
IIF statements require something defined like:
sum(IIF([yourfieldname]=something,1,0)
If the 1,2,3,4 are text they must be in quotes.
--
Milton Purdy
ACCESS
State of Arkansas


"blake7" wrote:

Hi all, I have a options group on my form with 4 option buttons, there values
are 1,2,3 & 4 being saved in my table column Currentstatus, I am trying to
count the various input using the code below, but it does not add up whats in
my main table, it is sometimes doubling the amounts it finds, any help?b
Thanks.

Total Entered: Count(*)
Total approved: Sum(IIf([currentstatus],1,0))
Total Reject: Sum(IIf([currentstatus],2,0))
Total NA: Sum(IIf([currentstatus],3,0))
Total TA: Sum(IIf([currentstatus],4,0))

In a datatype error, you are trying to perform an operation on the wrong
datatype. Like, trying to add or subtract text instead of numbers or trying
to type numbers as text. Look at the datatypes in your table. Number, text,
memo, etc If you need to operate on text, like IIF([yourfield]=1,1,0) then
put the numbers in quotes like
IIF([yourfield]="1","1","0")
--
Milton Purdy
ACCESS
State of Arkansas


"blake7" wrote:

Hi Both, thanks for the reply, I tried both suggestions and I get the same
error message for both "Data type mismatch in criteria expression" ? any
other suggestions guys. Thanks

"golfinray" wrote:

Count needs to include filed name:
Count([yourfieldname])
IIF statements require something defined like:
sum(IIF([yourfieldname]=something,1,0)
If the 1,2,3,4 are text they must be in quotes.
--
Milton Purdy
ACCESS
State of Arkansas


"blake7" wrote:

Hi all, I have a options group on my form with 4 option buttons, there values
are 1,2,3 & 4 being saved in my table column Currentstatus, I am trying to
count the various input using the code below, but it does not add up whats in
my main table, it is sometimes doubling the amounts it finds, any help?b
Thanks.

Total Entered: Count(*)
Total approved: Sum(IIf([currentstatus],1,0))
Total Reject: Sum(IIf([currentstatus],2,0))
Total NA: Sum(IIf([currentstatus],3,0))
Total TA: Sum(IIf([currentstatus],4,0))

Exactly what did you type that caused the error messages to appear?

What is the data type of currentstate: Text, or Number?

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"blake7" wrote in message
...
Hi Both, thanks for the reply, I tried both suggestions and I get the same
error message for both "Data type mismatch in criteria expression" ? any
other suggestions guys. Thanks

"golfinray" wrote:

Count needs to include filed name:
Count([yourfieldname])
IIF statements require something defined like:
sum(IIF([yourfieldname]=something,1,0)
If the 1,2,3,4 are text they must be in quotes.
--
Milton Purdy
ACCESS
State of Arkansas


"blake7" wrote:

Hi all, I have a options group on my form with 4 option buttons, there
values
are 1,2,3 & 4 being saved in my table column Currentstatus, I am trying
to
count the various input using the code below, but it does not add up
whats in
my main table, it is sometimes doubling the amounts it finds, any
help?b
Thanks.

Total Entered: Count(*)
Total approved: Sum(IIf([currentstatus],1,0))
Total Reject: Sum(IIf([currentstatus],2,0))
Total NA: Sum(IIf([currentstatus],3,0))
Total TA: Sum(IIf([currentstatus],4,0))

Since he's trying to use the IIf statement in a Sum statement, the IIf
statement must return a number, not text.

That means that if [yourfield] is text, you need IIF([yourfield]="1",1,0),
not IIF([yourfield]="1","1","0").

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"golfinray" wrote in message
...
In a datatype error, you are trying to perform an operation on the wrong
datatype. Like, trying to add or subtract text instead of numbers or
trying
to type numbers as text. Look at the datatypes in your table. Number,
text,
memo, etc If you need to operate on text, like IIF([yourfield]=1,1,0) then
put the numbers in quotes like
IIF([yourfield]="1","1","0")
--
Milton Purdy
ACCESS
State of Arkansas


"blake7" wrote:

Hi Both, thanks for the reply, I tried both suggestions and I get the
same
error message for both "Data type mismatch in criteria expression" ? any
other suggestions guys. Thanks

"golfinray" wrote:

Count needs to include filed name:
Count([yourfieldname])
IIF statements require something defined like:
sum(IIF([yourfieldname]=something,1,0)
If the 1,2,3,4 are text they must be in quotes.
--
Milton Purdy
ACCESS
State of Arkansas


"blake7" wrote:

Hi all, I have a options group on my form with 4 option buttons,
there values
are 1,2,3 & 4 being saved in my table column Currentstatus, I am
trying to
count the various input using the code below, but it does not add up
whats in
my main table, it is sometimes doubling the amounts it finds, any
help?b
Thanks.

Total Entered: Count(*)
Total approved: Sum(IIf([currentstatus],1,0))
Total Reject: Sum(IIf([currentstatus],2,0))
Total NA: Sum(IIf([currentstatus],3,0))
Total TA: Sum(IIf([currentstatus],4,0))