View original (French)

Syntax error (missing operator) in the access expression

Leonidas1508 Posted messages 8 Status Membre -  
yg_be Posted messages 23437 Registration date   Status Contributeur 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 réponses

Réponse 1 / 5
blux Posted messages 5003 Registration date   Status Modérateur Last intervention   3 451
 

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
Leonidas1508 Posted messages 8 Status Membre
 

Thanks @blux StatutModérateur 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
Réponse 2 / 5
blux Posted messages 5003 Registration date   Status Modérateur Last intervention   3 451
 

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
Leonidas1508 Posted messages 8 Status Membre
 

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
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 587 > Leonidas1508 Posted messages 8 Status Membre
 

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
Leonidas1508 Posted messages 8 Status Membre > yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention  
 

Thank you @yg_be StatutContributeur. 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
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 587 > Leonidas1508 Posted messages 8 Status Membre
 

show us the modified code.

0
Réponse 3 / 5
blux Posted messages 5003 Registration date   Status Modérateur Last intervention   3 451
 

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
Leonidas1508 Posted messages 8 Status Membre
 

So it's in the first one then. Hello @blux StatutModérateur

0
Réponse 4 / 5
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   Ambassadeur 1 587
 

How is it that this problem appears suddenly?

Are you modifying someone else's work without understanding what they have done?
0
blux Posted messages 5003 Registration date   Status Modérateur Last intervention   3 451
 

How come this problem appears suddenly?

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

0
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 587 > blux Posted messages 5003 Registration date   Status Modérateur 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
Leonidas1508 Posted messages 8 Status Membre
 

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

0
Leonidas1508 Posted messages 8 Status Membre > yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention  
 

Exactly. That's why I feel lost.

0
Leonidas1508 Posted messages 8 Status Membre > blux Posted messages 5003 Registration date   Status Modérateur Last intervention  
 

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

0
Réponse 5 / 5
blux Posted messages 5003 Registration date   Status Modérateur Last intervention   3 451
 

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