VBA FIND special characters, not REPLACE
Solved
Ludivine50
Posted messages
118
Status
Member
-
Ludivine50 Posted messages 118 Status Member -
Ludivine50 Posted messages 118 Status Member -
Hello everyone,
After a long search on the forums, I found 30 discussions about the Replace function, functions to replace ' with '' to make the search work that doesn't work, but not the answer to my very simple question.
I just want a code like this:
Dim RECHERCHE as string
If RECHERCHE contains a space or a character " ' " Then
MsgBox("Do not enter a space or character ' in the search box!")
Else
Execute my search
End If
And my problem is simply that I can't check if what the person has entered contains a ' or a space. Unfortunately, this little inconvenience creates panic!
Hoping that someone can help me,
Have a nice day
After a long search on the forums, I found 30 discussions about the Replace function, functions to replace ' with '' to make the search work that doesn't work, but not the answer to my very simple question.
I just want a code like this:
Dim RECHERCHE as string
If RECHERCHE contains a space or a character " ' " Then
MsgBox("Do not enter a space or character ' in the search box!")
Else
Execute my search
End If
And my problem is simply that I can't check if what the person has entered contains a ' or a space. Unfortunately, this little inconvenience creates panic!
Hoping that someone can help me,
Have a nice day
Configuration: ACCESS 2000 - 2003
4 answers
-
Hello,
try this event macro to place in the concerned "sheet" module of VBE
(in this example A1:C10 represents the area where the action is valid
Private Sub Worksheet_Change(ByVal Target As Range) Dim reg As Object Dim flag As Boolean If Not Intersect(Target, Range("A1:C10")) Is Nothing Then ' instantiate Set reg = CreateObject("vbscript.regexp") reg.Pattern = "'" flag = reg.Test(target) If flag Then MsgBox "one or more apostrophes have been entered in the cell" End If End If End Sub
but I'm not sure if apostrophe is feminine
--
The forum is based on sharing knowledge: I do not respond to technical questions by private message.
Best regards, Michel -
Well yes, it was on Excel, my thing
target represents the active cell
But regular expressions also work with Access
since I don't work with Access often, I might tell you nonsense
maybe by creating a boolean function with your text box as a parameter, and it would return False or True
function machin(tonparam)
Dim reg As Object
Set reg = CreateObject("vbscript.regexp")
reg.Pattern = "'"
machin = reg.Test(tonparam)
end functon
--
The forum is based on knowledge sharing: I do not respond to technical questions by private message.
Best regards, Michel-
Hello Michel, thank you very much for your response.
I just tested it and it works very well!
I just have one problem: when I open my search form and type something into the text box for the first time that we will search for later, it crashes.
To better describe the issue:
- someone searches and types CHURCH --> the search works
- someone does a second search and types THE CHURCH --> MsgBox("Please enter an expression without an apostrophe")
- someone does a third search and types TOWN HALL --> the search works
- closing the search form
- reopening the search form
- typing any character in the text box (without clicking the button that launches my search procedure) --> Error message:
Syntax error (missing operator) in the expression '[num] like '*THE CHURCH*' or [address] like '*THE CHURCH*' ....
This only happens with the first input, and then it works.
I don't understand why it keeps "THE CHURCH" in memory when the last input was "TOWN HALL," and why it continues to block on that.
What do you think?
-
-
Hello,
As I mentioned, I rarely work on Access..
Suggestions without guarantee:
Maybe clean up the area if there's an error
Abandon the "function" system and switch to "after update" procedure
--
The forum is based on knowledge sharing: I therefore do not respond to technical questions via private messages.
Best regards, Michel -
Re
In form creation mode -
- select your control where you test the apostrophe
- property - event
check "after Update"
on the "three dots" button, select "code generator"
here is the code: (replace "s_mel" (the name of my app that checked the syntax of an email) with the name of your control
Private Sub s_mel_AfterUpdate() Dim reg As Object Dim flag As Boolean If [s_mel] > 0 Then Set reg = CreateObject("vbscript.regexp") reg.Pattern = "'" flag = reg.Test([s_mel]) If flag Then MsgBox "one or more apostrophes have been entered in the cell" End If End If Set reg = Nothing End Sub
well all this, it allowed me to get back into Access, hence the delay!
--
The forum is based on knowledge sharing: I do not answer technical questions by private message.
Best regards, Michel-
Oops! I wanted to test tonight, but the tables of my database are linked to SQL SERVER, and right now I don't have access to the database...
So, I'll test on Monday.
I'll do it a bit differently, since I coded the entire search in VB. The form is made with Access, but after that, it's all VB. But I think I'll manage to sort it out.
Thank you, I wish you a good weekend, and see you on Monday! ;-) -
Hello Michel, I just tested it but it didn't change anything: it still displays an error message the first time I open the search form. So, I tried to recreate it completely. It's a bit long but I think I have no other solution, the form seems to be locked. By the way, before it couldn't search if there was a space in the input, now it can!
Thank you for your help Michel.
Have a nice day.
-