Results 1 to 10 of 10
  1. #1
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318

    DCount on a Query giving #Name? error

    I'm trying to do a DCount but am getting a #Name? error. I'm trying to find the number where command = to A1 in the query Q_WO_Outstanding. Is the syntax wrong?

    =DCount([Command],[Q_WO_Outstanding],[Command]="A1")

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Yes, the syntax is wrong. Change it to:

    =DCount([Command],[Q_WO_Outstanding],"[Command]='A1'")

    The whole criteria string has to be in quotation marks, and because A1 is also a string, it has to be in its own set of quotation marks as well. Single quotes or double quotes both work, but if you use double-quotes, it has to look like this:

    =DCount([Command],[Q_WO_Outstanding],"[Command]=""A1""")

    John

  3. #3
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    It still shows the error. This is in a report. The report has no Record Source because I will be referencing 3 seperate queries. When I added the queries in the Record Source as a SQL query it kept locking up the report. Will the report be able to read Q_WO_Outstanding without some kind of link in the record source?

  4. #4
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    That depends on what you mean by "read" the query. The error you are getting may not be related to the DCount, but to how you are using it. In what context are you using it in your report?

    John

  5. #5
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    Basically, I have a unbound field where I want the results of a count of all the A1 commands in the Q_WO_Outstanding query.

  6. #6
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    One easy way is to use the On Format event of the report section where you have the unbound field:

    FieldName = DCount([Command],[Q_WO_Outstanding],"[Command]=""A1""")

    John

  7. #7
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    There are actually multiple fields. I've attached an example without the tables to give you an idea what I am doing. Each field will have a count referencing a command (A1, A2, A3, A4, A5 or A6) and a query (Outstanding, In Progress or Resolved). Hopefully this helps.
    Attached Files Attached Files

  8. #8
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I can't open the file, because I only have A2003. But no matter.

    In theory, then, you can have 18 DLookup's ( 6 commands x 3 queries) to fill 18 report fields. That should not present any problems that I can see. Each is independant of the others.

    John

  9. #9
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    I changed it to a 2003 format. I guess I'm confused as to how to do the multiples. Do I still use the Format Event?
    Attached Files Attached Files

  10. #10
    Huddle is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Jun 2010
    Posts
    318
    I've found a solution in using Sum with and IIf statement. I broke each section down into individual sub reports to reference the individual queries.

    Thank you for your help.

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

Similar Threads

  1. DCount Error
    By drewetzel in forum Access
    Replies: 5
    Last Post: 02-13-2014, 06:57 AM
  2. Access giving wrong result using sql query
    By amlanroy in forum Access
    Replies: 3
    Last Post: 05-10-2012, 10:11 AM
  3. DLookUp function giving invalid use of null error
    By shubhamgandhi in forum Programming
    Replies: 4
    Last Post: 07-21-2011, 06:04 PM
  4. Replies: 2
    Last Post: 05-17-2011, 02:40 PM
  5. NoData() still giving me an error.
    By cowboy in forum Programming
    Replies: 3
    Last Post: 04-08-2010, 12:26 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