DCount with multiple criteria (1 Viewer)

[Eikenhorst]

Hello, I’m working on a complaints database for my traineeship. I use two forms, in the first form basic values like the Complaints_number, Custumer_name, Productcode, and Sort_complaint are entered and saved to the Complains table.
The second form is filled by the departments responsible for the complaint. They have to fill in the actions they are going to take on this complaint and how to prevent it. The information typed in the first form is being loaded to the second form by bounding some fields to a SQL query.

Now I would like to have a notification message in a MsgBox if this complain is a repeating complaint. This means if the same customer had the same sort complaint before for the same product. This MsgBox also needs to show the number of repeating complaints.

To make this happen I tried the following code:
Private Sub Form_Activate()
Dim REPEATCOUNT As Byte
REPEATCOUNT = DCount(“Complaints_number”, “Complaints”, “Custumer_name=” & Textcustumer_name AND “Productcode=” & Textproductcode AND “Sort_complaint=” & Textsort_complaint)
If REPEATCOUNT > 0 Then
MsgBox “This is a repeat complaint! This complaint has showed (REPEATCOUNT) times before. Extra attention please!”
End If
End Sub

Unfortunately, this doesn’t work. There is something wrong in the DCount function, it gives the following error message: Run-time error ‘13’; Type Mismatch. It is something in the criteria part, without it, it works.
I have tried to test the (REPEATCOUNT) in the MsgBox window but this also doesn’t work correctly, it shows “This complaint has showed (REPEATCOUNT) times before.” instead of the number.

I hope someone can help me with these problems. I assume that they aren’t that hard to solve but I’m not that experienced with VBA to see how. Any help is greatly appreciated!

 

[llkhoutx]

The criteria in your DCOUNT call is not concantenated correctly. Textcustumer_name is a field, not text.

Text data must be enclosed with a quote, tic or chr(34). Numberic data is not enclosed. You're criterias appears to include text.

For your

REPEATCOUNT = DCount(“Complaints_number”, “Complaints”, “Custumer_name=” & Textcustumer_name AND “Productcode=” & Textproductcode AND “Sort_complaint=” & Textsort_complaint)

Try

REPEATCOUNT = DCount(“Complaints_number”, “Complaints”, “Custumer_name = ”  & chr(34) &  Textcustumer_name & chr(34) & " AND “Productcode = ” & Textproductcode &  " AND Sort_complaint = ” & Textsort_complaint)

This assumes that Textproductcode and Textsort_complaint are numeric and that Textcustumer_name is text.

Textcustmer_name? Bad spelling will cause you problems down the road.

 

[Sharkiness]

Hi,

I am having a problem with a similar issue. I would like to search a customer table for forename and surname before adding to a table.

The problem i have is regardless of whether there is an entry in the fields or if the forename and surname doesnt exist, I am getting the msgbox to say that the information already exists when i know it definitely doesnt.

THis then stops me being able to add it the new data to the database. I have attached my code.

[SIZE=3][FONT=Times New Roman]Private Sub cmdAdd_Click()[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]Dim strSQLAdd As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim strSQLChk As String[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  strSQLChk = DCount("*", "tblCustomers", "Forename=" & Chr(34) & txtForename & Chr(34) & "And Surname =  & Chr(34) & txtSurname & Chr(34)" > 0)[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  strSQLAdd = "INSERT INTO tblCustomers(CustomerID, Title, Forename, MiddleName, Surname, DOB, REF1, Status, REF2)" & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      "VALUES ('" & Me.txtCustomerID & "','" & Me.txtTitle & "','" & Me.txtForename & "','" & Me.txtMiddleName & "','" & Me.txtSurname & "','" & Me.txtDOB & "','" & Me.txtREF1 & "','" & Me.txtStatus & "','" & Me.txtREF2 & "');"[/FONT][/SIZE]
 
 
[SIZE=3][FONT=Times New Roman]   If strSQLChk > 0 Then[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]      MsgBox "The information provided already exists in the database"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  Else[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  DoCmd.RunSQL strSQLAdd[/FONT][/SIZE]

I know the strSQLAdd works, I just dont seem to be able to get the strSQLChk to work properly which stops the add kicking in.

Any help would be greatly appreciated