ROUND Function in Query

[spet]

Hello, I am receiving the error from my Query below: "Wrong number of arguments used with function in query expression 'Sum(Round([BFPercent]*[Weight]),2) AS BFPounds'. If I remove the ,2 from it the query executes just fine.


I thought the ROUND function allows for that additional parameter to define where the rounding shall occur?





INSERT INTO CheckStubInfo ( Grade, PatronNo, [BF%], [Protein%], [OS%], Bacteria, SCC, [Milk Lbs], [date], [BF Lbs], [Protein Lbs], [OS Lbs] )
SELECT Patron.PatronGrade, Patron.PatronNumber AS Main, [Premium Info].BFPercent, [Premium Info].ProteinPercent, [Premium Info].OSSPercent, [Premium Info].Bacteria, [Premium Info].SCC, Sum(PatronWeights.Weight) AS SumOfWeight, [Premium Info].Date, Sum(Round([BFPercent]*[Weight]),2) AS BFPounds, Sum(Round([ProteinPercent]*[Weight]),2) AS ProteinPounds, Sum(Round([OSSPercent]*[Weight]),2) AS OSPounds
FROM [Premium Info] RIGHT JOIN (PatronWeights LEFT JOIN Patron ON PatronWeights.PatronNo = Patron.PatronNumber) ON [Premium Info].PatronNo = PatronWeights.PatronNo
WHERE (((PatronWeights.PickUpDate) Between [Please enter a beginning pickup date:] And [Please enter an ending pickup date:]) AND (([Premium Info].Date)=[Please enter an ending pickup date:]))
GROUP BY Patron.PatronGrade, Patron.PatronNumber, [Premium Info].BFPercent, [Premium Info].ProteinPercent, [Premium Info].OSSPercent, [Premium Info].Bacteria, [Premium Info].SCC, [Premium Info].Date, Patron.SplitPatron1, Patron.SplitPatron2, Patron.SplitPatron3
HAVING (((Patron.PatronNumber) Is Not Null) AND ((Patron.SplitPatron1) Is Null) AND ((Patron.SplitPatron2) Is Null) AND ((Patron.SplitPatron3) Is Null) AND (([Premium Info].date)=[Please enter an ending pickup date:]));

 

[isladogs]

Bracketing error. It should be Sum(Round([BFPercent]*[Weight],2)) AS BFPounds

or possibly Round(Sum([BFPercent]*[Weight]),2)

BTW - you have the same issue on several of your query fields

 

[spet]

I have tried both of these solution on all 3 fields that I am rounding and still receiving the same error message.

 

[isladogs]

Really? Did you check the bracketing carefully in each case - both are different.
Also check the datatypes for each field

If you still get an error, suggest you supply some example data

 

[Mark_]

To resolve your issue, I'd recommend the following;

creating a query that selects on one table for the two fields you want to SUM.
Add in an expression to do your rounded calculation
Update to do the SUM as you need.

Look at your query's SQL. This will show you how you need to format your existing SQL string to match.

 

[spet]

Just an update:


I have been working with a database that was already created and I'm doing modifications to it. I have been trying over and over to use the Round function with no luck. What I found was that there was a function called "Round" in the General Procedures module. That module wasn't expecting any parameters, so when I executed the Round function I was erroring out.


Thank you to all that replied!

 

[June7]

A custom function named same as an intrinsic function? Asking for trouble.

 

[spet]

Right! What a headache....took me 3 days to figure it out.


I thought I was going crazy.

 

[June7]

If it doesn't have arguments, how does it work? What exactly does it do?

 

[The_Doc_Man]

First things first: Since it appears in a General Module as a function, you should find all references to it, which a search can do from the VBA window (search entire project). Once you have found them all and reviewed them for applicability, you can do a FIND & REPLACE operation to rename that function. THEN you can include your intended ROUND operation when and where you want it.

As noted by others, misuse of reserved words and external function names can cause you a TON of headaches. Case in point...

 

[June7]

Access cannot do a search of query or textbox expressions. That requires writing elaborate code or acquiring a 3rd party add-in such as V-Tools. I used to use Rick Fisher's Find and Replace.

 

[Mark_]

So what does this custom "Round" do? That may help you find where it is used that isn't obvious.