SQL Problem, sorting on calculated field?

[Walshie]

Morning,

I have the following sql in vba to add up all the values of a column (tblscrap.transqty). I then want to sort on this criteria. I have put this into the query and the query works fine, so i've copied the sql into VBA but get the following error Run-time error '3061'. Too few parameters. Expected 1

Here's the code:

STRSQL = "Select DISTINCTROW [item-no], Description1, Sum(TransQty) AS [TotalTransQty],(dLookup(""Unit"", ""tblCost"", ""[item#]="" & [Item-no])*SUM(TRANSQTY)) AS [Value] FROM tblScrap WHERE  (((tblScrap.[Reason Code])='scrp') AND ((tblScrap.[trans-date]) Between #" & [Forms]![frmMain]![txtFrom] & "# And #" & [Forms]![frmMain]![txtTo] & "#))GROUP BY [item-no], Description1 ORDER BY DLookUp(""Unit"",""tblCost"",""[item#]="" & [Item-no])*[Total];"
 
    With CurrentDb.OpenRecordset(STRSQL)

It's just the ORDER BY at the end that causes this error, if i sort by any other column the results are as they should be

Would really appreciate any help

Cheers
Chris

 

[Walshie]

Resolved:

I changed the code to the following and it worked:

STRSQL = "Select DISTINCTROW [item-no], Description1, Sum(TransQty) AS [TotalTransQty],(dLookup(""Unit"", ""tblCost"", ""[item#]="" & [Item-no])*SUM(TRANSQTY)) AS [Value] FROM tblScrap WHERE  (((tblScrap.[Reason Code])='scrp') AND ((tblScrap.[trans-date]) Between #" & [Forms]![frmMain]![txtFrom] & "# And #" & [Forms]![frmMain]![txtTo] & "#))GROUP BY [item-no], Description1 ORDER BY dLookup(""Unit"", ""tblCost"", ""[item#]="" & [Item-no])*SUM(TRANSQTY);"

    With CurrentDb.OpenRecordset(STRSQL)
 
Cheers
Chris