I renamed the field To take the Space out. Now I'm Getting a type mismatch error
Code:
Public Function QOH(rNbr As String) As Variant
On Error GoTo Err_Handler
'Purpose: Calculate Quantity on Hand
'Return: Number, or 0 if no matches.
'Arguments: rNbr = Inventory Key to match Purchase & Sales Records to.
'
'Notes: 1. Use square brackets around field/table names with spaces or odd characters.
' 2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
' 3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
' 4. Returning more than 255 characters to a recordset triggers this Access bug:
' http://allenbrowne.com/bug-16.html
Dim RecPur As DAO.Recordset 'Related Purchase records
Dim RecSal As DAO.Recordset 'Related Sales Records
Dim strSqlPur As String 'SQL statement
Dim strSqlSal As String 'SQL statement
Dim SumPur As Variant
Dim SumSal As Variant
'Initialize to Null
QOH = 0
SumPur = 0
SumSal = 0
'Build SQL strings, and get the records.
'Purchase quantity
strSqlPur = "SELECT Purchases.InventoryNumber, Purchases.Qty FROM Purchases WHERE Purchases.InventoryNumber = '" & rNbr & "'"
Set RecPur = DBEngine(0)(0).OpenRecordset(strSqlPur, dbOpenDynaset)
'Loop through the matching records
Do While Not RecPur.EOF
If Not IsNull(RecPur(0)) Then
SumPur = SumPur + RecPur(0)
End If
RecPur.MoveNext
Loop
' Sales Quantity
strSqlSal = "SELECT Sales.Number, Sales.Quantity FROM Sales WHERE Sales.Number ='" & rNbr & "'"
Set RecSal = DBEngine(0)(0).OpenRecordset(strSqlSal, dbOpenDynaset)
'Check if there were no Sales.
If IsNull(RecSal) Then
SumSal = 0
Else
'Loop through the matching records
Do While Not RecSal.EOF
If Not IsNull(RecSal(0)) Then
SumSal = SumSal + RecSal(0)
End If
RecSal.MoveNext
Loop
End If
QOH = SumPur - SumSal
RecPur.Close
RecSal.Close
Exit_Handler:
'Clean up
Set RecPur = Nothing
Set RecSal = Nothing
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "QOH()"
Resume Exit_Handler
End Function