How to use Dlookup inside DCount

Posted on 2009-04-22
Last Modified: 2013-11-27
Hi Experts,

I'm having two tables: tb1 and tb2
I need to count the total number of records in tb1 with the criteria that value in tb1.[field1] can be found in tb2.[myfield]

So this requires me to use a DCount function whereas the criteria would be a Dlookup function returns non-null value. But I'm kinda stuck playing around with "

Can anyone provide the code for this purpose? Let's say I don't want to use reordset or other ways of doing it.

Many thanks!
Question by:LizzJ
    LVL 65

    Expert Comment

    no need for two functions, create a recordset instead
    first define your sql

    select count(tb1.*)  as TotalCnt
    from tb1, tb2
    where tb1.field1 = tbl2.myfield

    you need to sort out this sql first, ensure join is right

    then use it in code like this

    dim rs as dao.recordset
    dim lTotal as long

    lTotal = 0
    set rs=currentdb.openrecordset("your sql")
    if rs.eof = false then
        lTotal = rs!TotalCnt
    end if
    set rs=nothing

    Now total is in lTotal
    LVL 77

    Expert Comment

    "value in tb1.[field1] can be found in tb2.[myfield]"
    Just a bit cautious about the wording here.
    Do you simply mean where tb1.[field1] = tb2.[myfield] or are you implying something different to that?


    Author Comment


    i've mentioned that i don't want to use recordset

    what i mean is tb1.[field1] can be found in tb2.[myfield], is it the same as tb1.[field1] = tb2.[myfield] ?

    Author Comment

    to illustrate,

    here is a example:

    tb1:                tb2:
    field1             myfield
       aa               aa
       bb               bb

    so total count should be 2 since "cc" cannot be found in tb2.[myfield]

    I DON'T want to use recordset to do the counting. Instead, I want to use just one function. Is it possible?

    Thanks a lot ! Appreciate your help
    LVL 65

    Expert Comment

    well you cannot use two tables directly in this function unless it is based on a query

    so if you had your query that links the two tables together you can then use DCOUNT

    I believe it was clarification on what you asked for by Peter do you want to see if it is equal or is contained within and the sql will differ for this
    LVL 65

    Accepted Solution

    if you created a query like the one I posted earlier, say u call it qryProductCount then you can do this



    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now