Prev: Currency
Next: Access 2007 runtime - how to switch off confirm action queries
From: blake7 on 25 May 2010 09:08
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))

From: Douglas J. Steele on 25 May 2010 09:16
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" <blake7(a)discussions.microsoft.com> wrote in message
news:6AAF9FEC-B596-40D3-B3F8-9AAD82A97FA8(a)microsoft.com...
> 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))
>


From: golfinray on 25 May 2010 09:54
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))
>
From: blake7 on 25 May 2010 10:38
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))
> >
From: golfinray on 25 May 2010 10:57
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))
> > >
 |  Next  |  Last
Pages: 1 2
Prev: Currency
Next: Access 2007 runtime - how to switch off confirm action queries