Count Function

  • Thread starter Thread starter blake7
  • Start date Start date
B

blake7

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))
 
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.
 
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 said:
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 said:
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))
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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))
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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))
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


:

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))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top