Getting error when using '

[Ruegen]

I have unbound text boxes within a search form

I use

Me.frmSchoolSearchSub1.Form.Filter = "[NewSchoolsID] Like '*" & Me.txtIDnumber & "*'" & IIf(Not IsNull(Me.cmbState), " and [StateID] =" & Me.cmbState, Null) & " and [SchoolName] Like '*" & Me.txtSchoolS & "*'" & " and [SchoolSuburb] Like '*" & Me.txtSchoolSub & "*'" & "and [SchoolPostCode] Like '*" & Me.txtFilterSchoolPostCode & "*'" & " and [SchoolPhone] Like '*" & Me.txtPhone & "*'" & " and IsNull([Removed])"

however if someone uses ' character say like st paul's then I get an error because I use Like '*" in my code - the ' interferes



How do I get around this?

[June7]

Need to double up the ' mark.

I use:

Replace([fieldname], "'", "''")

You will probably have to do that on both parts.

Replace([SchoolName], "'", "''") Like '*" & Replace(Me.txtSchoolS, "'", "''") &

[Ruegen]

Need to double up the ' mark.

I use:

Replace([fieldname], "'", "''")

You will probably have to do that on both parts.

Replace([SchoolName], "'", "''") Like '*" & Replace(Me.txtSchoolS, "'", "''") &

wait - if I am filtering I need to replace? I don't wish to replace anything.

The form simply searches the database

Then occasionally when searching through the continuous form they may use an apostrophe - because of this I get an error.... in the database some names have an apostrophe as they are schools i.e. st paul's catholic.

[Ruegen]

...The error I want to avoid

[ItsMe]

I see a wildcard in your future. Maybe make a rule to not allow special characters and provide instruction for the user.. how to search without apostrophe. Not imagining a way to analyze the field for apostrophe right now.

[Ruegen]

I see a wildcard in your future. Maybe make a rule to not allow special characters and provide instruction for the user.. how to search without apostrophe. Not imagining a way to analyze the field for apostrophe right now.

Yeah I was hoping I wouldn't have to but it seems that way... I really need to think this one over as the company really needs the apostrophe and I like my search form

I want my cake, and stuff my face with it too

[ItsMe]

Maybe try the instr() function and shove the control directly in the middle of it, see if it breaks

[June7]

I tried to enforce a rule also but apostrophes just won't go away. Between contractions, possessives, foot symbol, I have to deal with in data. It doesn't seem to be a problem in Access query objects but building an SQL in VBA is different matter.

It doesn't change the data in table, just manipulates the value in the VBA sql so that the apostrophe will be accepted as a solo literal character.

[ItsMe]

Ruegen, I thought you already tried June's sample. Your answer is right there in post #2.

[Ruegen]

ohhhh I was worried it would replace the data and remove the apostrophe - I will give it a go thanks

[June7]

No, your sql is just setting filter criteria, not editing data.

[John_G]

Hi -

One other solution is to use double-quotes as your string delimiers, instead of single-quotes:

Me.frmSchoolSearchSub1.Form.Filter = "[NewSchoolsID] Like ""*" & Me.txtIDnumber & "*""" & IIf(Not IsNull(Me.cmbState), " and [StateID] =" & Me.cmbState, Null) & " and [SchoolName] Like ""*" & Me.txtSchoolS & "*""" & " and [SchoolSuburb] Like ""*" & Me.txtSchoolSub & "*""" & "and [SchoolPostCode] Like ""*" & Me.txtFilterSchoolPostCode & "*""" & " and [SchoolPhone] Like ""*" & Me.txtPhone & "*""" & " and IsNull([Removed])"

Of course, that means now your users can't enter double-quotes in their search criteria..... you can't have it both ways, unforunately.

HTH

John

[June7]

Ah ha ! Always wondered if there was situation that justified use of double quote over apostrophe. Thank you John.

[Ruegen]

Is there a way to prevent users from adding a double quote? So they can't use it when searching - message box return or something?

I imagine this sort of thing happens in hacking websites - using an apostrophe at the end of a string in a field box.... then writing code after it, concatenating etc..

[June7]

Why prevent it? If the data in table has the character, users should be able to type it as part of the value to be matched.