Results 1 to 4 of 4
  1. #1
    abodi is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    31

    Dcount with filter critera on a related table. possible?

    Code:
    DCount("*","[tblLeaveRequests]","Status='Approved' And #" & Format([vDate1],"mm/dd/yyyy") & "# BETWEEN [LeaveStartDate] AND [LeaveEndDate]")
    The above code counts all leave with the status of approved on the table [tblLeaveRequests]. Great, but i did forget a critera.

    Click image for larger version. 

Name:	dcount with critera on a related table.PNG 
Views:	10 
Size:	9.1 KB 
ID:	17928

    Of the leave it is counting, I want it to only count if the employee the leave is for is in a particular location. which exists on the related table, and not the one dcount is calling.



    Is this possible? or will i have to resort to replicated data in [tblLeaveRequests]

  2. #2
    abodi is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    31
    OR dcount could be run on a query that contained information from multiple tables?

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    You have it right on the second post - a DCount can be used with a multi-table query, and the criteria can reference fields in any table in the query.

    Be sure to use fully qualified field references ([tablename].[fieldname]) if the same field occurs in more than one table.

    John

  4. #4
    abodi is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    31
    Thanks for the confirmation John.

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

Similar Threads

  1. Replies: 3
    Last Post: 04-09-2014, 12:48 PM
  2. Critera for date using wild cards
    By Ubberz in forum Queries
    Replies: 2
    Last Post: 04-02-2014, 12:36 PM
  3. Query Critera w/ two Paths
    By Mike4172 in forum Queries
    Replies: 6
    Last Post: 04-10-2013, 08:47 PM
  4. Replies: 7
    Last Post: 07-02-2012, 10:50 PM
  5. multiple seach critera
    By dirtbiker1824 in forum Queries
    Replies: 1
    Last Post: 03-29-2011, 01:17 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