Struggling

[mafhobb]

Hi

I am struggling with this query. The message box simply returns the sql text itself, not the count result. All I need is the count of the field "warranty" when the SKU equals a specific value and "warranty" = 1.

       SQL = "SELECT Count(ReturnInfo.Warranty) AS CountOfWarranty FROM Calls INNER JOIN ReturnInfo ON Calls.CallID = ReturnInfo.CallID GROUP BY Calls.SKU, ReturnInfo.Warranty HAVING (((Calls.SKU)='" & SKUList!SKU & "') AND ((ReturnInfo.Warranty)=[1]))"

    MsgBox SQL

I have verified that this returns a single number in the query design window, so what am I doing wrong in this VBA code?

mafhobb

 

[Minty]

You have created a SQL Query String but are not using it, except to display the String with your msgbox.

You need to run the query (open a recordset) and then count the records it returns. Something like

    Dim rs               As Recordset
    Dim sSQL             As String
    Dim iRecCount        As Long


    sSQL = "SELECT Count(ReturnInfo.Warranty) AS CountOfWarranty FROM Calls INNER JOIN ReturnInfo ON Calls.CallID = ReturnInfo.CallID GROUP BY Calls.SKU, ReturnInfo.Warranty HAVING (((Calls.SKU)='" & SKUList!SKU & "') AND ((ReturnInfo.Warranty)=[1]))"

    Debug.Print sSQL

    Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
    iRecCount = 0
    If Not rs.EOF Then
        rs.MoveFirst
        iRecCount = rs("CountOfWarranty")
    End If

    MsgBox "You have " & iRecCount & " records", vbInformation, "Record count!"