ComboBoxes and Apostrophe's 1

[needadvice]

I Have A ComboBox that looks up company names from a table.
If the name has an apothrophe in it, the search fails. I can't put quotation marks around the name in the table, because it will come out with them in other areas of the application.
Is there an answer to the problem?
Why is it an Access table "find" has no problem with apostophe's but Comboboxes do?
Can anyone help?

 

[LonnieJohnson]

Is your SQL embedded in VBA? If so, then you will need to use Chr(34) instead of quotes.

Just a thought.

I may be wrong on the ascii number for the quote character. I am in a class right now and am just guessing on the number. prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[needadvice]

This ComboBox was Created by a wizard. I didn't write any code for it. It just can't recognize any record with an apostrophe in it. I can't believe I'm the only developer who ever encountered this problem. I must me doing someting wrong, but what?

 

[zambezibill]

To help determine where the problem lies, open up the query (SQL statement) used as the combo box's row source and see if all the desired records are showing up there.

Let us know what you find.

Cheers, Bill

 

[Guest_imported]

This is the sql on the ComboBox row source. The problem is it can't find any company name that has an apostrophe in it.

SELECT [Taft].[COMPANY], [Taft].[NOTICE RECEIVED] FROM Taft ORDER BY [Taft].[COMPANY];

 

[LonnieJohnson]

I just recreated your scenario. I have a table with values that have an apostrophe. I created a combo box with pretty much the same sytax as above with the exception of table and column names. It works for me. Can you send me a copy of your form and the table that the combo box is getting the data? You may want to try creating another combo box on a test form first and see if you still have the problem. You can mail it to me if you like at the mail below.

...

prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[needadvice]

Lonnie,
Thanks for your response. I can't send the table because it contains Privacy Act Info.
I set up a new table and new form with comboBox. The same thing happens. If I enter a record with an apostrophe, I get an error "Syntax error in expression" on the combobox code. The combobox was made by the wizard, Any other record can be found without this problem. For some reason Access is reading an apotrophe for what it actually is (a single quotation mark). I must be missing something here, but what?

 

[rajeessh]

Generally when u execute a query or something like

Select * from Table where ColumnName = 'ss's'

must be made as

Select * from Table where ColumnName = 'ss''s'

 

[needadvice]

This is the line of code on the combobox that has the problem;

rs.FindFirst "[Company]& [notice received]= '" & Me![Combo118].Column(0) & Me![Combo118].Column(1) & "'"

It says there is a missing operator. Any ideas?

 

[sko]

If your company table has both a companyID and company name, maybe you should include both ID and Name in your combo box. Then, hide the companyID but use it in your code.

 

[MichaelJ]

The syntax you are using does not allow for the use of apostrophes within the value of the variable itself (combo box value). This will always generate a run-time error. The fix for this is using double quotes. I recommend using a variable as suggested by Lonnie, it is much easier than trying to follow the doubles in code. Dimension the variable as a string and then assign it the value of Chr$(34). Try the following in your code block:

Dim strQuote as string
strQuote = Chr$(34)

rs.FindFirst " [Company] & [notice received] = " & strQuote & Me![Combo118].Column(0) & Me![Combo118].Column(1) & strQuote

Also, I recommend using an underscore in the place of a space in your field names. Have fun.

Mike
michaelj1@cox.net

 

[needadvice]

Thanks Michael,
Your code fixed the problem and the search works perfectly.
No more error messages....Hurray!