SQL ERROR 3075

[slames]

I am having problems with an insert sql query which is triggered by clicking a button on a form. My code is:


DoCmd.RunSQL "insert into tblInvoice(invScID, invSeries, invNumber, invPreCode, invJournal, invAddress, invLine1, invOrderNo1, invQuantity1, invTotal, invCurrency,invStatus, invSubscriberID, invReferenceAddress, invOldSubscriberID)values('" & Text72 & "','3','" & Text65 & "','RC','3','" & Text40 & "','" & Text53 & "','" & Text55 & "','" & Text57 & "','" & Text49 & "','" & Text51 & "','6','" & Text63 & "','" & Text59 & "','" & Text61 & "')"
DoCmd.OpenReport "rptCreditNote", acViewPreview.

The error I'm getting is 3075 Syntax error (missing operator) in query expression " Annales de Linstitut Fourier...(rest of address).

This is odd as it works sometimes and others not, and seems to have a problem with the address field, (text 53)

Can anyone help?

Thanks
Steph

 

[TonyJollans]

Hi Steph,

This can be caused by having quotes or apostrophes in your data. Is "Linstitut" in your data actually "L'institut"?

The underlying reason is that the string is processed twice - once by VBA as it is built, and again by Access when it is parsed. The best solution will depend on the nature of your data. Is it feasible to detect apostrophe's (and change them) in your address strings before you build your SQL?

Enjoy,
Tony

 

[slames]

It's not really feasible to change the addresses of our customers and take out all the apostrophes, is there a work around at all?

Thanks
Steph

 

[TonyJollans]

Sorry, I wasn't suggesting actually changing the addresses! You just need to change the representation of them in your code so that the correct addresses get added to your table. Assuming, for simplicity, that there can only be one apostrophe in an address ...

Dim Addr As String
Dim APos As Integer

Addr = "Annales de L'institut Fourier"

APos = InStr(Addr, "'")
If APos > 0 Then 
    Addr = Left(Addr, APos - 1) & "''" & Mid(Addr, APos + 1)
End If

Then use the, possibly amended, Addr in your SQL and the result should be a single apostrophe in your table.

Enjoy,
Tony