View original (French)

Like true/false different from ... ( access query)

bibimtl2x Posted messages 54 Status Member -  
bibimtl Posted messages 2 Status Member -

Hello,

I need your help with Access query criteria field:

Here is my formula:

-----------------------

like vraifaux( Formulaires![Données]![Cocher_trackit_closed] =true,"*",not "Completed")

--------------------------

if "Formulaires![Données]![Cocher_trackit_closed] =true" (it's a checkbox) it works all rows are displayed, but if it is not equal to true (so not checked) nothing is displayed :( even though I need all rows to display except those equal to "Completed"

it's probably the ( not "Completed" ) but I tried many variants and nothing works..

<> "Completed"

not "Completed"

"pas Completed"

"not Completed"

"pas 'Completed' "

many thanks for your help.. :)


4 answers

  1.
    yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassadeur 1 588
     

    hello,

    Your condition, therefore, is "the checkbox is checked or the field is different from 'Completed'."

    If you display your query in SQL mode, this should give the following, if your field is named myfield:

     where Formulaires![Données]![Cocher_trackit_closed] or monchamps <> "Completed"

    In create mode, you should have two criterion lines for the field, the first

     Formulaires![Données]![Cocher_trackit_closed]

    and the second

     <> "Completed"
    0
  2. bibimtl Posted messages 2 Status Member
     
    Hello,

    Thank you for taking the time to respond, it’s much appreciated, I’ll try to be more explicit.

    My checkbox: Formulaires![Données]![Cocher_trackit_closed]

    My field is named [trackit_statut]

    So in my query, criterion field of my column [trackit_statut], I want the condition that says:

    If Formulaires![Données]![Cocher_trackit_closed] is checked (true), show all rows.

    If Formulaires![Données]![Cocher_trackit_closed] is NOT checked (false), show only rows where the field [trackit_statut] is not equal to "completed".

    Thank you :)
    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      Your explanations confirm my understanding.

      Did you test my suggestion?

      0
      1. bibimtl2x Posted messages 54 Status Member 1 > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
         

        Hello,

        Honestly I don’t really understand your answer, I don’t use direct SQL. isn’t it possible to just have the formula to add in the criteria field? is it done just with direct SQL?

        because especially if I want to modify later, it’s going to be complicated since I don’t understand anything, and I have to add criteria on several columns

        thanks

        the direct SQL looks like this for now

        SELECT TBL_Données.[#_de_transaction], TBL_Données.Trackit_Statut
        FROM TBL_Données
        WHERE (((TBL_Données.Trackit_Statut) Like IIf([Formulaires]![Données]![Cocher_trackit_closed]=True,"*",([TBL_Données].[Trackit_Statut])<>"Completed")));
         

        0
      2. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > bibimtl2x Posted messages 54 Status Member
         

        If you don't understand an answer, why ignore it and repeat the initial question, instead of asking for explanations?

        In SQL:

        SELECT TBL_Données.[#_de_transaction], TBL_Données.Trackit_Statut FROM TBL_Données WHERE [Formulaires]![Données]![Cocher_trackit_closed] OR Trackit_Statut <> "Completed" ;

        You can then see what it looks like by returning in non-SQL display.

        It is much simpler to communicate via SQL text than to explain how to complete the creation mode table.

        0
  3. bibimtl2x Posted messages 54 Status Member 1
     

    Hello,

    Thank you again for your time :)

    Sorry. It may be me who has lacked clarity from the start; I did clearly mark in the "criteria" field what I meant by not in SQL, but as I said it’s probably me lol :)

    I did try to modify in SQL, but on my end it seems impossible to return to normal display; initially there are 3 options: data sheet mode, SQL mode, creation mode.. But if you modify the SQL code there remain only two modes: data sheet mode, SQL mode :(

    And since I don’t know SQL I can’t afford to stay stuck for future modifications :(

    So to return to the "criteria" field, do you think you would be able to help me anyway without SQL?

    A huge huge thank you :)

    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      When I modify the SQL code, I can return to creation mode.

      Perhaps with this SQL code:

      SELECT TBL_Données.[#_de_transaction], TBL_Données.Trackit_Statut FROM TBL_Données WHERE IIf([ Formulaires]![Données]![Cocher_trackit_closed] , True , TBL_Données.Trackit_Statut <> "Completed" );
      0
      1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
         

        Or, in creation mode, add, in an empty column:

        • first line: 
         IIf([ Forms]![Data]![Check_trackit_closed] , True , TBL_Data.Trackit_Status <> "Completed" )
        • third line 
         Where
        • sixth line 
           True
        0
      2. bibimtl2x Posted messages 54 Status Member 1 > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
         

        Hello,

        thank you, I think you had an error in your formula it was more an "=" and not a "," before the real

        but yes I had indeed thought of this way to do it, to add a column, but since I will have many columns and each with conditions I prefer to have the condition right in the same column rather than doubling them all and ending up getting lost.. :(

        do you have another idea to try in just the criteria field (6th row) ?

        thanks in advance 100 x again:) so appreciated :)

        0
  4. bibimtl Posted messages 2 Status Member
     

    Well I think I’ve found it :) and especially to discover the second line of condition that I didn’t know lol (the or), you talked about it a bit higher, but I hadn’t understood
    and I recreated a small Access to work, so the fields are a bit different but it looks like this:
    Criterion: Like TrueFalse([Forms]![frm_donne]![cacse_coché]=True;"*")
    or: <>"completed"

    A big thank you for your patience! it’s still with paths and exchanging ideas that I learn and progress!!!! thank you!!!! ;)

    0