Quote marks (") in field values of combo box

Hello ~ Sometimes field values selected with a combo box contain double quotes, i.e. [1/2" threaded pipe caps].  I'm noticing the auto complete ceases to function after the quote marks when the item description exists in the lookup table.

Can you suggest a remedy?

Thank You, Jacob
LVL 2
Chi Is CurrentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MBoyCommented:
Data = Data.Trim(Chr(34))
or
Data = Data.Replace(""", "Inch")
0
Chi Is CurrentAuthor Commented:
Hmmmmmmm  I'm not seeing how to apply this to a combo box look up?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Jacob ... what is the Row Source SQL for this combo?

btw ... the first post was not Access.

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Chi Is CurrentAuthor Commented:
Hi mx- Thank you for your reply.

The row source for the combo box is:

SELECT [Product Table].[Product #], [Product Table].[Product Desc], [Unit Table].[Unit Desc], [Product Table].Price, Vendor.[Vendor Name] FROM ([Unit Table] INNER JOIN [Product Table] ON [Unit Table].[Unit #] = [Product Table].Unit) INNER JOIN Vendor ON [Product Table].[Vendor #] = Vendor.[Vendor ID] WHERE ((([Product Table].[Vendor #])=[forms]![frmInputPOs]![Vendor List])) ORDER BY [Product Table].[Product Desc];

The single set of double quotes are in the [Product Table].[Product Desc] field.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try this:

SELECT [Product Table].[Product #], Replace([Product Table].[Product Desc], Chr(34),"") As Description  , [Unit Table].[Unit Desc], [Product Table].Price, Vendor.[Vendor Name] FROM ([Unit Table] INNER JOIN [Product Table] ON [Unit Table].[Unit #] = [Product Table].Unit) INNER JOIN Vendor ON [Product Table].[Vendor #] = Vendor.[Vendor ID] WHERE ((([Product Table].[Vendor #])=[forms]![frmInputPOs]![Vendor List])) ORDER BY [Product Table].[Product Desc];
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This should remove the "" for display in the combo box.

mx
0
Chi Is CurrentAuthor Commented:
The auto complete continues to stop working after the single double quote is entered in the combo box.
0
ClarkFilterCommented:
SELECT [Product Table].[Product #],     Replace([Product Table].[Product Desc],'"',"") as [Product Desc] , [Unit Table].[Unit Desc], [Product Table].Price, Vendor.[Vendor Name] FROM ([Unit Table] INNER JOIN [Product Table] ON [Unit Table].[Unit #] = [Product Table].Unit) INNER JOIN Vendor ON [Product Table].[Vendor #] = Vendor.[Vendor ID] WHERE ((([Product Table].[Vendor #])=[forms]![frmInputPOs]![Vendor List])) ORDER BY [Product Table].[Product Desc];


For illustration, I put some spaces in the quotes:
     Replace([Product Table].[Product Desc],'  "  ',"  ")
Notice that I enclosed my double quotes within single quotes in the first set of quotes.
The second set of double quotes has nothing between them.

So, with what I have above, you search for double quotes, and replace them with a zero-length string.  You can choose to replace them with whatever you want, such as " inch" or " in.", etc.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"The auto complete continues to stop working after the single double quote is entered in the combo box."
Are the double quotes still being displayed ?

mx
0
Chi Is CurrentAuthor Commented:
> Are the double quotes still being displayed ?

Yes, they continue to be displayed in the combo box along with whatever else is added, when the description already exists in the table (and even if it doesn't).
0
Chi Is CurrentAuthor Commented:
I'm also working on this VERY similar issue, in a DLookup statement:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_25045622.html

Jacob
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This portion of the SQL I posted s/b removing the double quotes:

Replace([Product Table].[Product Desc], Chr(34),"") As Description

mx
0
Chi Is CurrentAuthor Commented:
ClarkFilter ~

Thank you for your reply.  Using your statement as the row source for the combo box results in the same behavior: the autocomplete stops working.

The combo box works fine when strings without single quotes are entered.
0
ClarkFilterCommented:
Suppose someone is trying to enter "test" (including the double quotes).  What happens when the user enters the first non-quote character?  I did some testing, and once I entered:

"t

it autocompleted to:

"test"
0
Chi Is CurrentAuthor Commented:
ClarkFilter -

What's happening is, the quotes are removed from the string.

If the word ["test"] (with quotes) exists in the look up table, I CAN enter [test] (without quotes) into the combo box, it is found and displays as [test] (without quotes).

The problem arises when the single set of double quotes [Chr(34)] are desired in the string.

In the above example, if I type the word ["test"] (with quotes), it is not found and sets off the 'On Not In List' event.

Really, what I need is to be able to enter are strings like [1/2" tape] and retain the double quotes - in the lookup, autocomplete and display.

0
Chi Is CurrentAuthor Commented:
mx -

Same comment as above for your statement.  I'd like the combo box to treat double quotes Chr(34) as any other character.  i.e.  [1/2" tape]....

Am I dreaming?

Many Thanks!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I see.  Well, not seeing how that's going to happen.  Quote and double quotes contained within text itself is generally problematic.  In this case, if it terminates the auto complete ... the only solution I can see is to remove them from the combo display (the field being auto completed) and then no enter them when typing.

Also, in your other Q ... with the single quote  solution ... what happens if the look up text has instead of a double quote ... a single quote ?

mx
0
Chi Is CurrentAuthor Commented:
Part 1 - OK, I'll have to think about this...

> what happens if the look up text has instead of a double quote ... a single quote ?
Ahhhhhh, Obi-Wan, Run-time error 3075.  The same problem.......

It's difficult to imagine Access databases everywhere being unable to look up the field value of  [1/2" tape], and the like in combo boxes.

..........
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"The same problem......."

For fun ... try the solution I posted there ...   I would like to know.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"It's difficult to imagine Access databases everywhere being unable to look up the field value of  [1/2" tape], and the like in combo boxes."

One way to get around this is ... roughly ...
Have an unbound text box for typing in the search text.
You display the results in a list box.  
The query driving the list box filters on the text box.
However ... in the query ... you use the InStr() function in the criteria related to the text box.  InStr() avoids issues with special characters like quotes and double quotes.
More ... you use the OnChange event of the text box to Requery the list box on every keystroke.  So ... and the user starts typing ... the list initially populates with N items ... and is eventually reduced down to a single item(s) ...

That's the idea.

mx
0
Chi Is CurrentAuthor Commented:

>For fun ... try the solution I posted there ...   I would like to know.

Run-time error 3075, (missing operator).  The same problem......
0
Chi Is CurrentAuthor Commented:
> That's the idea.

Hmmmmmmmmmmm.....  'Sounds like a possibility.  Thank you, mx!
0
harfangCommented:
jacobbarnett,

I read this twice, and can't make sense out of it. What type of auto-complete are we talking about? the default built-in feature where a line gets selected and the field is completed, or about some mechanism you built yourself?

(°v°)
0
harfangCommented:
Sorry, there was a serious refresh problem. The last visible comments when I posted were over two hours old. Please disregard my comments. -- (°v°)
0
Chi Is CurrentAuthor Commented:
°v° - It's the built-in auto-complete that ahppens w/ combo boxes.  Thanks.
0
harfangCommented:
In that case, I was unable to reproduce the problem. Auto-complete works over all Unicode lists. Long shot: what double-quotes? Those from the keyboard (double straight quotes) or some "smart" typographical quotes ('99' quotes)? If some form of auto-correct is applied while typing (does Access 2007 play with "smart" quotes?) or was applied to the data, I could see this happening.

Try to copy-paste both the text you type (including the double quotes) and the text being searched right here.

(°v°)
0
Chi Is CurrentAuthor Commented:
°v° - Thank you for having a look at this and your thoughts:

When a field value contains a double quote (Chr(34)) in a lookup table, I'd like the combo box to find it.

IF I have a field value like [1/2" tape] (no brackets) in the lookup table, the combo box's lookup feature does not see it.
0
Chi Is CurrentAuthor Commented:
Text I type:

1/2" tape


Field value I'm looking for:

1/2" tape

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Actually ... I can't reproduce the problem either.

I created this Value List:

1/2" tape;1/8"dongle;1/4"Hose;3/4"rod;1/8"jammer

I can type 1/8"  ... and get 1/8"     .... I further type a j   ... and I get 1/8"jammer.

?

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harfangCommented:
Jacob,

Was your comment a copy-paste? We both didn't observe this behaviour... Perhaps try to isolate the problem in a new form, with just the combo box and nothing else.

(°v°)
0
Chi Is CurrentAuthor Commented:
!!!!

????

&*@#%!$
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.