Error 3075 - Syntax Error (missing operator) in query....

cukarisol
StarLounger
Posts: 55
Joined: 21 Sep 2011, 06:59

Error 3075 - Syntax Error (missing operator) in query....

Post by cukarisol »

Hi all

During data entry, whenever I include double quotation marks in an unbound memo field in MS Access 2003 Front End (linked to a Sybase 12.5 Backend). (Example: Then the rabbit said, "I'd like a carrot"); on save, I get the following error message:
.....Error 3075 - Syntax Error (missing operator) in query expression...

Field Type in Sybase - Text
Field Type as Displayed in linked table in Access 2003 - Memo

Is there any way to overcome this and allow double quotation marks to be entered into a memo field?

Thanks

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Error 3075 - Syntax Error (missing operator) in query...

Post by HansV »

See what happens if you double the double quotes within the text:

Then the rabbit said: ""I'd like a carrot""
Best wishes,
Hans

cukarisol
StarLounger
Posts: 55
Joined: 21 Sep 2011, 06:59

Re: Error 3075 - Syntax Error (missing operator) in query...

Post by cukarisol »

Hi Hans

I put x2 double quotes around the mentioned quote and it saved fine. Why does the database produce the mentioned Syntax error with one double quote on each side?

Thanks

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Error 3075 - Syntax Error (missing operator) in query...

Post by HansV »

The following is just a guess.

I assume that the driver that communicates between Access and Sybase runs code to perform an insert query when you create a new record, and an update query when you edit an existing record. For example (much simplified):

INSERT INTO MyTable (TextField) VALUES ("Then the rabbit said")

Now if the text to be inserted contains quotes, this causes a problem:

INSERT INTO MyTable (TextField) VALUES ("Then the rabbit said: "I'd like a carrot"")

The string to be inserted started with the first double quote, and ends at the next one, so the SQL statement becomes

INSERT INTO MyTable (TextField) VALUES ("Then the rabbit said: "

and then the next part makes no sense as SQL.

Doubling the double quotes indicates that they are literal characters instead of string delimiters. Also see Post=21115.
Best wishes,
Hans

cukarisol
StarLounger
Posts: 55
Joined: 21 Sep 2011, 06:59

Re: Error 3075 - Syntax Error (missing operator) in query...

Post by cukarisol »

Hi Hans

Apologies for not getting back to you earlier. I've found a suitable workaround for issue as mentioned.

My Solution: If user inserts double quotes, single quotes are inserted instead.

Not an ideal solution but it does the job and allows record to save.

...on field property "On Key Press"

Private Sub FieldName_KeyPress(KeyAscii As Integer)
If KeyAscii = 34 Then KeyAscii = 39
End Sub

Thanks

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Error 3075 - Syntax Error (missing operator) in query...

Post by HansV »

Thanks for posting back. If you don't mind the succession of single quotes in

Then the rabbit said: 'I'd like a carrot'

that's a fine workaround.
Best wishes,
Hans