Can one use field validation to exclude control characters?

david001

New member
Local time
Today, 18:59
Joined
Jun 5, 2017
Messages
7
Can one use field validation to exclude control characters (ie Chr(0-31) and chr(127)) from text fields?

Or by another built in mechanism (ie, not by using VBA)?

Thanks!! New member, David
 
Only if the text is length one, i.e. a single-character field. Otherwise, you might need supplemental filtration of some other kind, which could include VBA.

Before answering that question, you need to answer one more thing. What would you do with any "offending" characters? Remove them? Replace them with something else? If so, what else?
 
Thanks Doc for your reply.

I would exclude them. Sometimes I want to be absolutely sure that there are no control characters in a text or memo field.

Spurred on by your reply I'm experimenting but unable to finish the experiment as I'm going to and fro between hospital appointments for my wife. I noticed that the validation field can be longer than 2000 characters so something like this may work for me:

Not ((Like "*" & chr$(0) & "*") or (Like "*" & chr$(1) & "*") or ... or (Like "*" & chr$(31) & "*")

I will update this thread as soon as possible! Thanks again for your reply!
 
Setting the ValidationRule to the following seems to work:

Not ((Like "*" & chr$(1) & "*") OR (Like "*" & chr$(2) & "*") OR ... OR (Like "*" & chr$(31) & "*") OR (Like "*" & chr$(127) & "*"))

Notes:
a) Fortunately the string in its entirety is less than 2048 characters so fits!
b) If chr$(0) is added, it does not work as I would expect. For the time being I'm going to assume that chr$(0) means something very special to Jet. This may require further examination!
c) I have generated this ValidationRule using vba (which is ironic) as at the end of this post.

Thanks Doc for inspiring me to finally, after many years, work this out!

David

VBA code:

Public Function CreateValidation()

Dim str As String
Dim x As Long, y As Long, i As Long, spacer As String
x = 1
y = 31
spacer = " "

For i = x To y
If i = x Then str = "Not ("
str = str & "(Like ""*"" & chr$(" & i & "" & ") & ""*"")"
If i <> y Then str = str & spacer & "OR "
Next

str = str & " OR (Like ""*"" & chr$(127) & ""*"")"


str = str & ")"

Dim db As dao.Database
CurrentDb.TableDefs("Table1").Fields("str").ValidationRule = str

End Function
 
This input condition statement would block the ENTIRE STRING if any control characters were present. You would not filter the characters - you would filter the string, because the validation criteria are all-or-nothing-at-all in nature.

Here is how you might get some headway. Find or create a general module. Put this function in it. As to how to use it? That depends...

What is the original source of the strings in question and what is your method of getting them into Access? If there is an intermediate string involved, you can call this function to filter the string before you actually perform the table insertion. You could ALSO call this via an SQL UPDATE query after the fact to remove the control characters. The part that is in color is OPTIONAL if you want to replace control characters with spaces. If removal is all you really want, omit the two colored lines.

Code:
Public Function NoControl( stIN as String) as String

Dim stWork as String
Dim stChr as String
Dim lPos as Long

    lf Len(stIN) = 0 Then
        NoControl = ""
    Else
        stWork = ""
        For lPos = 1 to Len(stIn)
          stChr = Mid( stIn, lPos, 1 )
          If Asc( stChr ) Between 32 And 126 Then     'NOTE: Between...And is INCLUSIVE of the limits.
              stWork = stWork & stChr
[COLOR="Blue"]          Else
              stWork = stWork & " "
[/COLOR]          End if
        Next lPos
        NoControl = stWork
    End If

End Function

NOTE: I actually do not recommend removal if there is a chance of having legit carriage control stuff as well as extraneous control characters. However, the above function takes in a string and returns a character-by-character copy of the string but skipping all control characters (and the DEL character, 127). It does not replace them with spaces if you remove the two colored lines.

P.S. - good luck with your wife. I'm helping mine get set up for a rotator cuff surgery in a couple of weeks. Understand the run-around when they need medical attention.
 
Last edited:
I made a follow up post (before yours?) with more details, but I got a message saying the moderator had to check it before the post was finalised. I'll wait to see it gets in before replying. Again, thanks. My wife has looked after me for so many years that I'm pleased (in a way) that I can repay my debt.
 
I made a follow up post (before yours?) with more details, but I got a message saying the moderator had to check it before the post was finalised. I'll wait to see it gets in before replying. Again, thanks. My wife has looked after me for so many years that I'm pleased (in a way) that I can repay my debt.

Approved it.
 
Here I'm trying to send the post that went missing ...

Setting the ValidationRule to the following seems to work:

Not ((Like "*" & chr$(1) & "*") OR (Like "*" & chr$(2) & "*") OR ... OR (Like "*" & chr$(31) & "*") OR (Like "*" & chr$(127) & "*"))

Notes:
a) Fortunately the string in its entirety is less than 2048 characters so fits!
b) If chr$(0) is added, it does not work as I would expect. For the time being I'm going to assume that chr$(0) means something very special to Jet. This may require further examination!
c) I have generated this ValidationRule using vba (which is ironic) as at the end of this post.

Thanks Doc for inspiring me to finally, after many years, work this out!

David

VBA code:

Public Function CreateValidation()

Dim str As String
Dim x As Long, y As Long, i As Long, spacer As String
x = 1
y = 31
spacer = " "

For i = x To y
If i = x Then str = "Not ("
str = str & "(Like ""*"" & chr$(" & i & "" & ") & ""*"")"
If i <> y Then str = str & spacer & "OR "
Next

str = str & " OR (Like ""*"" & chr$(127) & ""*"")"


str = str & ")"

Dim db As dao.Database
CurrentDb.TableDefs("Table1").Fields("str").ValidationRule = str

End Function
 
Hi Doc

This does invalidate the whole string which is what I wanted to achieve. The source of the strings I want to invalidate are various, but primarily to do with bespoke import functions. Carriage returns (etc) can clearly be allowed if necessary by omitting the chr$(10) and (13).

Thanks for your inspiration and help and best wishes for your wife's surgery.

David
 
OK. Thanks, it is relatively minor surgery despite being a "full anesthesia" case.

Your answer took longer because your description was misleading. Originally it seemed as though you only wanted to eliminate the control characters from the input string, not to eliminate an input string (as a whole) that contained control characters. Your approach is correct if you want to blow off the whole string for having ANY control characters. My answers were tailored based on the missed communication, but no biggie. Glad you are making progress.
 

Users who are viewing this thread

Back
Top Bottom