lets say i have this expression in my query

SALES: Sum(IIf(nz([ACCallResultCode])='S02',1,0))

but what i need it to do is calculate all the codes that start with an "S" in one field so for instant i have the following codes: S01, S02, S88, AND S07

how would i write my formula to look for all the S codes instead of just one at a time?

thanks
chad

hi,

try the following...

Chad,

Just a quick thought . . .

You may wish to run a subqry that checks your result codes using the left function:

iif(Left([ACCallResultCode],1)="S",Sum(IIf(nz([ACCallResultCode])='S02',1,0)))

so it can get all the S codes . . . in this qry and then embed it back into your main qry.

Or maybe a subqry that looks at left([ACCallResultCode],1) with a criteria of "S".

Steve

that worked thanks

chad

now if i wanted to add to types of codes in one formula whould i just have to put it like this:

SALES: Sum(IIf(Nz([ACCallResultCode]) Like 'S*' or 'N*',1,0))

Is that correct.

thanks
chad

bump

SALES: Sum(IIf(Nz([ACCallResultCode]) Like 'S*' Or Nz([ACCallResultCode]) Like 'N*',1,0))

This is untested but should be close...

Jack

that worked thanks

You are welcome!

Jack

on further review of the code it doens't seem to be doing what it should be doing.
here is the code i am using to try to get my numbers:

NoSales: Sum(IIf(nz([ACCallResultCode]) Like "n01" Or Nz([ACCallResultCode]) Like "n02" Or Nz([ACCallResultCode]) Like "N03" Or Nz([ACCallResultCode]) Like "N04" Or Nz([ACCallResultCode]) Like "N05" Or Nz([ACCallResultCode]) Like "N06" Or Nz([ACCallResultCode]) Like "N09" Or Nz([ACCallResultCode]) Like "N10" Or Nz([ACCallResultCode]) Like "N20" Or Nz([ACCallResultCode]) Like "N21" Or Nz([ACCallResultCode]) Like "N23" Or Nz([ACCallResultCode]) Like "N24",1,0))

but it is not showing the accurate number.

if i was to add up the "N" code manually it gives me the right number here is the criteria i am using in another query to get the right numbers:

field: ACCallResultCode
Table: dbo_TdiAgentCallResults
Total: where

criteria: "n02" Or "n03" Or "n04" Or "n05" Or "n06" Or "n10" Or "n20" Or "n21" Or "n23" Or "n24"

why would this one be working but not the top one?

thanks
chad

I am not sure, but try using the = sign instead of Like and see if that helps. Also, why not use the criteria instead of the diifficult to write IIF() statement? Also, are any of your ACCallResultCodes actually null that you need to use the Nz() function?

Jack

Nope none are null i went ahead and took out the nz() that didn't do anything i also changed the "likes" to "=" and that didn't change anything. the reason i can't use the criteria is i have other fields that i need to sum as well like number of sales, deletes, and so on. i was just trying to only make one query instead of one for each field i need like sales, deletes, no sales and so on.

thanks
chad

forgot to hit the reply by your name

I also forgot to mention that i can't count the code "N07" AND "N08" the mean something diffrent then what i am trying to calculate that is whay i can just do the "N*"

This is all very confusing and at this point I all I could do would be to try and do what you are doing, which is to use code and trial and error to get it to work. I am off for the morning, but if you have a sample db you can post I willl take a look at it this afternoon and see if I can help you find a solution....

Jack