Results 1 to 5 of 5
  1. #1
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314

    Count records based on a value in a combo box

    I need to count records in a table for a report, with criteria based on a value in a Combo Box. I created a query in Design View, with 4 columns, each for the same combo box, to query on 4 different criteria.



    I first tried = "Board Meeting", in the Criteria row. But Access reports a data type mismatch.
    Researching, I found MS suggests using the DLookup() function to work on a Combo Box. To work this out, I turned off the remaining 3 columns and concentrated on just column 1.

    The Combo Box is named [Event Type]. The Table is [Events]. The criteria is "Board Meeting". The Combo Box Consists of two columns: ID in Column 0 and the text value to query on, in Column 1.

    I created the DLookUp expression as follows:

    =DLookUp("[Event Type]","Events","[Event Type].Column(1) = Board Meeting")

    Using this expression, Access reports, missing operator in the criteria expression, followed by another message "Unknown". I can't figure out what operator Access thinks it needs. What is wrong with this criteria expression? See image of the query design view.

    Click image for larger version. 

Name:	DLookup.jpg 
Views:	7 
Size:	33.4 KB 
ID:	32604

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Since you're hard-coding everything, why not just put in the numeric value associated with board meeting?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    Oops. I usually embed such lists in the Combo Box, but since this list needs to be used in more than one place, I created it as a lookup table of Activity Types. When I went to lookup the ID for Board Meeting, as you suggested, I discovered I hadn't created an ID field for this table, as I had done for all the other data tables. The only column is the text value, so the Combo Box really has only the one column. Can I not still query on it without a key ID field?

  4. #4
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    When you said "hard coding" in your reply, I would prefer that anyway, so I tried moving away from hard coding, and have it working without it, by finding only those event types actually used. I used two columns, one for Group By and another for Count. Thanks.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 08-09-2016, 01:52 PM
  2. Count records based on report ,not in query
    By Abasalic4 in forum Reports
    Replies: 1
    Last Post: 04-13-2012, 12:46 PM
  3. Replies: 3
    Last Post: 01-19-2012, 06:02 PM
  4. Combo Box based upon records in sub form
    By caddcop in forum Forms
    Replies: 3
    Last Post: 02-21-2011, 11:45 AM
  5. Count IIf based on records above and below
    By William McKinley in forum Queries
    Replies: 19
    Last Post: 09-17-2010, 04:49 PM

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