Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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
0
Chi Is Current
Asked:
Chi Is Current
  • 15
  • 9
  • 4
  • +2
1 Solution
 
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 MVP, Access and Data Platform)Commented:
Jacob ... what is the Row Source SQL for this combo?

btw ... the first post was not Access.

mx
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
"The same problem......."

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

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 15
  • 9
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now