Results 1 to 8 of 8
  1. #1
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118

    DCount code


    Don't know if this is possible but I'll ask anyway.

    This code counts the number of records that have the HSKP criteria in the Dept field.

    =(DCount("[ID]","[Query1]","[Dept]='HSKP'"))

    I'd like to get the criteria for the Dept field from another control on a form instead of coding it in the control. Is this possible?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Certainly. Concatenate variable reference.

    =(DCount("[ID]","[Query1]","[Dept]='" & controlname & "'"))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    Now I'm getting a #NameError?. The control name is correct. The control is on a different form. Is the path wrong?

    =(DCount("[ID]","[Query1]","[Dept]='" & [Forms]![Reporting]![DeptRptFrm] & "'"))

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Depends. Other form is open independently?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    The other form can open independently but must be open for Query1 to work. Its a parameter query

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't use dynamic parameter query. But that is irrelevant to DCount() used in textbox on form or report.

    What is DeptRptFrm - subform container control? Must reference a control that has a value, such as a textbox or combobox.

    Those outer parens are not necessary although shouldn't hurt.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    DeptRptFrm is a combobox on a form called Reporting. The combo box gets it values from a table called Departments

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Unless Reporting is used as a subform, what you have should work.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Dcount and between
    By Clexidr@ in forum Access
    Replies: 2
    Last Post: 07-27-2017, 09:01 AM
  2. DCount Help
    By mpaulbattle in forum Queries
    Replies: 3
    Last Post: 11-21-2016, 11:42 AM
  3. DCount
    By Newbie11 in forum Reports
    Replies: 4
    Last Post: 08-03-2012, 12:23 AM
  4. DCount
    By nsteenhaut in forum Queries
    Replies: 2
    Last Post: 10-04-2011, 05:00 PM
  5. DCount and SQL
    By DSTR3 in forum Queries
    Replies: 3
    Last Post: 12-06-2010, 03:07 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