Quote marks (") in field values of combo box

Data = Data.Trim(Chr(34))
or
Data = Data.Replace(""", "Inch")

Hmmmmmmm  I'm not seeing how to apply this to a combo box look up?

Jacob ... what is the Row Source SQL for this combo?

btw ... the first post was not Access.

mx

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.

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];

This should remove the "" for display in the combo box.

mx

The auto complete continues to stop working after the single double quote is entered in the combo box.

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.

"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

> 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).

I'm also working on this VERY similar issue, in a DLookup statement:
https://www.experts-exchange.com/questions/25045622/Single-Set-of-Double-Quotes-in-field-value-in-DLookup-Statement-Error-3075.html

Jacob

This portion of the SQL I posted s/b removing the double quotes:

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

mx

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.

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"

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.

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!

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

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.

..........

"The same problem......."

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

mx

"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

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

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

> That's the idea.

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

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°)

Sorry, there was a serious refresh problem. The last visible comments when I posted were over two hours old. Please disregard my comments. -- (°v°)

°v° - It's the built-in auto-complete that ahppens w/ combo boxes.  Thanks.

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°)

°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.

Text I type:

1/2" tape


Field value I'm looking for:

1/2" tape

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°)

!!!!

????

&*@#%!$