Validate characters in textbox before more vba

[Ruegen]

Firstly I am using

Me.Filter = "[SchoolName] Like " & Chr(34) & Me.txtSchoolSearch & "*" & Chr(34)

The problem is if the user puts in a " character in the text box the filter won't work

is there a way to wrap a function around the code or run a function first on the text box which removes the characters we don't want and replaces it with characters less problematic?

this way I could run the check first, then run the rest of the filter unhindered by any ' or "


I'm hoping to make a global function I can call that will check the string for any ' or " and treat the string differently so that it doesn't affect the filter in a bad way...

[June7]

The apostrophe can be dealt with using Replace([SchoolName], "'", "''").

Only thing I can suggest for the quote mark is error handler code and a big red letter label caption on form warning users not use quote marks. I've never found a way to deal with literal quote mark within text when constructing SQL statements. I teach my users not to use ", not even for inch symbol.

Or search on school ID not name.

Does the data actually have quote marks?

Can you use a combobox instead?

[ItsMe]

This validation seems to be working...

EDIT: I was able to get it to break by combining quotes and apostrophe in the same search.

Dim strWhere As String
Dim strText As String
strText = Me.txtUnbound.Value
If InStr(strText, "'") Then
strText = """" & strText & "*"""
Else
strText = "'" & strText & "*'"
End If
strWhere = "[SomeValue] LIKE " & strText
Me.FilterOn = False
Me.Filter = ""
Me.Filter = strWhere
Me.FilterOn = True

[Ruegen]

This validation seems to be working...

EDIT: I was able to get it to break by combining quotes and apostrophe in the same search.

Dim strWhere As String
Dim strText As String
strText = Me.txtUnbound.Value
If InStr(strText, "'") Then
strText = """" & strText & "*"""
Else
strText = "'" & strText & "*'"
End If
strWhere = "[SomeValue] LIKE " & strText
Me.FilterOn = False
Me.Filter = ""
Me.Filter = strWhere
Me.FilterOn = True

so this basically checks the field for any ' (or " if it is added) and saves them to a string then if the value is like strText the filter won't work (false) and if it is true it will filter

what happens if you want to search for ' in the word say, st paul's and then filter your results using it? Does the above work for that too?

I'm not wanting to block out text input - merely avoid getting errors.

[Ruegen]

The apostrophe can be dealt with using Replace("[SchoolName]", "'", "''").

Only thing I can suggest for the quote mark is error handler code and a big red letter label caption on form warning users not use quote marks. I've never found a way to deal with literal quote mark within text when constructing SQL statements. I teach my users not to use ", not even for inch symbol.

Or search on school ID not name.

Does the data actually have quote marks?

Can you use a combobox instead?

Some schools have ' for say st pauls. Sometimes the staff here for "unexplained" reasons use " character. Then I have to deal with it. I have to make it so that it doesn't happen.

[ItsMe]

Post #2 will search for the beginning of text and apply it to the form's filter. You can include a ' or a " but not both at the same time.

You could search for Mark's Addre and retrieve Mark's Address

You will need additional code to work closer to your goals. For instance, this is not checking for typos within user input.

[Ruegen]

Post #2 will search for the beginning of text and apply it to the form's filter. You can include a ' or a " but not both at the same time.

You could search for Mark's Addre and retrieve Mark's Address

You will need additional code to work closer to your goals. For instance, this is not checking for typos within user input.

so in no way I can't create a function that will change out a series of pre-determined characters like ' or "?

I can't make the filter go without an error occurring?

I'm not worried about typo, if they can't read what they have just typed - too bad.

retrieving more than one result like "Mark's Address" is acceptable as not many schools are the same and I can filter by other areas too. I use like "*" to allow for that.

[ItsMe]

If you do not want to include the wildcard (*) then don't, or use a check box on your search form so the user can include "Beginning of", "Entire Text, etc.

As for what can and can't be done, I am sure it is possible to do whatever. It is just going to take additional validation. From what you describe, it sounds as though you may need to analyze each character and enumerate all of the apostrophes and quotations, then parse and concatenate back together.

[Ruegen]

If you do not want to include the wildcard (*) then don't, or use a check box on your search form so the user can include "Beginning of", "Entire Text, etc.

As for what can and can't be done, I am sure it is possible to do whatever. It is just going to take additional validation. From what you describe, it sounds as though you may need to analyze each character and enumerate all of the apostrophes and quotations, then parse and concatenate back together.

yeah pretty much that.

I know with javascript and web forms you need to place character checks so you don't get hacked - I thought this would be the same thing.

[June7]

This issue already discussed in https://www.accessforums.net/program...ing-41011.html

Although the quote mark is an expansion of the topic.

Still wonder why a combobox is not used.

[Ruegen]

This issue already discussed in https://www.accessforums.net/program...ing-41011.html

Although the quote mark is an expansion of the topic.

Still wonder why a combobox is not used.

I'm not a fan of the combo box for a search field - the idea is that it is used to drop down more information - I don't seek to use that for a search filter field.

[ItsMe]

...Although the quote mark is an expansion of the topic....

June, I just tested the following. It worked fine with multiple quotes, apostrophes. It did not break.

Dim strWhere As String
Dim strText As String
strText = Me.txtUnbound.Value
strText = Replace(strText, "'", "''")
strWhere = "[SomeValue] LIKE '" & strText & "*'"

Me.FilterOn = False
Me.Filter = ""
Me.Filter = strWhere
Me.FilterOn = True

[trevor40]

check the incoming data from the user and adjust it as you require or limit it to a list of approved values - a-z, 1-9 etc
redirect user to input the correct format. Or use an Input mask on the table field.

[June7]

I appreciate that the filter code works (except when the textbox is null). I apparently have been operating on a misconception about " mark. Since I knew about issue with apostrophe, perhaps I presumed quote mark would be one as well. Or I had a difficult experience long ago and have since forgotten the details for but the conclusion stuck.

Entry to a combobox does not have to be limited to the items in list. I use a combobox that allows users to enter whatever they want and at the same time the combobox AutoExpand shows a match if there is one. I think it is helpful and my users seem to like. The code is set for wildcard search regardless.

[trevor40]

What about the input mask for the combobox?