Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2016

    Unanswered: Form filter based on recored absence on another table

    Hello Everyone,

    I have a table of Customers, and a table of Meetings (Relationship Type One-to-Many, Meetings.Customer -> Cutomers.ID).

    I made a form of Customers list, and I want to give the user option to filter it by various options (using VBA code), by City/Country/Company etc, so far it is quite easy. I also want the option to filter Customers which have no set meetings yet, and that is what I cant figure out how to do.

    This is the code I have so far:

        Dim strSQL As String
        strSQL = ""
        If FCompany.Value <> "" Then
            strSQL = "[Company]='" & FCompany.Value & "'"
        End If
        If FCity.Value <> "" Then
            If strSQL = "" Then
                strSQL = "[City]='" & FCity.Value & "'"
                strSQL = strSQL & " AND [City]='" & FCity.Value & "'"
            End If
        End If
        If FCountry.Value <> "" Then
            If strSQL = "" Then
                strSQL = "[Country/Region]='" & FCountry.Value & "'"
                strSQL = strSQL & " AND [Country/Region]='" & FCountry.Value & "'"
            End If
        End If
        DoCmd.OpenForm "Customer List", , , strSQL
    Thank you in advance,

  2. #2
    Join Date
    May 2016
    Provided Answers: 4
    Hi Zivon,

    No chance, impossible to filter Customers which have no set meetings without changing your recordsource.

    To identify them, inspire you with this sql request

    SELECT user.User, Count(meeting.datemeeting) AS CompteDedatemeetingFROM [user] LEFT JOIN meeting ON user.User = meeting.User
    GROUP BY user.User
    HAVING (((Count(meeting.datemeeting))=0));

    In my example
    table user

    table meeting
    user datemeeting
    Hippo 01/01/2016
    Hippo 02/01/2016

    Besi 0
    Last edited by informer; 09-19-16 at 16:55.

  3. #3
    Join Date
    Sep 2016

    Thank you

    Thank you for the quick reply.
    I was raised to believe that everything is possible! luckily I did not gave up and found another solution. Thank you

Posting Permissions

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