Prev: Currency
Next: Access 2007 runtime - how to switch off confirm action queries
From: Douglas J. Steele on 25 May 2010 10:57
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" <blake7(a)discussions.microsoft.com> wrote in message
news:42FAC61B-5358-45DA-8030-66399F581ED7(a)microsoft.com...
> 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: Douglas J. Steele on 25 May 2010 11:40
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" <golfinray(a)discussions.microsoft.com> wrote in message
news:113DA324-8390-4ED6-9A4B-E4400A95B65C(a)microsoft.com...
> 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))
>> > >


First  |  Prev  | 
Pages: 1 2
Prev: Currency
Next: Access 2007 runtime - how to switch off confirm action queries