Error in Function

[Alex Motilal]

Hi Everybody,



The following function returns 0 as average transport rate per bale:

Public Function GetAvgBlTrspRate(HeapNumber As Integer) As Double
Dim MyString As String
MyString = "SELECT (Sum([tblBaleShiftingDelivery]![balesShifted]*[tblBaleShiftingDelivery]![transpRtForShifting]))/(Sum([tblBaleShiftingDelivery]![balesShifted])) AS AvgTraspPerBale "
MyString = MyString & "FROM tblHeap INNER JOIN (tblPressing INNER JOIN tblBaleShiftingDelivery ON tblPressing.pressingID = tblBaleShiftingDelivery.lotNo) ON tblHeap.heapID = tblPressing.heapGenerated "
MyString = MyString & "WHERE (((tblHeap.heapID)=[HeapNumber]));"
GetAvgBlTrspRate = MySring
End Function

Can anyone point out what mistake I am making?

With thanks
Alex

[June7]

First of all, you are just building an SQL statement string, you aren't actually running a query. Running a SELECT query in VBA requires opening a recordset.

Second, I am surprised it returns anything. You declare the function as Double but then set it to a string variable. Also, the variable is misspelled in the last line. Put 'Option Explicit' line in the module header and misspellings like that will be revealed.

[Alex Motilal]

Thanks June7 for pointing out the mistake.

Regards,
Alex