Full Version: Calculation Problem
drmojo418
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
Dom DXecutioner
hi,

try the following...

CODE
SALES: Sum(IIF(Nz([ACCallResultCode]) Like 'S*',1,0))
fredrisg

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
drmojo418
that worked thanks

drmojo418
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
drmojo418
bump
Jack Cowley
SALES: Sum(IIf(Nz([ACCallResultCode]) Like 'S*' Or Nz([ACCallResultCode]) Like 'N*',1,0))

This is untested but should be close...

Jack
drmojo418
that worked thanks
Jack Cowley
You are welcome!

Jack
drmojo418
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
Jack Cowley
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
drmojo418
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