Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Querie NOT IN (MSACCESS 2003)

    I think the expression I'm looking for is Not In.

    I have a listbox on a form driven by a query

    List Named: AthleteList

    SELECT AthleteNames.AthleteID, AthleteNames.Athlete, AthleteNames.Country, JUNCTION.ID2
    FROM AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID
    WHERE (((JUNCTION.ID2)=[forms]![Mainform1]![Subform1].[form].[ID2]))
    ORDER BY AthleteNames.Athlete;

    This fills with names already in records. However another listbox is on the form which fills with names against inputted search text.
    This all works, but I want to exclude those names already in the other list to avoid duplications if the user trys to select a name to add to the record. Hope I have not confused anybody.

    The second listbox code is:

    MySql = "SELECT AthleteNames.Athlete, AthleteNames.AthleteID,AthleteNames.Country"
    MySql = MySql & " FROM AthleteNames"
    MySql = MySql & " WHERE AthleteNames.Athlete Like " & Chr(34) & Me!IPA.Text & "*" & Chr(34)
    MySql = MySql & " ORDER BY AthleteNames.Athlete;"
    Me.List173.RowSource = MySql
    Me.List173.Requery

    Many thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Querie NOT IN (MSACCESS 2003)

    Try

    MySql = "SELECT AthleteNames.Athlete, AthleteNames.AthleteID,AthleteNames.Country"
    MySql = MySql & " FROM AthleteNames"
    MySql = MySql & " WHERE AthleteNames.Athlete Like " & Chr(34) & Me!IPA.Text & "*" & Chr(34)
    MySql = MySql & " AND AthleteNames.AthleteID Not In (SELECT AthleteNames.AthleteID,"
    MySql = MySql & " AthleteNames.Athlete, AthleteNames.Country, JUNCTION.ID2"
    MySql = MySql & " FROM AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID ="
    MySql = MySql & " JUNCTION.AthleteID WHERE JUNCTION.ID2=" & [Forms]![Mainform1]![Subform1].[Form].[ID2]
    MySql = MySql & " ORDER BY AthleteNames.Athlete)"
    MySql = MySql & " ORDER BY AthleteNames.Athlete;"

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querie NOT IN (MSACCESS 2003)

    Thanks Hans. The search list shows no records when entering text.?

    I did a debug print of the MySql in case it shows why.


    SELECT AthleteNames.Athlete, AthleteNames.AthleteID,AthleteNames.Country FROM AthleteNames WHERE AthleteNames.Athlete Like "M*" AND AthleteNames.AthleteID Not In (SELECT AthleteNames.AthleteID, AthleteNames.Athlete, AthleteNames.Country, JUNCTION.ID2 FROM AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID WHERE JUNCTION.ID2=14721 ORDER BY AthleteNames.Athlete) ORDER BY AthleteNames.Athlete;

    Thanks and Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Querie NOT IN (MSACCESS 2003)

    I don't think I can solve this without seeing the data. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querie NOT IN (MSACCESS 2003)

    Thanks Hans. I played around and had some luck. I got this to work.

    MySql = "SELECT AthleteNames.Athlete"
    MySql = MySql & " FROM AthleteNames WHERE AthleteNames.Athlete NOT IN"
    MySql = MySql & " (SELECT AthleteNames.Athlete FROM AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID"
    MySql = MySql & " WHERE JUNCTION.ID2=[forms]![Mainform1]![Subform1].[Form].[ID2])"
    MySql = MySql & " AND AthleteNames.Athlete Like " & Chr(34) & Me!IPA.Text & "*" & Chr(34)
    MySql = MySql & " ORDER BY AthleteNames.Athlete;"

    Must have done something dood today. but thanks for your replies. Best regards

Posting Permissions

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