 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008' in a query?
I have this SQL in a query: sSql = "SELECT *" & vbCrLf & _ "FROM tblTest4" & vbCrLf & _ "WHERE tblTest4.Caption_Name LIKE '%" & qryAuthor & "%';" But I would like to do a query on the character ' But as you can see when qryAuthor = ' I have a problem. If I try one ' then the query hangs. If I use '' I get an error . I have tried Chr(39) and ' none of which work. Any ideas how to query on this character? Thanks. -- George Hester _________________________________ Hi George, Replace the ' delimiters with "" (that's two double quote characters) in the last line: "WHERE tblTest4.Caption_Name LIKE ""%" & qryAuthor & "%"";" HTH, Rob >I have this SQL in a query: > [quoted text clipped - 11 lines] > George Hester > _________________________________ Yes I have tried that. The errors I am getting are two. First this one: Closing delimiter not found for the string beginning at postition 831 in the command. The string begins with: ')} AS [tblTest4]. The second is this error: Err.Source = Err.Number = 80040E14 Err.Description = The two always come together. The second is my attempt at error handling. Or it finds nothing or it hangs. There are ' for the field in the database so not sure what more to try. -- George Hester _________________________________ > Hi George, > [quoted text clipped - 21 lines] > > George Hester > > _________________________________ Sorry George, I don't understand either of those errors. The first, referring to position 831, leads me to suspect that the problem might lie with the contents of qryAuthor, which you are inserting into your sSql string. I suggest you try some debug.print statements to find out exactly what the various strings are evaluating to, and perhaps try cutting/pasting those strings from the immediate window into a query and running it, to see what happens then. Other than that, there's nothing else I can think of at the moment. Rob > Yes I have tried that. The errors I am getting are two. First this one: > [quoted text clipped - 46 lines] >> > George Hester >> > _________________________________ Hi Rob. I figured it out finally. It turns out Find also exhibits this issue. I thought it only came up in my definition of sSQL used in the query. But the issue will also arise in the Find command used in my DAP. So I had to just do something like this: qryAuthor = Replace(qryAuthor,"'","''") and that fixed it. I was spending all my time looking at the sSQL thinking that was where the issue was. It was but I didn't realize that the single-quote issue also comes up in the Find I was using. Wll that one's done. Thanks for your help. -- George Hester _________________________________ > Sorry George, > [quoted text clipped - 59 lines] > >> > George Hester > >> > _________________________________ >I have this SQL in a query: > [quoted text clipped - 6 lines] >use '' I get an error . I have tried Chr(39) and ' none of which work. >Any ideas how to query on this character? Thanks. Try replacing the ' in the query string with two consecutive ' marks: "WHERE tblTest4.Caption_Name LIKE '%" & Replace(qryAuthor, "'". "''") & "%';" For clarity (don't do it this way!!) that's replacing " ' " with " ' ' ". The latter will be treated as just a single singlequote. John W. Vinson [MVP] Yes John that was the easiest way of fixing it. I just had to do it in two places instead of one. Since I used ' in various places in the Database I didn't realize that the issue also comes up here: MSODSC.RecordsetDefs.Item(0).ServerFilter = myFilter Where myFilter contains database entries with '. -- George Hester _________________________________ > >I have this SQL in a query: > > [quoted text clipped - 16 lines] > > John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.