Search Box apostrophe error

[ECEK]

Hi there guys.
I have the following code that is called from Afterupdates in several textboxes.

This program is being used in a runtime environment.
My problem is that when somebody searches for a name that has an apostrophe the full version doesn't work and the runtime crashes !

I'm perplexed ?

Private Sub subChangeFilter()
If Trim(Me.textBox1 & Me.textBox2 & Me.textBox3 & Me.textBox4 & Me.textBox5 & "") = "" Then
Me.FilterOn = False
Else
Me.Filter = "firstname LIKE '*" & Me.textBox2 & "*' _
AND tp1 LIKE '*" & textBox5 & "*' AND PC LIKE '*" & textBox4 & "*' _
AND surname LIKE '*" & textBox1 & "*' AND A1 LIKE '*" & textBox3 & "*'"




Me.FilterOn = True
End If
End Sub

Is there anything that I can add to my code to get around this?
Or better still understand why it's happening!

 

[CJ_London]

you need to use the replace function to replace a single quote with two single quotes

....LIKE '*" & replace(Me.textBox2,"'","''") & .....

incidentally, using an initial * with LIKE means the db cannot use indexes so the query will be quite slow. Probably not noticeable on small datasets but will be on large ones.

Just consider what a user inputs - if they are looking for 'Harry', how likely are they to enter (i.e. looking for) 'rry'? You can always train them to enter an initial * as part of their entry

 

[ECEK]

I found this great article:
http://custom-software.biz/AccessWizard/Apr13.htm
Could anybody help me to implement its conclusions?

strIn = Replace(strIn, "'", "''")

 

[CJ_London]

read the thread, you have answers

 

[ECEK]

Thanks CJ
I implemented the new code as follows but the search doesn't work at all now.

Private Sub subChangeFilter()
If Trim(Me.textBox1 & Me.textBox2 & Me.textBox3 & Me.textBox4 & Me.textBox5 & "") = "" Then
Me.FilterOn = False
Else
Me.Filter = "firstname LIKE '*" & Replace(Me.textBox2, "'", "''") & "*' AND tp1 LIKE '*" & Replace(Me.textBox5, "'", "''") & "*' AND PC LIKE '*" & Replace(Me.textBox4, "'", "''") & "*' AND surname LIKE '*" & Replace(Me.textBox1, "'", "''") & "*' AND A1 LIKE '*" & Replace(Me.textBox4, "'", "''") & "*'"
Me.FilterOn = True
End If
End Sub

This is how I'm calling it, if this is useful?

Private Sub textbox1_afterupdate()
Call subChangeFilter
Me.textBox1.SetFocus
End Sub

 

[CJ_London]

define 'does not work'

if you mean it returns no records it will be because it hasn't found one where all conditions are met. And do you need to use replace on all fields? or just those where the user could enter a single quote?

 

[ECEK]

I enter anything into textBox1. Press return and nothing happens ?

I also tried to remove the leading wildcard and got an error.
ie

Me.Filter = "firstname LIKE Replace(Me.textBox2, "'", "''") & "*' AND tp1 LIKE Replace(Me.textBox5, "'", "''") & "*' AND PC LIKE Replace(Me.textBox4, "'", "''") & "*' AND surname LIKE Replace(Me.textBox1, "'", "''") & "*' AND A1 LIKE Replace(Me.textBox4, "'", "''") & "*'"
Me.FilterOn = True

Im a bit lost on what's happening in this. I'm being a bit dumb...trying to logically think it through.

My initial code (above) has 5 textboxes on a form

Entering ANYthing into ANY box will produce a result (if one exists).

I just need to resolve the apostrophe issue. I also tried the " & chr(34) " solution but that doesn't work either.

 

[CJ_London]

your query has 'AND's in the criteria and you said it worked before

To get your query to work as before, you would have to complete all textboxes - and to return any records you would need a match on all fields in the criteria

 

[ECEK]

I can't get my head around this replace business.

My list of surnames contains names such as O'Malley

My search is

Me.Filter = "surname LIKE '*" & Me.textBox1

If I use replace i.e.
Me.Filter = "surname LIKE '*" & Replace(Me.textBox1, "'", "''")

then am I then searching for O''Malley ? (which doesn't exist !!)

I have tried:

Me.Filter = "Replace([surname],"'","''") LIKE '*" & Replace([textBox1], "'", "''") & "*' "

but the VBA corrects the "'"" to " '" after the [surname]

I'm getting in a right muddle.

I did find the article with the code to add to the function but I do not know where to put it?

strIn = Replace(strIn, "'", "''")

am I being a bit DIM !!

 

[Uncle Gizmo]

CJ, I've noticed this happening a lot more lately. You make a perfectly legitimate suggestion and you get completely ignored by the OP.

This thread reads like two different conversations in one thread!



Sent from my SM-G925F using Tapatalk

 

[ECEK]

Thanks Gizmo

I went back to CJ's initial answer. I've found that whilst

Me.Filter = "surname LIKE '*" & Replace(Me.textBox1, "'", "''") & "*' "

works.

I am struggling with multiples. ie

Me.Filter = "surname LIKE '*" & Replace(Me.textBox1, "'", "''") & "*' AND firstname LIKE '*" & Replace(Me.textBox2, "'", "''") & "*'"

The problem is that it doesn't filter AfterUpdate after textBox1 has been completed, but it does filter correctly when I AfterUpdate on textBox2.

Not using the replace (like so)

Me.Filter = "surname LIKE '*" & Replace(Me.textBox1, "'", "''") & "*' AND firstname LIKE '*" & Me.textBox2 & "*'"

Does work

 

[Cronk]

Your issue probably is arising because an error will result if you have a null value in any of the text boxes in Replace(me.textbox.....)

Your If statement

If Trim(Me.textBox1 & Me.textBox2 & Me.textBox3 & Me.textBox4 & Me.textBox5 & "") = "" Then

only requires something to be entered in any one of the text boxes, even if the other values are Null, to try applying the filter

Perhaps

If Trim(Me.textBox1 & "") ="" or trim( Me.textBox2 & "")="" or ....

 

[ECEK]

Thanks Cronk I'll give it a go.
In the meantime how do I get rid of the pre wildcard ?

Me.Filter = "surname LIKE '*" & Replace(Me.textBox1, "'", "''") & "*'

CJ was right, when you search you don't need the wildcard at the beginning but I'm unsure how to correct this code without it falling to bits !!!

 

[Minty]

Simply remove it ;

Me.Filter = "surname LIKE '" & Replace(Me.textBox1, "'", "''") & "*'

 

[ECEK]

Everything works fine but I did have a problem if their happened to be no data in some of the field of the underlying table.

This was addressed earlier by CJ and Cronk.
I was unable to get the:

If Trim(Me.textBox1 & "") ="" or trim( Me.textBox2 & "")="" or ....

to work but my solution was as follows.

The first two fields surname and firstname are compulsory but if there is no data in one of the following:
A1 (UseAddress1)
PC (Postcode)
tp1 (TelephoneH)
then the search will not work.

My solution was to based my form on a query instead of the table and use the following in the query

A1: IIf(Len([UseAddress1])>0,Replace([UseAddress1]," ",""),"")
PC: IIf(Len([UsePostcode])>0,Replace([UsePostcode]," ",""),"")
tp1: IIf(Len([TelephoneH])>0,Replace([TelephoneH]," ",""),"")

I'm sure that this whole solution is a bit vulgar but I wasn't able to work it out with just code.

I'm a bit of a bull in a china shop when it comes to ''s and ,....'s