View original (French)

Syntax error (missing operator) in the access expression

Leonidas1508 Posted messages 8 Status Member -  
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   -

Here is the client sought: ABBEY OF THE STAR

Here is the message I have:

syntax error (missing operator) in the expression: [CT_Num]='00000' AND [EC_Sens]=0 AND [ec_piece]='1111' and [ec_intitule]='Invoice No. 1111 / ABBEY OF THE STAR' AND [EC_Lettre]=0 AND [jm_DATE] >=#01/01/001# and [ec_No]<=22222.

Could you help me?

If I click on others, I get the correct result, but especially on those we've recorded with apostrophes, there is this error.

The macro is:

condition where = [CT_Num]='"&[RefClient]&"'

5 answers

  1.
    blux Posted messages 2049 Registration date   Status Moderator Last intervention   3 455
     

    Hello,

    it's normal, your string delimiter is the apostrophe and in your name, you have an apostrophe, so Access can't handle it, it thinks you are looking for Invoice No. 1111 / ABBAYE DE L and then it finds ETOILE' which does not match any syntax known to it.

    Change the delimiter to a quotation mark: "

    See you later blux           "Everything goes for fools.
    It's even how you recognize them."

    0
    1. Leonidas1508 Posted messages 8 Status Member
       

      Thanks @blux StatusModerator I changed the ' to " and now it returns no data even though there is data to return. And now, for all the other clients, it returns no data when before it was only the clients with apostrophes that returned a syntax error.

      0
  2. blux Posted messages 2049 Registration date   Status Moderator Last intervention   3 455
     

    And what if you only change the ' to " for the condition [ec_intitule]?

    Because I’m wary of data that seems to be numerical but will be tested as strings (like [CT_Num]).

    See you later blux           "The fools, they dare everything.
    It's even how you recognize them."

    0
    1. Leonidas1508 Posted messages 8 Status Member
       

      Private Sub Effacer_Recherche_Click()
      Me.Rech_RefClient.Value = ""
      Me.Rech_Client.Value = ""
      Me.Rech_Collab.Value = ""
      Forms![Gestion des Plafonds]![Controle Depassement Client].Form.RecordSource = "SELECT [Controle Depassement Client].RefClient,[Controle Depassement Client].Client,[Controle Depassement Client].Plafond,[Controle Depassement Client].SoldeTot,[Controle Depassement Client].Depassement,[Controle Depassement Client].SoldeA,[Controle Depassement Client].SoldeB,[Controle Depassement Client].CO_Nom,[Controle Depassement Client].BL,[Controle Depassement Client].[Total BL] from [Controle Depassement Client] where [Controle Depassement Client].Client Like '*" & Replace(Me.[Rech_Client], "'", "''") & "*'"
      End Sub

      Private Sub Rech_Client_AfterUpdate()
      Forms![Gestion des Plafonds]![Controle Depassement Client].Form.RecordSource = "SELECT [Controle Depassement Client].RefClient,[Controle Depassement Client].Client,[Controle Depassement Client].Plafond,[Controle Depassement Client].SoldeTot,[Controle Depassement Client].Depassement,[Controle Depassement Client].SoldeA,[Controle Depassement Client].SoldeB,[Controle Depassement Client].CO_Nom,[Controle Depassement Client].BL,[Controle Depassement Client].[Total BL] from [Controle Depassement Client] where [Controle Depassement Client].Client Like '*" & Replace(Me.[Rech_Client], "'", "''") & "*'"
      End Sub

      Private Sub Rech_Client_Change()
      Forms![Gestion des Plafonds]![Controle Depassement Client].Form.RecordSource = "SELECT [Controle Depassement Client].RefClient,[Controle Depassement Client].Client,[Controle Depassement Client].Plafond,[Controle Depassement Client].SoldeTot,[Controle Depassement Client].Depassement,[Controle Depassement Client].SoldeA,[Controle Depassement Client].SoldeB,[Controle Depassement Client].CO_Nom,[Controle Depassement Client].BL,[Controle Depassement Client].[Total BL] from [Controle Depassement Client] where [Controle Depassement Client].Client Like '*" & Me.[Rech_Client] & "*'"
      End Sub

      Private Sub Rech_Client_Enter()
      Forms![Gestion des Plafonds]![Controle Depassement Client].Form.RecordSource = "SELECT [Controle Depassement Client].RefClient,[Controle Depassement Client].Client,[Controle Depassement Client].Plafond,[Controle Depassement Client].SoldeTot,[Controle Depassement Client].Depassement,[Controle Depassement Client].SoldeA,[Controle Depassement Client].SoldeB,[Controle Depassement Client].CO_Nom,[Controle Depassement Client].BL,[Controle Depassement Client].[Total BL] from [Controle Depassement Client] where [Controle Depassement Client].Client Like '*" & Me.[Rech_Client] & "*'"
      End Sub

      Private Sub Rech_Collab_AfterUpdate()
      Forms![Gestion des Plafonds]![Controle Depassement Client].Form.RecordSource = "SELECT [Controle Depassement Client].RefClient,[Controle Depassement Client].Client,[Controle Depassement Client].Plafond,[Controle Depassement Client].SoldeTot,[Controle Depassement Client].Depassement,[Controle Depassement Client].SoldeA,[Controle Depassement Client].SoldeB,[Controle Depassement Client].CO_Nom,[Controle Depassement Client].BL,[Controle Depassement Client].[Total BL] from [Controle Depassement Client] where [Controle Depassement Client].CO_Nom Like '*" & Me.[Rech_Collab] & "*'"
      End Sub

      Private Sub Rech_Collab_Change()
      Forms![Gestion des Plafonds]![Controle Depassement Client].Form.RecordSource = "SELECT [Controle Depassement Client].RefClient,[Controle Depassement Client].Client,[Controle Depassement Client].Plafond,[Controle Depassement Client].SoldeTot,[Controle Depassement Client].Depassement,[Controle Depassement Client].SoldeA,[Controle Depassement Client].SoldeB,[Controle Depassement Client].CO_Nom,[Controle Depassement Client].BL,[Controle Depassement Client].[Total BL] from [Controle Depassement Client] where [Controle Depassement Client].CO_Nom Like '*" & Me.[Rech_Collab] & "*'"
      End Sub

      Private Sub Rech_RefClient_AfterUpdate()
      Forms![Gestion des Plafonds]![Controle Depassement Client].Form.RecordSource = "SELECT [Controle Depassement Client].RefClient,[Controle Depassement Client].Client,[Controle Depassement Client].Plafond,[Controle Depassement Client].SoldeTot,[Controle Depassement Client].Depassement,[Controle Depassement Client].SoldeA,[Controle Depassement Client].SoldeB,[Controle Depassement Client].CO_Nom,[Controle Depassement Client].BL,[Controle Depassement Client].[Total BL] from [Controle Depassement Client] where [Controle Depassement Client].RefClient Like '*" & Me.[Rech_RefClient] & "*'"
      End Sub

      Private Sub Rech_RefClient_Change()
      Forms![Gestion des Plafonds]![Controle Depassement Client].Form.RecordSource = "SELECT [Controle Depassement Client].RefClient,[Controle Depassement Client].Client,[Controle Depassement Client].Plafond,[Controle Depassement Client].SoldeTot,[Controle Depassement Client].Depassement,[Controle Depassement Client].SoldeA,[Controle Depassement Client].SoldeB,[Controle Depassement Client].CO_Nom,[Controle Depassement Client].BL,[Controle Depassement Client].[Total BL] from [Controle Depassement Client] where [Controle Depassement Client].RefClient Like '*" & Me.[Rech_RefClient] & "*'"
      End Sub

      Private Sub Rech_RefClient_Enter()
      Forms![Gestion des Plafonds]![Controle Depassement Client].Form.RecordSource = "SELECT [Controle Depassement Client].RefClient,[Controle Depassement Client].Client,[Controle Depassement Client].Plafond,[Controle Depassement Client].SoldeTot,[Controle Depassement Client].Depassement,[Controle Depassement Client].SoldeA,[Controle Depassement Client].SoldeB,[Controle Depassement Client].CO_Nom,[Controle Depassement Client].BL,[Controle Depassement Client].[Total BL] from [Controle Depassement Client] where [Controle Depassement Client].RefClient Like '*" & Me.[Rech_RefClient] & "*'"
      End Sub
       

      Here are a few other queries that govern the macro. I took over someone else's work and I'm having a hard time understanding what they did actually.

      0
      1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > Leonidas1508 Posted messages 8 Status Member
         

        Hello,

        when you share code, please take this into account: https://codes-sources.commentcamarche.net/faq/11288-poster-un-extrait-de-code

        What is the connection between the initial expression and the code shown in #4?

        Since you modified the initial code, don't you think it's important to show what it has become?

        0
      2. Leonidas1508 Posted messages 8 Status Member > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
         

        Thank you @yg_be StatusContributor. Alright. I will take that into account. I'm just showing the modules on which the Access form operates.

        The code shown at the beginning, I modified it by replacing ' with "

        0
      3. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > Leonidas1508 Posted messages 8 Status Member
         

        show us the modified code.

        0
  3. blux Posted messages 2049 Registration date   Status Moderator Last intervention   3 455
     

    The error query is not in the code you provided...

    See you blux           "Fools dare to do anything.
    That's how we recognize them"

    0
    1. Leonidas1508 Posted messages 8 Status Member
       

      So it's in the first one then. Hello @blux StatusModerator

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

    How is it that this problem appears suddenly?

    Are you modifying someone else's work without understanding what they have done?

    0
    1. blux Posted messages 2049 Registration date   Status Moderator Last intervention   3 455
       

      How come this problem appears suddenly?

      It may be the recent creation of the abbey that triggers it...

      0
    2. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > blux Posted messages 2049 Registration date   Status Moderator Last intervention  
       

      The existing code appears to guard against the presence of apostrophes in the fields:

       "SELECT ... where Client Like '*" & Replace(Me.[Rech_Client], "'", "''") & "*'"
      0
    3. Leonidas1508 Posted messages 8 Status Member
       

      I'm trying to modify while understanding. But I've gotten lost, which is why I'm turning to the experts.

      0
    4. Leonidas1508 Posted messages 8 Status Member > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
       

      Exactly. That's why I feel lost.

      0
    5. Leonidas1508 Posted messages 8 Status Member > blux Posted messages 2049 Registration date   Status Moderator Last intervention  
       

      there hasn't been any new creation. the client has always existed, we have just changed the representative for the client

      0
  6. blux Posted messages 2049 Registration date   Status Moderator Last intervention   3 455
     

    So I stand by what I said, the error you showed us does not seem to be covered by the specific handling of the other queries for which you provided us the code (the "replace..." clause).

    See you blux           "Stupid people dare anything.
    It's even how we recognize them."

    0