Results 1 to 6 of 6
  1. #1
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48

    I need help about Dcount VBA function for using two criteria

    Hello Everyone,

    I want to get my Dcount formula count the number of records based on two criteria. For that I wrote the code below. However it gives me type mismatch error. Where do I do wrong? Thanks a lot.

    _____________________________________
    Private Sub Komut178_Click()

    Dim variable As String
    variable = DCount("[formname]", "[formusertable]", "[formname] = '" & Me.txtname.Value & "'" And "[userno] = " & Me.numuserno.Value & "")

    MsgBox variable

    End Sub

    _____________________________________

    Formname and userno are the fields of table "formusertable"
    Formname is string while userno is integer.

    And please note that, I have tried those two criteria seperately in the formula, and it runs perfectly like below. But when I combine like above, it is not working.

    variable = DCount("[formname]", "[formusertable]", "[formname] = '" & Me.txtname.Value & "'")


    variable = DCount("[formname]", "[formusertable]", "[userno] = " & Me.numuserno.Value & "")

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Text needs to be surrounded by single quotes or triple quotes (I think) if the text contains a single quote.
    Dates need #, numerics do not need anything.

    Tip: Put the criteria into a string variable, and then you can Debug.Print that variable to see what you get.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by Welshgasman View Post
    Text needs to be surrounded by single quotes or triple quotes (I think) if the text contains a single quote.
    Dates need #, numerics do not need anything.

    Tip: Put the criteria into a string variable, and then you can Debug.Print that variable to see what you get.
    Hello Welshgasman,

    But it is already in a way like you said. there is single quote in text (txtname) and none single quote in integer (numuserno)

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use Debug.Print and not MSGBOX. Then you can copy and paste here.?

    Code:
    Private Sub Komut178_Click()
    
    Dim strCriteria As String
     strCriteria = DCount("[formname]", "[formusertable]", "[formname] = '"  & Me.txtname & "' And [userno] = " &  Me.numuserno )
    
    Debug.Print strCriteria
     
    End Sub
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    DTO. is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    48
    Quote Originally Posted by Welshgasman View Post
    Use Debug.Print and not MSGBOX. Then you can copy and paste here.?

    Code:
    Private Sub Komut178_Click()
    
    Dim strCriteria As String
     strCriteria = DCount("[formname]", "[formusertable]", "[formname] = '"  & Me.txtname & "' And [userno] = " &  Me.numuserno )
    
    Debug.Print strCriteria
     
    End Sub

    Thanks that works

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    In actual fact that was a load of rubbish.

    I meant to say
    Code:
    Private Sub Komut178_Click()
    
    Dim strCriteria As String, iFormCount as Integer
    strCriteria = "[formname] = '"  & Me.txtname & "' And [userno] = " &  Me.numuserno
    Debug.Print strCriteria
    
    iFormCount = DCount("[formname]", "[formusertable]", strCriteria )
    
    
     
    End Sub
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dcount function with multiple criteria
    By eusebio in forum Forms
    Replies: 1
    Last Post: 11-23-2018, 04:45 PM
  2. DCount Function not working
    By rcerda in forum Access
    Replies: 6
    Last Post: 06-05-2018, 04:59 PM
  3. Dcount Function
    By felixkiprono402@gmail.com in forum Access
    Replies: 10
    Last Post: 05-23-2017, 09:26 AM
  4. dcount function - new to Access VBA
    By jillp in forum Programming
    Replies: 7
    Last Post: 09-20-2012, 06:35 AM
  5. DCOUNT function with IN statement
    By dwilson in forum Access
    Replies: 2
    Last Post: 08-12-2011, 09:54 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums