Run-time error 80040e14 (Missing Operator) in '% ... Writer's Choice .... %"

I think I know the problem.  I don't know the solution.  Here's my code...

          strSQL = "SELECT * FROM [Item Types] WHERE [Title] like '%" & 
strTestPhrase & "%'"
          rstItems.Open Source:=strSQL, ActiveConnection:=cnnAllotment, _
            CursorType:=adOpenKeyset, LockType:=adLockPessimistic

Here's the strTestPhrase as interpretted by the error message...
  '[Title] like '%Glencoe Writer's Choice  Joshual Edel%"

Here's the error message...
  Syntax error (missing operator) in query expression '[Title] like 
'%Glencoe Writer's Choice  Joshual Edel%".

I believe it's the apostrophe in the word Writer's that triggers this one. 
How can I change my code to accomodate this situation?  BTW, I'm searching 
book titles which means the title could contain most anything.  The other 
thing that got my attention are the two spaces before Joshual.

thank you for your suggestions.



0
WDSnews
12/9/2009 12:50:44 AM
access 16762 articles. 3 followers. Follow

2 Replies
1163 Views

Similar Articles

[PageSpeed] 5

WDSnews wrote:
> I think I know the problem.  I don't know the solution.  Here's my 
> code...
>
>           strSQL = "SELECT * FROM [Item Types] WHERE [Title] like '%" 
> & strTestPhrase & "%'"
>           rstItems.Open Source:=strSQL, 
> ActiveConnection:=cnnAllotment, _
>             CursorType:=adOpenKeyset, LockType:=adLockPessimistic
>
> Here's the strTestPhrase as interpretted by the error message...
>   '[Title] like '%Glencoe Writer's Choice  Joshual Edel%"
>
> Here's the error message...
>   Syntax error (missing operator) in query expression '[Title] like 
> '%Glencoe Writer's Choice  Joshual Edel%".
>
> I believe it's the apostrophe in the word Writer's that triggers this 
> one. How can I change my code to accomodate this situation?  BTW, I'm 
> searching book titles which means the title could contain most 
> anything.  The other thing that got my attention are the two spaces 
> before Joshual.
>
> thank you for your suggestions.

Try to doubling up the apostrophe, i e

....like '%" & Replace(strTestPhrase, "'", "''") & "%'"

i e, replace one occurrence of apostrophe with two

-- 
Roy-Vidar


0
RoyVidar
12/9/2009 11:41:50 AM
I'll try it.  thank you.


"RoyVidar" <roy_vidarNOSPAM@yahoo.no> wrote in message 
news:mn.4af97d9ca847c530.85221@yahoo.no...
> WDSnews wrote:
>> I think I know the problem.  I don't know the solution.  Here's my 
>> code...
>>
>>           strSQL = "SELECT * FROM [Item Types] WHERE [Title] like '%" & 
>> strTestPhrase & "%'"
>>           rstItems.Open Source:=strSQL, ActiveConnection:=cnnAllotment, _
>>             CursorType:=adOpenKeyset, LockType:=adLockPessimistic
>>
>> Here's the strTestPhrase as interpretted by the error message...
>>   '[Title] like '%Glencoe Writer's Choice  Joshual Edel%"
>>
>> Here's the error message...
>>   Syntax error (missing operator) in query expression '[Title] like 
>> '%Glencoe Writer's Choice  Joshual Edel%".
>>
>> I believe it's the apostrophe in the word Writer's that triggers this 
>> one. How can I change my code to accomodate this situation?  BTW, I'm 
>> searching book titles which means the title could contain most anything. 
>> The other thing that got my attention are the two spaces before Joshual.
>>
>> thank you for your suggestions.
>
> Try to doubling up the apostrophe, i e
>
> ...like '%" & Replace(strTestPhrase, "'", "''") & "%'"
>
> i e, replace one occurrence of apostrophe with two
>
> -- 
> Roy-Vidar
>
> 


0
WDSnews
12/10/2009 7:51:11 PM
Reply:

Similar Artilces:

running balance
Hi I need help figuring out a formula for a running balance. I have 3 columns one called deposits, another called petty cash disbursements and another called expenses. I want to be able to know how can I get the total amount of money that I have on an account even though I keep on entering different dollar amounts at all times. I know that the formula =A39-B39 is giving me the amount for the first two columns, but I don't know what to do for the third column. I would really appreciate it if someone can help me out. Thanks. Jackie deposits-disbursements-expenses+previous balance ...

Found contact too many times
I find it irritating when I try to use contacts sometimes. I create a contact, add a phone number, a fax number and and a nick name. I start an e-mail. For a while entering the nick-name works, and then it stops working and I cannot even FIND the name when I enter it (when I e-mail myself at home, my nick-name was "me", and that worked for over a year. I forgot what I did to get it to work last time). So I start typing in the name and let it auto-complete. It gives me a choice between e-mailing to two identical versions of my e-mail address (although I can only find it once...

database connection error
I have my batabase and POS on the same machine as my operation is very small. I find that when the network cable is unpluged i get a database connection error. Can someone explain why that happens if the POS is pointing to the database that is on the same machine and not over a network? Set SO Administrator - File - Configuration - Servername to: (local) instead of your ip address or computer name. This may also be an issue with your SQL server client network utility. "Andaiye" wrote: > I have my batabase and POS on the same machine as my operation is very small. ...

Outlook still running in background
When I closeout outlook then try and shut the computer down it tells me it is having trouble shutting outlook down and it gives me a choice to "end now" but that doesn't even help. The only thing that I can do is shut the computer off. Or I go into processes and it shows outlook still running in the back ground then I end the task that way in order to shut the computer down. This only started a few weeks a go and they only thing that I do is instal any windows updates or virus updates. I have tried reinstalling office and xp with out any sucess. Any suggestions? ...

Unknown Error 0x80040119
Hello! Having some pretty huge issues with this error - Unknown Error 0x80040119 this occurrs when the Send/Receive button is pressed in Outlook 2003 on a Terminal Server session. Scenario: Users accesses email from a Notebooks out of office using VPN. Outlook is configured to connect an exchange server in cached mode. This particular user travels frequently and also accesses their email via Outlook on our Terminal Servers which is when the problem ocurrs with "Unknown Error 0x80040119". http://support.microsoft.com/?kbid=814441 On Tue, 22 Mar 2005 01:47:00 +0100, "A...

What does "Compile error in hidden module:Autoexec New" mean?
I am receiving this error message when i start excel, with saved excel programs as well as new ones I am creating. A PDFMaker add-in problem. http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307410 -- Jim "Karla" <Karla@discussions.microsoft.com> wrote in message news:B5C336C6-10D3-40BE-8146-C0491DA16804@microsoft.com... |I am receiving this error message when i start excel, with saved excel | programs as well as new ones I am creating. ...

Failed Recent Updates and Error Code 80070490
Since yesterday my computer and me have been trying to install two Vista updates: Security Updates for Windows Vista (KB967723) and Updates for windows Vista (KB972145) and I get the Error Found Code 80070490 I have Windows Vista Ultimate. Please advise. Hello Octavio, 0x80070490 - ERROR_NOT_FOUND Corrupted System File; Need to perform an in-place upgrade or reinstall of the OS. How to use the System File Checker tool to troubleshoot missing or corrupted system files on Windows Vista or on Windows 7 <http://support.microsoft.com/kb/929833> Tell...

Outlook "The object could not be found" error (O2003)
Hi there, In Outlook 2003, whenever I have an email with a email link inside it I get an error "The object could not be found", when I try and click it. This has only happened since I installed my Activesync software for my PDA. any help? any fixes? This is killing me! Have you rebooted since installing AS? Does Event viewer give you any additional information? Did you try reregistering IE? reregister urlmon.dll by clicking start, run and typing regsvr32 urlmon.dll if that fails, try How to Reinstall or Repair Internet Explorer and Outlook Express in Windows XP http://...

Times Addition
Hi I need to know how to add 3 times : 8:00+2:00+3:00=13:00 Thanks The Date data type in Access is intended for timestamps, not durations. Rather than attempting to add durations in that manner, you should store the durations as Long Integers (either as total seconds or total minutes, depending on what resolution you require) and add them together. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Chipcom" <bz1977@gmail.com> wrote in message news:1190828761.862171.250540@o80g2000hse.googlegroups.com... > Hi > > I need to kn...

How do I apply borders one side at a time
In Publisher I am trying to have a border on the top, on the bottom, on the leftside, but NOT on the rightn side. Can I do this? How? Select the object, click the line icon, more lines (styles, in 2000 and below) select the right side, click none. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "the dr mike" <the dr mike@discussions.microsoft.com> wrote in message news:50E30E0F-85FC-4283-893F-D72FC2FE60CD@microsoft.com... > In Publisher I am trying to have a border on the top, on the bottom, on the > leftside...

"Messaging interface has returned an unknown error"
I'm running Outlook 02 on Win XP Pro SP2. I think this may have been caused by the DST time zone update utility, but I'm not certain. Here's the situation. If I try to open a calendar item I get the following error: "Can't open this item. The messaging interface has returned an unknown error. If the problem persists, restart Outlook." If I try to create a new message or appointment from the quick start toolbar, or clicking a mailto: link on a web page: "The operation failed." If I click the "to" "cc" or "bcc" button while...

Time Format to Text Output
Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 1...

Get/Change operation on table 'SY_Error_Messages_MSTR'
Hi all, We upgraded from GP version 9 to version 10 over the past weekend. I have a user who is entering Payables Transactions and she is receiving the following message when she tries to enter a duplicate PO#.... Unhandled database exception: A get/change operation on table 'SY_Error_Messages_MSTR' cannot find the table. Invalid object name 'SY01700' So based on the error, the incorrect message is appearing to the user. Can I rebuild the SY01700 table? Is this really the issue? Any help would be greatly appeciated! Thanks, Lori The SY01700 Table holds the error mess...

Mail Relaying denied error message, how can it be fixed???
I setup outlook express smtp to use incoming: mail.tpg.com.au and outgoing: mail.tpg.com.au I can successfully receive emails and read newsgroup but I can't send out emails because it keep on return the below error message, how can it be fixed so that I can send emails? Thanks... The error message when sending is::: The message could not be sent because one of the recipients was rejected by the server. The rejected e-mail address was 'huy9876@hotmail.com'. Subject 'Re: Again', Account: 'mail.tpg.com.au', Server: &...

SQL2008 connection error
Hi, I just migrated SSRS2005 tp SSRS2008. My question is, 1. I can't connect to SQL 2008 at all, I tried ODBC and SSRS reports. TCPIP is enabled. 2. Now the SSRS 2008 url is http://servername /prodreports, How do I change the URL to reports.company.com Thnaks, Hi, can you try outside of SSRS (via ODDB Admin) if you are able to connect to the ODBC datasource. If you are using SQL 2008 I would recommend the Sql Server Managed Provider instead of ODBC. You can change the url reservations in the RS Config Tool (RSCONFIGTOOL.EXE) Best Regards Klaus Sobel &...

Userenv errors 1058 and 1030 on clients
Hello, I'm bursting my head with this already. Clients complaint that their logon 50% of the tiimes don't sync (using redirected folders). If they cancel the sync, or either logon without lan, and after that plug in and sync manually, it goes ok. It just doesn't happen most of the times at logon and logoff times. On the server side I have no events on this. On the client side I have plenty, all stating that: "Windows cannot access the file gpt.ini for GPO cn={6C5E2B58-133A-4744-8CDD-89D66A73793F},cn=policies,cn=system,DC=iasopor,DC=local. The file must ...

Formula Cell Error
Hi all, i have a problem with the following code, would appreciate if anyone can give me some help on this. Basically everything works fine except for this line: Cells(i, 12).FormulaR1C1 = "=SUM(J" & StartCount & ":J" & EndCount & ")" The cell will reflect "=SUM('J2':'J3')" instead of "=SUM(J2:J3)". --------------------------------------------------------------------------------------------- Sub Test() For i = 2 To 100 Step 1 Cells(i, 1).Select If Cells(i, 1).Interior.ColorI...

Error
My HR users are seeing a new error when trying to update one of the fields on the "Position Extra Fields" Screen (located at "Position Setup" Screen -> "Position Extra Fields" Screen) The error is "This employee record has been changed since you opened this window. Changes won't be saved." I can duplicated it also, even when signed in as admin. I tried tracing it but see nothing that helps. A SQL trace shows an attempted insert to HRPOS022 then next a Select to Dynamics..Sy01700 (holds the error messages) Ca...

Money internal error when I download from bank
Help! was using M04, recently upgraded to M05. After new install was prompted to use MSN passport- realised afterwards I didnt want to. After reinstall and restore from backup, I am now finding that when I download from Royal Bank (Canada) there is an "internal error" and money restarts. It often does this 2 or 3 times on restart, and Ive lost the downlaod data from the bank- Im fed up with having to restart and type entries by hand. Have I a setting wrong somewhere? ...

REPOST Re: Trying to get mailbox lock from process
Has anyone made any headway on this issue? I am currently trying to determine if any informative messages are logged on the server side. --=C ####################### Hi Brent, Is it possible the mailbox is being accessed by another client at the same time? -Michael On 10/21/04 12:38 PM, in article BD9D5E5D.473D%brent@hazmatexplo.org, "Brent DeCracker" <brent@hazmatexplo.org> wrote: >> Michael, >> >> Just to throw my two cents in - I'm having the same problem, and it didn't >> occur until I upgraded to 11.1. I've been using th...

Missing Fonts when saved in PDF
On occasion, when I save my publication in PDF form, the fonts will change. Are there certain fonts which are not allowed? How do I get them? Not all fonts can be embedded. There's nothing you can do about that except to use fonts than can be. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "bellarush" <bellarush@discussions.microsoft.com> wrote in message news:036AE470-3C94-4298-9FEB-AC929F16A30A@microsoft.com... > On occasion, when I save my publication in PDF form, the fonts will > change. &...

Item Price List Copy Error
Hello - I am using GP 9.0 and have been utilizing the "Copy Price List" option from the price list maintenance window. This has not given me any problems in the past. However, now when I go to copy a price list (I select the item#, select the source item #, and then when I go to process, an error message comes up on the report printout that states: "Item in use or has been edited by another user". We have no idea why this is happening as no one else is editing the item. Please advise as to what we might do to fix this error. Thanks in advance! Are you sure th...

Windows Live Mail Error
For the last two days I have been trying to send some emails, but I'm getting and error message: Windows Live Mail Error ID: 0x800CCC68 That's not the complete error message. Right-click on your error message, copy, then paste it into a reply = here.=20 We can't do much troubleshooting without the complete error message. =20 --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP program: http://mvp.support.microsoft.com "Teresita" <Teresita@discussions.microsoft.com> wrote in message = news:3123D0B2-59CE-4F44-BFA6-25883FB7F6BC@microsoft.com... ...

page setup dialog and paper choices
I need some help with the dialog Page Setup Dialog. When the dialog comes up, it brings up all the available choices for paper that the printer supports - Letter, Legal, Envelope etc.. I would like to know how I can add a custom paper to this list. This is a pre-printed sheet (the size is actually standard letter), and I want the user to be able to select it & the program to know when the user has selected it. If not, Do I need to derive a class from CPageSetupDialog , add code to add this custom paper to the combo-box list, trap messages etc.. Thanks in advance rajas, Even if the ...

Backup error Access Denied
I have been recieving the following error message for a while and can't figure out how to fix it. It is only happening on one mailbox. "Backup - \\MBMAIL\Microsoft Exchange Mailboxes Access denied to file Jhon Doe [jdoe]Top of Information StoreSync IssuesServer FailuresMail Delivery (failure jdoe@xyz.com)." Help Please Can you access the mailbox from a mail client? If so, you can use mdbvu to look at the Top of the Information store folder, and delete the message in there. If you cannot access the mailbox, then I have seen it where some users think they are ...