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
Error 3075 - Syntax Error (missing operator) in query....
-
- StarLounger
- Posts: 55
- Joined: 21 Sep 2011, 06:59
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Error 3075 - Syntax Error (missing operator) in query...
See what happens if you double the double quotes within the text:
Then the rabbit said: ""I'd like a carrot""
Then the rabbit said: ""I'd like a carrot""
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 55
- Joined: 21 Sep 2011, 06:59
Re: Error 3075 - Syntax Error (missing operator) in query...
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
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Error 3075 - Syntax Error (missing operator) in query...
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.
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
Hans
-
- StarLounger
- Posts: 55
- Joined: 21 Sep 2011, 06:59
Re: Error 3075 - Syntax Error (missing operator) in query...
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
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Error 3075 - Syntax Error (missing operator) in query...
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.
Then the rabbit said: 'I'd like a carrot'
that's a fine workaround.
Best wishes,
Hans
Hans