Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    15

    Unanswered: ControlSource using function DCount

    I am a novice in both Access and VBA. I have been assign to build an Inventory Control for the company. I got the data structure work pretty well by trial and errors. Now, it's time to build the analysis for it; and I ran into some difficulties.
    I use all the fields as combo box on a tab of the main form, and put expression "=DCount() as controlSource; most of them return correct value, but when there is no record found, the return value is wierd and I don't understand why. For example, when I want to count the parts that are in "Returned for Repair", result shows #Name? instead of 0. Could somebody explain why, and how can I fix it?
    Also, I like to build a public function for this, so I can pass the table/query name along with the "search-target" as String, then it could return the right value.

    Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is because DCount() returns Null when there is no match. Use = Nz(DCount(...), 0) instead.
    Have a nice day!

  3. #3
    Join Date
    Oct 2009
    Posts
    15
    Thanks a lots Sinndho. I got most of it.
    There is another issue related to this. There are two categories that need qry to find the search targets, how can I use Dcount with them.
    For example, asset tables have one field name hierarchyID, which stands for a configuration of system from the top down, such as router main module, then interface board/power board/,and OS, etc.
    To count hardware, you just helped me all, but for software (OS), I need to go back to to the hierarchy table to find the component category, which will have "OS" or "Application" listing. How can I do this? If not, can you shed sone light? Thanks again

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not sure to fully understand the problem, mainly because I don't know how your database is organised, but DCount() can accept three parameters:
    - The first is a column or the derived expression from a column.
    - The second is the table or query (domain) to work with.
    - The third contains criteria that you apply to the defined domain.

    In your case this third parameter should be something like:
    Code:
    <Column> = 'OS' OR <Column> = 'Application'
    As an alternative, you could also build a SELECT query with a WHERE clause:
    Code:
    SELECT ... FROM ... WHERE <Column> = 'OS' OR <Column> = 'Application'
    and call the DCount function using this query as the domain. This could be quicker, as domain functions (DCount, DMin, DMax, DLookup etc.) are known to be rather slow.
    Have a nice day!

  5. #5
    Join Date
    Oct 2009
    Posts
    15
    I knew that, but I have to build an unbound tabbed page for data analysis, and I don't know how to make the controlsource for the textbox in the page to do so. I also tried and failed to pass value from one pahe to another.
    Can you tel me how. Thanks

  6. #6
    Join Date
    Oct 2009
    Posts
    15
    I got it. Thanks for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •