Results 1 to 7 of 7
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    DCount With Multiple Criteria

    I want to use DCount() with three criteria, but I am getting an error of


    Run-time error '13':
    Type mismatch

    This is the code I have

    Code:
    Public Sub Testing()
      intNumber = DCount([Shirt Size], "ABCDEFG", "[Final]=0206 AND [Shirt Color] = Red And [Gender]=Boy")
    End Sub
    What is the proper way to use dlookup() with multiple criteria in vba?

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You need to escape text fields with some quotes Try

    Public Sub Testing()
    intNumber = DCount([Shirt Size], "ABCDEFG", "[Final]=0206 AND [Shirt Color] = 'Red' And [Gender]='Boy'")
    End Sub

    You would need to use # # around dates if they became involved.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    I changed my code to

    Code:
    Public Sub Testing()
    intNumber = DCount([Shirt Size], "ABCDEFG", "[Final]='0206' AND [Shirt Color] = 'Red' And [Gender]='Boy'")
    End Sub
    but still get the same error


    The field name to check is Shirt Size, and the table name is ABCDEFG and the other fields in Brackets in the "Where" portion are valid fields in the table.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    quotes around the RETURN FIELD and the TABLE/QUERY and the criteira

    =DCount("[Shirt Size]", "ABCDEFG", "[Final]='0206' AND [Shirt Color] = 'Red' And [Gender]='Boy'")

  5. #5
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    229
    Is 0206 stored as text or numeral? If numeral then skip the quotes.

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by goodguy View Post
    Is 0206 stored as text or numeral? If numeral then skip the quotes.
    As it has a leading zero I would make an educated guess that it is text, but good question.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    229
    I saw that too, but since we can mask number fields to display as text, it is easy to get confused especially if one is not the original creator of the db.

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

Similar Threads

  1. dcount for multiple criteria
    By markjkubicki in forum Programming
    Replies: 5
    Last Post: 01-24-2019, 10:08 AM
  2. Dcount function with multiple criteria
    By eusebio in forum Forms
    Replies: 1
    Last Post: 11-23-2018, 04:45 PM
  3. DCount Multiple Criteria Not Working
    By seantnash in forum Queries
    Replies: 6
    Last Post: 08-19-2016, 02:40 PM
  4. DCount with Multiple Criteria
    By Newbie11 in forum Reports
    Replies: 4
    Last Post: 05-25-2012, 09:04 AM
  5. How do you use DCOUNT for multiple criteria?
    By wwhit in forum Programming
    Replies: 5
    Last Post: 05-15-2012, 11:14 AM

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