?
Solved

Quote marks (") in field values of combo box

Posted on 2010-01-11
31
Medium Priority
?
496 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:Chi Is Current
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 9
  • 4
  • +2
31 Comments
 
LVL 3

Expert Comment

by:MBoy
ID: 26287173
Data = Data.Trim(Chr(34))
or
Data = Data.Replace(""", "Inch")
0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26287203
Hmmmmmmm  I'm not seeing how to apply this to a combo box look up?
0
 
LVL 75
ID: 26287226
Jacob ... what is the Row Source SQL for this combo?

btw ... the first post was not Access.

mx
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 2

Author Comment

by:Chi Is Current
ID: 26287276
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
 
LVL 75
ID: 26287327
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
 
LVL 75
ID: 26287331
This should remove the "" for display in the combo box.

mx
0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26287350
The auto complete continues to stop working after the single double quote is entered in the combo box.
0
 
LVL 3

Expert Comment

by:ClarkFilter
ID: 26287378
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
 
LVL 75
ID: 26287463
"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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26287506
> 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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26287516
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
 
LVL 75
ID: 26287551
This portion of the SQL I posted s/b removing the double quotes:

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

mx
0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26287565
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
 
LVL 3

Expert Comment

by:ClarkFilter
ID: 26287724
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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26288266
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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26288317
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
 
LVL 75
ID: 26288531
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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26288707
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
 
LVL 75
ID: 26288719
"The same problem......."

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

mx
0
 
LVL 75
ID: 26288780
"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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26288828

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

Run-time error 3075, (missing operator).  The same problem......
0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26288849
> That's the idea.

Hmmmmmmmmmmm.....  'Sounds like a possibility.  Thank you, mx!
0
 
LVL 58

Expert Comment

by:harfang
ID: 26288882
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
 
LVL 58

Expert Comment

by:harfang
ID: 26288897
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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26288923
°v° - It's the built-in auto-complete that ahppens w/ combo boxes.  Thanks.
0
 
LVL 58

Expert Comment

by:harfang
ID: 26288990
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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26289105
°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
 
LVL 2

Author Comment

by:Chi Is Current
ID: 26289111
Text I type:

1/2" tape


Field value I'm looking for:

1/2" tape

0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 26289154
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
 
LVL 58

Expert Comment

by:harfang
ID: 26289264
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
 
LVL 2

Author Closing Comment

by:Chi Is Current
ID: 31675722
!!!!

????

&*@#%!$
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question