Results 1 to 6 of 6
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    DCount for current entry in form?

    Hi, I've attached an example database to explain better. I've got a DB full of complaints and a form that examines each complaint one by one. I'd like for the form to display a number of how many total complaints we have in the DB for the customer shown. In the example I have a DCount which shows all complaints in the query, but I can't seem to do the criteria piece right to have it zero in on the CustomerID listed in the current entry that is being viewed. I tried:

    Code:
    =DCount("*","qryComplaints","Me.CustomerID")
    But that errored out. Any idea?
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    =DCount("*","tblComplaints","CustomerID=" & [CustomerID])

    Why isn't CustomerID a number type field, not text?

    If it must be text:

    =DCount("*","tblComplaints","CustomerID='" & [CustomerID] & "'")

    Me only works in VBA procedures.
    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
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    That worked!! Thank you!

  4. #4
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Actually one other question...some complaints don't have a customer ID. They just state "N/A".

    As a result, the dCount still quantifies these "N/A"s but its not really needed. Is there any way to add into the the DCount criteria that if the CustomerID is "N/A", it should just display "N/A"?

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Something here doesn't smell right. As june mentioned, the customerID should probably be a number, and unique within the customer table. If there is no customer id for the complaint table you should probably leave it null and not "N/A"... but anyway
    Code:
    =IIf([CustomerID]="N/A","N/A",DCount("*","tblComplaints","CustomerID='" & [CustomerID] & "'"))

  6. #6
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by kd2017 View Post
    Something here doesn't smell right. As june mentioned, the customerID should probably be a number, and unique within the customer table. If there is no customer id for the complaint table you should probably leave it null and not "N/A"... but anyway
    Code:
    =IIf([CustomerID]="N/A","N/A",DCount("*","tblComplaints","CustomerID='" & [CustomerID] & "'"))
    Thank you! Yea its a bit weird. Sometimes we get complaints that aren't from a singular person or even a customer. For instance, a random person submitted a complaint that our landscaping was subpar. They weren't a customer and the prior regime just marked those folks as "N/A" for their customerID. Hence, why the field is not a number. This DB also has all historical data and our prior POS had the customerIDs contain letters as well. Again, hence why the field is not a number

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

Similar Threads

  1. Replies: 7
    Last Post: 10-13-2016, 12:32 PM
  2. Current entry not appearing
    By trident in forum Queries
    Replies: 1
    Last Post: 04-12-2016, 08:20 AM
  3. Replies: 11
    Last Post: 03-31-2016, 09:19 AM
  4. Replies: 5
    Last Post: 01-22-2016, 02:02 PM
  5. Replies: 3
    Last Post: 06-04-2010, 12:47 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