Results 1 to 6 of 6
  1. #1
    wwhit is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    18

    How do you use DCOUNT for multiple criteria?


    I have the following code:

    Code:
    If DCount("[EMPLOYEE]", "Class Requirement Table Query", "[EMPLOYEE]= '" & Me![EMPLOYEE2] & "'") And DCount("[EMPLOYEE2]", "Training Table", "[EMPLOYEE2] <> '" & Me![EMPLOYEE2] & "'") And [Forms]![Training Schedule to View Create Form].[TRAINING CLASS NAME] <> "Navigation" Then
    
                    MsgBox "The Employee Name..."
                    Me.Undo
                    Cancel = True
    
    End If
    I have this code set up for a combo button change so that when the user picks an employee name that they are listed in the Class Requirement Table Query and they are not listed in the Training Table and the training class name is Navigation. However, when I run the code it is not working to count the employee names with the training table - DCount("[EMPLOYEE2]", "Training Table", "[EMPLOYEE2] <> '" & Me![EMPLOYEE2] & "'"). Any suggestions???

  2. #2
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    You have space in your table names. try:
    DCount("[EMPLOYEE]", "[Class Requirement Table Query]", "[EMPLOYEE]= '" & Me![EMPLOYEE2] & "'")

    also is Me![employee2] text?

  3. #3
    wwhit is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    18
    Yes Me![employee2] is a text. I tried the solution above and it was still giving the same result. The problem with the code is the DCount("[EMPLOYEE2]", "Training Table", "[EMPLOYEE2] <> '" & Me![EMPLOYEE2] & "'").

  4. #4
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    Had a brainwave, when you say it is not working, do you get an error message such as invalid use of null?

  5. #5
    wwhit is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    18
    Good Question. I should of been more specific. It is not giving an error but it is not doing what I want it to do. It is counting the employees with the Class Requirement Table Query as I have separated out the code to see the problem. It is not counting the employees in the Training Table to verify that the employee is not in the table to give the message box. It gives the message box no matter if the employee is in the table or not. I am not sure where I went wrong with the code. Please help. Thanks...

  6. #6
    wwhit is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    18
    I firgured it out. I had to create a query that would find unmatched values between the two tables then my code would work:

    Code:
    If DCount("[EMPLOYEE]", "Class Requirement Table Without Matching Training Table", "[EMPLOYEE]= '" & Me![EMPLOYEE2] & "'") And [Forms]![Training Schedule to View Create Form].[TRAINING CLASS NAME] <> "Navigation" Then
    
    MsgBox "The Employee Name you have entered is not scheduled for the Navigation class." & vbCrLf & vbCrLf & "The Navigation class is required for this employee prior to scheduling another class."
    Me.Undo
    Cancel = True
    
    End If

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

Similar Threads

  1. If Then Else Multiple Criteria
    By GrayWolf in forum Programming
    Replies: 5
    Last Post: 04-12-2012, 07:27 PM
  2. Replies: 1
    Last Post: 07-13-2011, 11:00 AM
  3. multiple optional criteria
    By TheShabz in forum Programming
    Replies: 7
    Last Post: 07-05-2011, 05:13 PM
  4. multiple dcount update query
    By slothnet in forum Programming
    Replies: 5
    Last Post: 08-24-2010, 03:44 PM
  5. Mask for dcount criteria
    By akbigcat86 in forum Programming
    Replies: 3
    Last Post: 08-09-2010, 11:00 AM

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