ROUND Function in Query (1 Viewer)

spet

Registered User.
Local time
Today, 14:30
Joined
Oct 5, 2018
Messages
38
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

MVP / VIP
Local time
Today, 19:30
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 14:30
Joined
Oct 5, 2018
Messages
38
I have tried both of these solution on all 3 fields that I am rounding and still receiving the same error message.
 

isladogs

MVP / VIP
Local time
Today, 19:30
Joined
Jan 14, 2017
Messages
18,186
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_

Longboard on the internet
Local time
Today, 12:30
Joined
Sep 12, 2017
Messages
2,111
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

Registered User.
Local time
Today, 14:30
Joined
Oct 5, 2018
Messages
38
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

AWF VIP
Local time
Today, 11:30
Joined
Mar 9, 2014
Messages
5,423
A custom function named same as an intrinsic function? Asking for trouble.
 

spet

Registered User.
Local time
Today, 14:30
Joined
Oct 5, 2018
Messages
38
Right! What a headache....took me 3 days to figure it out.


I thought I was going crazy.
 

June7

AWF VIP
Local time
Today, 11:30
Joined
Mar 9, 2014
Messages
5,423
If it doesn't have arguments, how does it work? What exactly does it do?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:30
Joined
Feb 28, 2001
Messages
27,000
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

AWF VIP
Local time
Today, 11:30
Joined
Mar 9, 2014
Messages
5,423
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_

Longboard on the internet
Local time
Today, 12:30
Joined
Sep 12, 2017
Messages
2,111
So what does this custom "Round" do? That may help you find where it is used that isn't obvious.
 

Users who are viewing this thread

Top Bottom