criteria to sum same product for one person (1 Viewer)

basilyos

Registered User.
Local time
Today, 08:28
Joined
Jan 13, 2014
Messages
246
hello


i want my database to tell me when the same person bought the same product 20 times


Code:
If DCount("*", "tbl_Personal_Sanctions_02", "[PID] = " & [PID]) = 0 Then
                MsgBox ("congratulations")
            Else
            End If
the product field is (Sanction_Code)
the type of this field is lookup so i can choose more than one product
the pid is the customers id



what to add to my code to get what i want



how to check
 

June7

AWF VIP
Local time
Today, 07:28
Joined
Mar 9, 2014
Messages
3,886
Maybe you should use => 20 instead of = 0

If DCount("*", "tbl_Personal_Sanctions_02", "[PID] = " & [PID] & " AND [Sanction_Code]=" & Sanction_Code) => 20 Then
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:28
Joined
May 7, 2009
Messages
15,037
create a user-defined function and pass the PID number to check with the function:
Code:
Public Function fnCountProduct(pid As Long)

    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim strProd As String
    
    Const tbl_name As String = "tbl_Personal_Sanctions_02"
    
    Set db = CurrentDb
    
    Set rs = db.OpenRecordset("select sanction_code.value, count(sanction_code.value) as cnt from [" & tbl_name & "] where [pid]=" & pid & " " & _
                    "group by sanction_code.value having count(sanction_code.value) > 19;")
                    
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            strProd = strProd & rs(0) & vbCrLf
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    If Len(strProd) > 0 Then
        MsgBox "You have brought this/these products 20 or more times:" & vbCrLf & vbCrLf & strProd, vbInformation + vbOKOnly
    End If
End Function

syntax:

fnCountProduct(ProductID)
 

Users who are viewing this thread

Top Bottom