If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
detecting unwanted characters
I need a query criterion that returns records in which a text field (255 max
characters) contains any character(s) other than alpha (upper and lower case), digit, period, dash, or underscore. The text field lengths are not constant. I'm striking out tying to do this with the functions that I see available, but I'm not at all convinced that it can't be done. Any suggestions? Thanks! |
#2
|
|||
|
|||
detecting unwanted characters
UNTESTED --
Create a table with one field, TXT, containing all of the characters that are acceptable. In a select query with both tables not joined add a calculated field like this -- Test_it: InStr([YourField], [TXT]) Set criteria 0 on this calculated field. -- KARL DEWEY Build a little - Test a little "AccessMan" wrote: I need a query criterion that returns records in which a text field (255 max characters) contains any character(s) other than alpha (upper and lower case), digit, period, dash, or underscore. The text field lengths are not constant. I'm striking out tying to do this with the functions that I see available, but I'm not at all convinced that it can't be done. Any suggestions? Thanks! |
#3
|
|||
|
|||
detecting unwanted characters
You might try the following
SELECT TextField FROM SomeTable WHERE TextField Like "*[!._0-9,a-z-]*" Basically that should find any text that has at least one of the characters is NOT a period, underscore,letter, number of dash. '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === KARL DEWEY wrote: UNTESTED -- Create a table with one field, TXT, containing all of the characters that are acceptable. In a select query with both tables not joined add a calculated field like this -- Test_it: InStr([YourField], [TXT]) Set criteria 0 on this calculated field. |
#4
|
|||
|
|||
detecting unwanted characters
Karl:
This suggestion is successful in finding the presence of acceptable characters anywhere in the field value, but I'm looking for the presence of UNacceptable characters, preferably without having to enumerate them. "KARL DEWEY" wrote: UNTESTED -- Create a table with one field, TXT, containing all of the characters that are acceptable. In a select query with both tables not joined add a calculated field like this -- Test_it: InStr([YourField], [TXT]) Set criteria 0 on this calculated field. -- KARL DEWEY Build a little - Test a little "AccessMan" wrote: I need a query criterion that returns records in which a text field (255 max characters) contains any character(s) other than alpha (upper and lower case), digit, period, dash, or underscore. The text field lengths are not constant. I'm striking out tying to do this with the functions that I see available, but I'm not at all convinced that it can't be done. Any suggestions? Thanks! |
#5
|
|||
|
|||
detecting unwanted characters
Did you try the query I suggested?
If so, did it give you the wrong results? Or did it error? '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === AccessMan wrote: Karl: This suggestion is successful in finding the presence of acceptable characters anywhere in the field value, but I'm looking for the presence of UNacceptable characters, preferably without having to enumerate them. "KARL DEWEY" wrote: UNTESTED -- Create a table with one field, TXT, containing all of the characters that are acceptable. In a select query with both tables not joined add a calculated field like this -- Test_it: InStr([YourField], [TXT]) Set criteria 0 on this calculated field. -- KARL DEWEY Build a little - Test a little "AccessMan" wrote: I need a query criterion that returns records in which a text field (255 max characters) contains any character(s) other than alpha (upper and lower case), digit, period, dash, or underscore. The text field lengths are not constant. I'm striking out tying to do this with the functions that I see available, but I'm not at all convinced that it can't be done. Any suggestions? Thanks! |
#6
|
|||
|
|||
detecting unwanted characters
AccessMan wrote:
I need a query criterion that returns records in which a text field (255 max characters) contains any character(s) other than alpha (upper and lower case), digit, period, dash, or underscore. The text field lengths are not constant. I'm striking out tying to do this with the functions that I see available, but I'm not at all convinced that it can't be done. Any suggestions? Thanks! I actually had a need for a similar thing awhile ago, but in mine, I wanted to actually remove the unwanted characters rather than just detect their presence. I wrote a function that takes the string you want to check for unwanted characters as well as a string containing the characters that you DO want, and it loops through the first string removing each character that did NOT appear in the second string. Please note that the following functions have not been tested, and I have a habit of mixing up the order of the string parameters in the InStr function, so I'm not sure if I got them right. This function should return a string containing only the characters you WANT to allow: Public Function StripUnwantedCharacters(stringToCheck as string, allowedChars as string) as string Dim tmp as string dim i as integer for i = 1 to len(stringToCheck) if instr(mid(stringToCheck, i, 1), allowedChars, 1) 0 then tmp = tmp & mid(stringToCheck, i, 1) end if next i StripUnwantedCharacters = tmp end function You could modify it to simply return a flag if a field contains unwanted characters, maybe something like this: Public Function FlagUnwantedCharacters(stringToCheck as string, allowedChars as string) as boolean Dim returnValue as boolean dim i as integer returnValue = false for i = 1 to len(stringToCheck) if instr(mid(stringToCheck, i, 1), allowedChars, 1) 0 then returnValue = true i = len(stringToCheck) + 1 end if next i FlagUnwantedCharacters = returnValue end function Then in your query, you would say something like: SELECT * FROM [TableName] WHERE FlagUnwantedCharacters([FieldName], "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ0123456789.-_") = True; I realize that the second parameter of the function is a bit long, but doing it this way will allow you to use the function for different sets of allowed characters. |
#7
|
|||
|
|||
detecting unwanted characters
John:
I tried the other suggestion first. When I tried your suggestion it did work - thanks! I'm a little mystified about the syntax of WHERE TextField Like "*[!._0-9,a-z-]*" I see that the ! serves as NOT. and it seems to apply to everything that follows. Is the comma treated as a separator or as member of the set? Separators don't seem to be needed after the period, underscore, and the a-z sequence. Is this syntax described somewhere? Thanks! "John Spencer" wrote: Did you try the query I suggested? If so, did it give you the wrong results? Or did it error? '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === AccessMan wrote: Karl: This suggestion is successful in finding the presence of acceptable characters anywhere in the field value, but I'm looking for the presence of UNacceptable characters, preferably without having to enumerate them. "KARL DEWEY" wrote: UNTESTED -- Create a table with one field, TXT, containing all of the characters that are acceptable. In a select query with both tables not joined add a calculated field like this -- Test_it: InStr([YourField], [TXT]) Set criteria 0 on this calculated field. -- KARL DEWEY Build a little - Test a little "AccessMan" wrote: I need a query criterion that returns records in which a text field (255 max characters) contains any character(s) other than alpha (upper and lower case), digit, period, dash, or underscore. The text field lengths are not constant. I'm striking out tying to do this with the functions that I see available, but I'm not at all convinced that it can't be done. Any suggestions? Thanks! |
#8
|
|||
|
|||
detecting unwanted characters
Actually the comma is not needed as a separator.
The syntax is described in the Access help-Enter Like into the search box. You can also look up the help for LIKE in the VBA help. Access Jet SQL works almost exactly the same way. I should have stuck the comma earlier in the sequence (if comma is a valid character or I should have left the comma out completely. '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === AccessMan wrote: John: I tried the other suggestion first. When I tried your suggestion it did work - thanks! I'm a little mystified about the syntax of WHERE TextField Like "*[!._0-9,a-z-]*" I see that the ! serves as NOT. and it seems to apply to everything that follows. Is the comma treated as a separator or as member of the set? Separators don't seem to be needed after the period, underscore, and the a-z sequence. Is this syntax described somewhere? Thanks! "John Spencer" wrote: Did you try the query I suggested? If so, did it give you the wrong results? Or did it error? '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === AccessMan wrote: Karl: This suggestion is successful in finding the presence of acceptable characters anywhere in the field value, but I'm looking for the presence of UNacceptable characters, preferably without having to enumerate them. "KARL DEWEY" wrote: UNTESTED -- Create a table with one field, TXT, containing all of the characters that are acceptable. In a select query with both tables not joined add a calculated field like this -- Test_it: InStr([YourField], [TXT]) Set criteria 0 on this calculated field. -- KARL DEWEY Build a little - Test a little "AccessMan" wrote: I need a query criterion that returns records in which a text field (255 max characters) contains any character(s) other than alpha (upper and lower case), digit, period, dash, or underscore. The text field lengths are not constant. I'm striking out tying to do this with the functions that I see available, but I'm not at all convinced that it can't be done. Any suggestions? Thanks! |
#9
|
|||
|
|||
detecting unwanted characters
John - thanks again, I am using this to great advantage now.
One strange thing though: using like "*[!._0-9a-z-]*" does not flag the greek symbol phi Ø as disallowed. "John Spencer" wrote: Actually the comma is not needed as a separator. The syntax is described in the Access help-Enter Like into the search box. You can also look up the help for LIKE in the VBA help. Access Jet SQL works almost exactly the same way. I should have stuck the comma earlier in the sequence (if comma is a valid character or I should have left the comma out completely. '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === AccessMan wrote: John: I tried the other suggestion first. When I tried your suggestion it did work - thanks! I'm a little mystified about the syntax of WHERE TextField Like "*[!._0-9,a-z-]*" I see that the ! serves as NOT. and it seems to apply to everything that follows. Is the comma treated as a separator or as member of the set? Separators don't seem to be needed after the period, underscore, and the a-z sequence. Is this syntax described somewhere? Thanks! "John Spencer" wrote: Did you try the query I suggested? If so, did it give you the wrong results? Or did it error? '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === AccessMan wrote: Karl: This suggestion is successful in finding the presence of acceptable characters anywhere in the field value, but I'm looking for the presence of UNacceptable characters, preferably without having to enumerate them. "KARL DEWEY" wrote: UNTESTED -- Create a table with one field, TXT, containing all of the characters that are acceptable. In a select query with both tables not joined add a calculated field like this -- Test_it: InStr([YourField], [TXT]) Set criteria 0 on this calculated field. -- KARL DEWEY Build a little - Test a little "AccessMan" wrote: I need a query criterion that returns records in which a text field (255 max characters) contains any character(s) other than alpha (upper and lower case), digit, period, dash, or underscore. The text field lengths are not constant. I'm striking out tying to do this with the functions that I see available, but I'm not at all convinced that it can't be done. Any suggestions? Thanks! |
Thread Tools | |
Display Modes | |
|
|