Form won't detect apostrophes in search box when looking up from my query

[accesssux]

I'm no access buff: But I need some help! I have a form that searches by company name that run off these queries:


SELECT Contacts.[Prospect ID], Contacts.County, Contacts.Company, Contacts.Salesperson, Contacts.[Type of Business], Contacts.Title, Contacts.[Last Name], Contacts.[First Name], Contacts.[Location Address], Contacts.City, Contacts.State, Contacts.Zip, Contacts.[Office Phone], Contacts.Ext, Contacts.Fax, Contacts.[Cell Phone], Contacts.[Email Address], Contacts.[PO Box], Contacts.Customer, Contacts.Website, Contacts.[Reffered By], Contacts.[Alternate Phone], Contacts.[Contact 2], Contacts.[Proposal Date], Contacts.[Materials and Labor], Contacts.[Yearly Savings], Contacts.[KW saved], Contacts.[KWH /year saved], Contacts.[# New Fixtures], Contacts.[Installed project cost], Contacts.[Actual Savings], Contacts.[Installed # of Fixtures], Contacts.[Start Date], Contacts.[Action Plan], Contacts.[Level of Sale], Contacts.Prospect, Contacts.[Physical Data], Contacts.[Proposal Amount], Contacts.[Contingencies Removed], Contacts.[Power Company], Contacts.[Installed Date], Contacts.Notes, Contracts.[Open Contract], Contracts.[Contract Execution Date]
FROM Contacts LEFT JOIN Contracts ON Contacts.[Prospect ID] = Contracts.[Prospect ID]


ORDER BY Contacts.Company;



THE SECOND ONE:


SELECT Contacts.[Prospect ID] AS [Contacts_Prospect ID], Contacts.County, Contacts.Company, Contacts.Salesperson, Contacts.[Type of Business], Contacts.Title, Contacts.[Last Name], Contacts.[First Name], Contacts.[Location Address], Contacts.City, Contacts.State, Contacts.Zip, Contacts.[Office Phone], Contacts.Ext, Contacts.Fax, Contacts.[Cell Phone], Contacts.[Email Address], Contacts.[PO Box], Contacts.Customer, Contacts.Website, Contacts.[Reffered By], Contacts.[Alternate Phone], Contacts.[Contact 2], Contacts.[Proposal Date], Contacts.[Materials and Labor], Contacts.[Yearly Savings], Contacts.[KW saved], Contacts.[KWH /year saved], Contacts.[# New Fixtures], Contacts.[Installed project cost], Contacts.[Actual Savings], Contacts.[Installed # of Fixtures], Contacts.[Start Date], Contacts.[Action Plan], Contacts.[Level of Sale], Contacts.Prospect, Contacts.[Physical Data], Contacts.[Proposal Amount], Contacts.[Contingencies Removed], Contacts.[Power Company], Contacts.[Installed Date], Contracts.[Prospect ID] AS [Contracts_Prospect ID], Contracts.[Open Contract], Contracts.[Power Co Rate Schedule], Contracts.[Project Name], Contracts.[Contract Amount], Contracts.[Contract Execution Date], Contracts.[Credit Required], Contracts.[Rebate Claim # (or Electric Meter #)], Contracts.[Preapproval App Sent Date], Contracts.[Preapproval App Sent Amount], Contracts.[Recieved Preapproval Date], Contracts.[Recieved Preapproval Amount], Contracts.[PreInstall Completed Date], Contracts.[Proper Contract w EPS Codes], Contracts.[PreInstall Inspection], Contracts.[Initial EPS Spreadsheet w Codes], Contracts.[Initial Power Co App Sent], Contracts.[Initial Power Co Lighting Form Sent], Contracts.[Power Co Preapproval Recieved], Contracts.[Credit Approved], Contracts.[Credit App Sent], Contracts.[Credit App Recieved], Contracts.[Entered in Peachtree], Contracts.[PostInstall Date Completed], Contracts.[Final EPS Spreadsheet w Codes], Contracts.[EPS PostInstall Inspection], Contracts.[Final Power Co App Sent], Contracts.[Final Power Co Lighting Form Sent], Contracts.[Final Power Co Approval Recieved], Contracts.[Final Draft Invoice Done], Contracts.[Installation Dates], Contracts.[Rebate Invoice Done], Contracts.[Rebate Final App Sent Date], Contracts.[Power Co Inspection Date], Contracts.[Final Requested Amount], Contracts.[Final Rebate Amount] AS [Contracts_Final Rebate Amount], Contracts.[Rebate Payable to], Contracts.[Rebate Paid], Contracts.[Rebate Recieved Date] AS [Contracts_Rebate Recieved Date], Contracts.[Contract Paid in Full], Contracts.Notes AS Contracts_Notes
FROM Contacts LEFT JOIN Contracts ON Contacts.[Prospect ID] = Contracts.[Prospect ID]
WHERE (((Contracts.[Open Contract])=Yes))
ORDER BY Contacts.Company;


Don't know how to fix it.

[Helen]

I had a similar problem with a text field that could commonly be expected to have apostrophes in it. To deal with it, before running a query, I replaced all the occurrences of the apostrophe with what looks like an apostrophe, but isn't the keyboard version, but is actually alt-239.

Since the RunSQL statement needs to be a string (in double quotes), strings WITHIN that are identified using apostrophes - hence your problem when an apostrophe appears in a string field.

DoCmd.RunSQL "UPDATE " & strTable & " SET " & strTable & "!" & strField & " = Replace(" & strTable & "!" & strField & ",""'"",'´');"
in the above, after the last strField is: & ",""apostrophe"",'alt-239');"
You can Create Query to make an UPDATE query to do the same thing of course, without it being coded as a RunSQL.

Do what you need to (run your big query in your post) then do the opposite replace business to put the apostrophes back:

DoCmd.RunSQL "UPDATE " & strTable & " SET " & strTable & "." & strField & " = Replace(" & strTable & "." & strField & ",'´',""'"");"
in the above, after the last strField is: & ",'alt-239',""apostrophe"");"

Solved my problem perfectly, hope it does the same for you.

[accesssux]

Helen,
Thank you so much for your reply, and I'm sure its very helpful - but I am soooo confused. I don't know SQL or where I put that code in. I'm sure its pretty easy but, do you think you could run that by me in simpler terms? Is there a difference between SQL and RunSQL? And how would I get to the runSQL?
Danielle

[accesssux]

P.S. I understand the idea with the apostrophes. But maybe I'm looking at the wrong code. There doesnt seem to be any apostrophes in my current code. Hmmm.

[Helen]

Sorry - I have this as code in an event.

You could do it as an update query that you run before those you posted, in the 'Update to' box, put Replace(TableName!FieldName,"'","´")
to replace the apostrophe with the single high dash (alt-239)

and then have another to put the apostrophes back after your posted queries run.