Using Sum Function on SQL Query

I am using 1 table "QuotesFile" this table have a column net price, i am including this new column

"CSCC-O-V" which will contain the sum of all field where condition is true, see attached code.

I got this error

My original query is working fine, see orginal code attached


CSCC-O-V: Sum(Net Price(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC')))
 
Original query
 
SELECT UCase([Created By User Name]) & " - " & [Created By User Type] AS Name, Sum(IIf([Created By Source]="CSCC",1,0)) AS [CSCC-Q], Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC',1,0)) AS [CSCC-O], Sum(IIf([Created By Source]="IQT",1,0)) AS [IQT-Q], Sum(IIf(QuotesFile![Created By Source]='IQT' And QuotesFile![Quote Status]='QC',1,0)) AS [IQT-O], Sum(IIf([Created By Source]="SCC",1,0)) AS [SCC-Q], Sum(IIf(QuotesFile![Created By Source]='SCC' And QuotesFile![Quote Status]='QC',1,0)) AS [SCC-O]
FROM QuotesFile
WHERE (((QuotesFile.[Created By User Partner])=[Forms]![frmReports]![cboPartner]) AND ((QuotesFile.[Creation Date]) Between [Forms]![frmReports]![txtStartDate] And [Forms]![frmReports]![txtEndDate]))
GROUP BY UCase([Created By User Name]) & " - " & [Created By User Type], LCase([Created By User]) & " - " & [Created By User Type];

Open in new window

error.JPG
gfrancoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aesmikeCommented:
your IIF expression doesn't make sense to Access.
Your IIF needs 3 arguements:
1. Condition, currently QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC'
2. Value to return if true.  Currently, nothing
3. Value to return if false.  Currently, nothing
You are missing the 2nd and 3rd arguements.
0
Jim P.Commented:
Try it as:

CSCC-O-V: Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC'), [Net Price],0)
 
0
Jim P.Commented:
Oh and these should probably be a period instead.

QuotesFile.[Created By Source]
QuotesFile.[Quote Status]
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

gfrancoAuthor Commented:
I tried using your function and also this
CSCC-O-V: Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC'),QuotesFile! [Net Price],0)
I just need to be sum net price for this condition. thanks

But is not working i got an error see file attached

error.JPG
0
Jim P.Commented:
I'm assuming [Net Price] is a field in a table/query. (I won't discuss spaces in table and field names.)

I couldn't read the error message in your second screen shot. It's just pixelated.

Try it as:
CSCC-O-V: Sum(IIf(QuotesFile.[Created By Source]='CSCC' And QuotesFile.[Quote Status]='QC',QuotesFile.[Net Price],0))

Open in new window

0
gfrancoAuthor Commented:
No, It was working with !, because it is the reference to the field.
As i told you before, my originally query is working Perfect (see attached)

I just need to include another field that sum the field Net price where the condition is met, but when I used this
CSCC-O-V: Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC'),QuotesFile! [Net Price],0) I got the same error, that I posted.

Thanks
SELECT UCase([Created By User Name]) & " - " & [Created By User Type] AS Name, 
Sum(IIf([Created By Source]="CSCC",1,0)) AS [CSCC-Q], 
Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC',1,0)) AS [CSCC-O], 
Sum(IIf([Created By Source]="IQT",1,0)) AS [IQT-Q], 
Sum(IIf(QuotesFile![Created By Source]='IQT' And QuotesFile![Quote Status]='QC',1,0)) AS [IQT-O], 
Sum(IIf([Created By Source]="SCC",1,0)) AS [SCC-Q], 
Sum(IIf(QuotesFile![Created By Source]='SCC' And QuotesFile![Quote Status]='QC',1,0)) AS [SCC-O]
FROM QuotesFile
WHERE (((QuotesFile.[Created By User Partner])=Forms!frmReports!cboPartner) And 
((QuotesFile.[Creation Date]) Between Forms!frmReports!txtStartDate And Forms!frmReports!txtEndDate))
GROUP BY UCase([Created By User Name]) & " - " & [Created By User Type], LCase([Created By User]) & " - " & [Created By User Type];

Open in new window

0
gfrancoAuthor Commented:
It seems working, so let me know How can I format to a currency number?
$ 99,999,00

formating.JPG
0
Jim P.Commented:
Format(Sum(IIf(QuotesFile![Created By Source]='CSCC' And QuotesFile![Quote Status]='QC',QuotesFile! [Net Price],0)),"Currency")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gfrancoAuthor Commented:
Thanks, what is the reason when I got the format currency through design panel, i did not see the sentence on the sql query as you show me.

This is my solution, thanks
0
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.