String Character Conflict

[NetNomad312]

First, I should apologize for the last thread I made... I didn't look at the FAQs first (and I guess that's why no one replied). This, though, I couldn't find... although maybe I missed it.

I need to send this command to a subform:

Me.Subform.Form.filter = filter

"filter" is the string I created - it is set to the contents of a list box at the point where the user double-clicks it. However, some of these items in the list contain the apostrophe character, #39. This is unavoidable, I can't change this. The problem is, that the filter property must be set using that character, like

FIELD = '[whatever]'

...and whenever the string I have contains one of these characters, I get a "Syntax error: missing operator" message. I need to find a way around this...

The only way I could think of was sending the string through a sub I created that gets rid of this character, but that just meant that the string ended up different and the filter didn't work. For example (and the names of controls and data have been changed):

lstStuff contains an item called "It's red". When I double click this, it gives me an error.. unless I run it through this extra sub, in which case the filter gets set to "Its red" and no records with that name get shown on the subform.

I hope that describes my problem well enough. How do I get around this problem?

 

[RoyVidar]

Try either:

[tt]dim strFilter as string
strFilter= "fieldname = '" & replace(me!lstStuff,"'","''") & "'"

' or

strFilter= "fieldname = """ & me!lstStuff & """""[/tt]

Roy-Vidar

 

[NetNomad312]

...wow. How'd you do that? That actually works, but... the string was changed, I wonder how the records still were shown after that? Then again, I don't know exactly what the ! operator does... heh.

Anyway, thanks a lot.