Results 1 to 5 of 5
  1. #1
    MacGeol is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2017
    Posts
    3

    NOT IN criteria with list of values from text zone make query fail

    Hi all,


    I use a query to populate a listbox in a form
    In the WHERE clause of that Query i use a NOT IN criteria winch list of values comes from a text box in the same form and a LIKE criteria wich value also come from a text zone of the same form.
    Here is the query
    Code:
    SELECT R_Contacts_tous_complet.ID_RELATION, R_Contacts_tous_complet.Contact_Nom, R_Contacts_tous_complet.Classer_sous
    FROM R_Contacts_tous_complet
    WHERE R_Contacts_tous_complet.ID_RELATION NOT IN (forms![Ajouter au groupe]!txtNonDispo) AND R_Contacts_tous_complet.Contact_Nom Like "*" & forms![Ajouter au groupe]!SrchText & "*";

    The /R_Contacts_tous_complet.Contact_Nom Like "*" & forms![Ajouter au groupe]!SrchText & "*"/ criteria works very well.
    But the NOT IN provide really weird results :
    When the text zone txtNonDispo contains only one value, for example "65", it works.
    When the text zone txtNonDispo contains 2 values, for example "65, 97", it's like the NOT IN criteria is missing cause rows with ID_RELATION 65 and 97 are in the result.
    And when the text zone txtNonDispo ccontains 3 or more values, for example "65, 97, 131", the result is null...


    I understand nothing...
    I try different things... but nothing worked, always the same results.


    I hope someone here could help me.


    Thanks a lot for your advices

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you cannot pass that sort of parameter to a query like that - the query is interpreting "65,97" as a single string value

    Instead, in your query, create a new column

    Expr1: Eval([ID] & " in (" & forms![Ajouter au groupe]!txtNonDispo & ")")

    then untick the show box and put False in the criteria

    Note this only works for numbers. if it was text, on your form you would need to input '65','97'

  3. #3
    MacGeol is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2017
    Posts
    3
    Hi Ajax,

    Thanks a lot for your answer.
    How dumb I am! Of course the txtNonDispo value is a string...
    I apply something near to your solution by editing directly the SQL like that :
    Code:
    SELECT R_Contacts_tous_complet.ID_RELATION, R_Contacts_tous_complet.Contact_Nom, R_Contacts_tous_complet.Classer_sousFROM R_Contacts_tous_complet
    WHERE R_Contacts_tous_complet.Contact_Nom Like "*" & forms![Ajouter au groupe]!SrchText & "*" And Eval([ID_RELATION] & " not in (" & forms![Ajouter au groupe]!txtNonDispo & ")")=True;
    It works great for the query.
    BUT
    When I click the button that open the form calling the query I get a '3071' error on
    Code:
    DoCmd.OpenForm NomFormulaire, acNormal, , , , acDialog, Titre & "|" & strID_Contacts
    I think it's due to the value of the text box txtNonDispo when the form is loading.
    But it's weird cause I put a value in the text box before populating the listbox with the query...

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what is the description for 3071 error? I suspect it is because the txtNonDispo field is null or being interpreted as text. Either change the control default value to 0 or modify the query to use the nz function

    nz(forms![Ajouter au groupe]!txtNonDispo,0)

  5. #5
    MacGeol is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2017
    Posts
    3
    Yeah!

    Thanks a lot Ajax, with your advices and some little changes IT WORKS!

    The final SQL query is :
    Code:
    SELECT R_Contacts_tous_complet.ID_RELATION, R_Contacts_tous_complet.Contact_Nom, R_Contacts_tous_complet.Classer_sousFROM R_Contacts_tous_complet
    WHERE R_Contacts_tous_complet.Contact_Nom Like "*" & forms![Ajouter au groupe]!SrchText & "*" And Eval([ID_RELATION] & " not in (" & nz(forms![Ajouter au groupe]!txtNonDispo,"-1") & ")")=True;
    Thanks a lot again

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

Similar Threads

  1. Replies: 3
    Last Post: 01-04-2017, 02:44 PM
  2. Return list of records that fail to append?
    By terdfurgeson in forum Access
    Replies: 3
    Last Post: 09-17-2015, 04:24 PM
  3. Replies: 11
    Last Post: 02-11-2015, 06:24 PM
  4. Replies: 2
    Last Post: 07-09-2014, 12:43 PM
  5. Replies: 2
    Last Post: 05-16-2012, 04:45 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