The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation in incorrect? (1 Viewer)

[spet]

Good Day to all and Thank you in Advance,

I am stumped on why I am receiving the following error message.

The select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation in incorrect?

My code is as follows:

stSQL = "SELECT Sum(CheckStubInfo.[BF Amt]) AS [SumOfBF Amt], Sum(CheckStubInfo.[Protein Amt]) AS [SumOfProtein Amt], Sum(CheckStubInfo.[OS Amt]) AS [SumOfOS Amt], Sum(CheckStubInfo.[PPD Amt]) AS [SumOfPPD Amt], Sum(CheckStubInfo.[SCC Amt]) AS [SumOfSCC Amt], Sum([BF Amt])+Sum([Protein Amt])+Sum([OS Amt])+Sum([PPD Amt])+Sum([SCC Amt]) AS MilkValue, Round(Sum([BF Lbs])/Sum([Milk LBS])*100,4) AS AvgBF, Round(Sum([Protein Lbs])/Sum([Milk LBS])*100,4) AS AvgPro, Round(Sum([OS Lbs])/Sum([Milk LBS])*100,4) AS AvgOS, Sum(CheckStubInfo.[Milk Lbs]) AS [SumOfMilk Lbs], Sum(CheckStubInfo.[Gross Pay]) AS [SumOfGross Pay]" _
& "Round(Sum([Gross Pay])/Sum([Milk Lbs])*100,4) AS [Avg Price], Sum(CheckStubInfo.[AdditionalPayments]) AS MiscAdj, Sum(CheckStubInfo.[Quality Amt]) AS Quality, Sum(CheckStubInfo.[Volume Amt]) AS Vol, Sum(CheckStubInfo.[Pickup Amt]) AS HaulChrg, Sum(CheckStubInfo.[Purchases]) AS Purch, Sum(CheckStubInfo.[Deductions]) AS Deduct, Sum(CheckStubInfo.[Estimated Check]) AS [Guess Check], Sum(CheckStubInfo.[Net Check]) AS [NetChk] FROM CheckStubInfo WHERE CheckStubInfo.Date = #" & DLookup("LastDay", "SetPayrollDates") & "# AND CheckStubInfo.Grade = 'A' ;"

 

[Uncle Gizmo]

Sum(CheckStubInfo.[Gross Pay]) AS [SumOfGross Pay]" _

& "Round(Sum([Gross Pay])/Sum([Milk Lbs])*100,4) AS [Avg Price],



'Should be:-



Sum(CheckStubInfo.[Gross Pay]) AS [SumOfGross Pay], " _

& "Round(Sum([Gross Pay])/Sum([Milk Lbs])*100,4) AS [Avg Price],

 

[moke123]

AS [SumOfGross Pay]" _
& "Round(Sum([Gross Pay])/Sum([Milk Lbs]

Check spaces like between pay] and Round.
what you have will resolve to - Pay]Round

 

[spet]

Sum(CheckStubInfo.[Gross Pay]) AS [SumOfGross Pay]" _

& "Round(Sum([Gross Pay])/Sum([Milk Lbs])*100,4) AS [Avg Price],



'Should be:-



Sum(CheckStubInfo.[Gross Pay]) AS [SumOfGross Pay], " _

& "Round(Sum([Gross Pay])/Sum([Milk Lbs])*100,4) AS [Avg Price],

Thank you so very much! I've been starring at this code for hours! DUHHHHHHHHH moment for me!

 

[Uncle Gizmo]

Easier to Read like this:-

SELECT Sum([BF Amt]) AS [SumOfBF Amt],
    Sum([Protein Amt]) AS [SumOfProtein Amt],
    Sum([OS Amt]) AS [SumOfOS Amt],
    Sum([PPD Amt]) AS [SumOfPPD Amt],
    Sum([SCC Amt]) AS [SumOfSCC Amt],
    Sum([BF Amt]) + Sum([Protein Amt]) + Sum([OS Amt]) + Sum([PPD Amt]) + Sum([SCC Amt]) AS MilkValue,
    Round(Sum([BF Lbs]) / Sum([Milk LBS]) * 100, 4) AS AvgBF,
    Round(Sum([Protein Lbs]) / Sum([Milk LBS]) * 100, 4) AS AvgPro,
    Round(Sum([OS Lbs]) / Sum([Milk LBS]) * 100, 4) AS AvgOS,
    Sum([Milk Lbs]) AS [SumOfMilk Lbs],
    Sum([Gross Pay]) AS [SumOfGross Pay],
    Round(Sum([Gross Pay]) / Sum([Milk Lbs]) * 100, 4) AS [Avg Price],
    Sum([AdditionalPayments]) AS MiscAdj,
    Sum([Quality Amt]) AS Quality,
    Sum([Volume Amt]) AS Vol,
    Sum([Pickup Amt]) AS HaulChrg,
    Sum([Purchases]) AS Purch,
    Sum([Deductions]) AS Deduct,
    Sum([Estimated Check]) AS [Guess Check],
    Sum([Net Check]) AS NetChk
FROM CheckStubInfo
WHERE DATE = # " & DLookup(" LastDay ", " SetPayrollDates ") & " #
    AND Grade = 'A';

Done with:- https://poorsql.com/

 

[Uncle Gizmo]

And there is an online SQL validator here:- https://www.eversql.com/sql-syntax-check-validator/

Which is very helpful for checking that you've got the brackets and commas in the right places

 

[The_Doc_Man]

The punctuation is incorrect here on another count:

AS [SumOfGross Pay]" _
& "Round(Sum([Gross Pay])/Sum([Milk Lbs]

You have no comma between the close bracket of the AS clause and the Round function that is your next argument. You need either a comma or a math operator or a string operator or SOMETHING to act as a list separator between the two arguments.