Results 1 to 7 of 7
  1. #1
    Whit is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    7

    Trying to use the switch function to locate unacceptable characaters

    I am checking the fields in each record in a table for unacceptable characters.
    If any field has anything other than 0 to 9, A to Z, a - z in it I want to display the record's row number and the field's contents.
    I am using the switch statement in a select statement to do this.
    The query is:
    SELECT
    rownum,
    switch(Field1 like "*[!a-z0-9]*" ,Field1), -- If Field 1 has anything other than 0 to 9, A-Z, a-z, the field's contents display else spaces
    switch(Field2 like "*[!a-z0-9]*" ,Field2) -- If Field 2 has anything other than 0 to 9, A-Z, a-z, the field's contents display else spaces
    FROM tblOrgData
    WHERE
    Field1 like "*[!a-z0-9]*" or -- If Field 1 has anything other than 0 to 9, A-Z, a-z select the record
    Field1 like "*[!a-z0-9]*" -- If Field 2 has anything other than 0 to 9, A-Z, a-z select the record

    The query works fine when the unacceptable characters have an ASCII number of 0 to 127. It finds all fields with a character other than 0 to 9, A-Z, a-z in it.
    However, when the only bad characters in a field have an ASCII value of 128 or greater, it does not always select that record. For certain ASCII values the record is not selected.

    I don't understand why certain ASCII characters are ignored and how to correct it. Below are the ASCII values that do not get selected by the above query when they are in field1 or field2, but that should be selected. Any ideas?

    rownum Expr1001
    131 ƒ
    138 Š
    140 Œ
    153
    154 š
    156 œ
    159 Ÿ
    170 ª
    178 ²
    179 ³
    185 ¹
    186 º
    188 ¼
    189 ½
    190 ¾
    192 À
    193 Á
    194 Â
    195 Ã
    196 Ä
    197 Å
    198 Æ
    199 Ç
    200 È
    201 É
    202 Ê
    203 Ë
    204 Ì
    205 Í
    206 Î
    207 Ï
    208 Ð
    209 Ñ
    210 Ò
    211 Ó
    212 Ô
    213 Õ
    214 Ö
    216 Ø
    217 Ù
    218 Ú
    219 Û
    220 Ü
    221 Ý
    222 Þ
    223 ß
    224 à
    225 á
    226 â
    227 ã
    228 ä
    229 å
    230 æ
    231 ç
    232 è
    233 é
    234 ê
    235 ë
    236 ì
    237 í
    238 î
    239 ï
    240 ð
    241 ñ
    242 ò
    243 ó
    244 ô
    245 õ
    246 ö
    248 ø
    249 ù
    250 ú
    251 û
    252 ü
    253 ý
    254 þ
    255 ÿ


  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Maybe all the skipped items were in field2... It isn't in the criteria.

    Code:
    WHERE
    Code:
    Field1 like "*[!a-z0-9]*" or -- If Field 1 has anything other than 0 to 9, A-Z, a-z select the record
    Field1 like "*[!a-z0-9]*" -- If Field 2 has anything other than 0 to 9, A-Z, a-z select the record
    Edit: Sorry for the confusing boxes. Layout insists on 2 code fields and won't abide a fix...

  3. #3
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    In this thread you posed essentially this same problem, to which June7 responded with a solution and you then marked the thread as solved. Now you are posing the problem again. What changed?

  4. #4
    Whit is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    7
    This is my first time using this forum and I was unsure how to present my question.

    June7's response doesn't respond to the fact that I also need to identify the fields with characters that are not 0 to 9, A-Z, a-z that are in the first 127 ASCII characters as well as all of the charaaters after ASCII 127.. I should have initially stated I was using the switch function and that I wanted to be able to use the statement where Field1 like "*[!a-z0-9]*" in the query to select the records with unacceptable characters regardless of their being in the first 127 ASCII characters or the last 128.

    My question should have been:

    "Why does the statement Field1 like "*[!a-z0-9]*" in the where clause work properly for all ASCII characters in the first 127 ASCII characters but for ASCII characters 128 to 255 it selects some but not all, when it in fact should be selecting all of them.

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Modified June7's code as below. If any character in the string is not within the 3 Case ranges, the function will return true.
    This should handle all situations.

    Code:
    Function testS(strT As String) As Boolean
    Dim j As Integer
    For j = 1 To Len(strT)
    Select case  Asc(Mid(strT, j, 1))
        Case 48 to 57       '0 thru 9
        Case 65 to 90     'A thru Z
        Case 97 to 122    'a thru z
        Case else
            testS= true
    End Select
    Next
    End Function
    SELECT table1.* FROM table1 WHERE TestS([Test]) = True;
    Last edited by davegri; 04-18-2018 at 01:18 PM. Reason: syntax

  6. #6
    Whit is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    7
    I can see how the modification to June7's response would work for any character other than 0 to 9, a-z, A-Z. My question though, is still
    "Why does the statement Field1 like "*[!a-z0-9]*" in the where clause work properly for all ASCII characters in the first 127 ASCII characters but for ASCII characters 128 to 255 it selects some but not all, when it in fact should be selecting all of them.

    The reason is.. I have several instances where I need to check for different combinations of acceptable/non-acceptable characters in string fields. For example, I need to validate:

    LastName: I want to use: Where lastname like "*[!a-z]*" or
    Date: date like "*[!0-9/]*" or
    Address: address like "*[!0-9a-z#]*" or
    Dollar Amount: DollarAmount like "*[!0-9$.]*"

    Using"*[!0-9a-z]*"

    is a lot easier than writing and compiling a function like the following for each different set of allowable characters.

    Code:
    Function testS(strT As String) As Boolean
    Dim j As Integer
    For j = 1 To Len(strT)
    Select case Asc(Mid(strT, j, 1))
    Case 48 to 57 '0 thru 9
    Case 65 to 90 'A thru Z
    Case 97 to 122 'a thru z
    Case else
    testS= true
    End Select
    Next
    End Function

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    "Why does the statement Field1 like "*[!a-z0-9]*" in the where clause work properly for all ASCII characters in the first 127 ASCII characters but for ASCII characters 128 to 255 it selects some but not all, when it in fact should be selecting all of them.
    I'm far, far from an expert at regular expressions, but I can throw out a guess. Maybe it's the dual possible use for *. This has an effect on a RE, no? In Access, it's also a wild card. So is it being interpreted as (Access parts in red)

    LIKE any part of table field & specified characters from an expression & any remaining part of table field ,
    or is it
    LIKE specified characters from an expression that happens to have * as one of the 'operators'
    To further illustrate my meaning, I tried concatenating the * so it was utilized only by Access, to the expression result as in

    Code:
    SELECT tblAscii.CodeValue, tblAscii.Character, Switch([tblAscii].[Character] Like "*" & "[!a-z0-9]" & "*",[tblAscii].[Character]) AS Expr1 
    FROM tblAscii WHERE Character Like "*" & "[!a-z0-9]" & "*";
    What I got is 88 characters that are definitely not alphanumeric, but I think it's still not what you want. From what little I've been able to absorb from past studying of RE web pages is that not only are some expression operators limited to one platform or another, what is considered to be an alpha character is governed by the system code page (or some similar obscure thing). A search I did on operators didn't even list the ! you are using as one of them, so who really knows, but I tried it without ! and got 188 records of what I would call alpha. So you may not want Æ in your results (I did get that one when not using !) but while it may be Cyrillic or whatever, I'll bet it's considered alpha. Without ! I got the following list (note, I removed rows where the description of the character was all alpha but the character itself is not, such as NULL)

    CodeValue Character Expr1
    49 0 0
    50 1 1
    51 2 2
    52 3 3
    53 4 4
    54 5 5
    55 6 6
    56 7 7
    57 8 8
    58 9 9
    66 A A
    67 B B
    68 C C
    69 D D
    70 E E
    71 F F
    72 G G
    73 H H
    74 I I
    75 J J
    76 K K
    77 L L
    78 M M
    79 N N
    80 O O
    81 P P
    82 Q Q
    83 R R
    84 S S
    85 T T
    86 U U
    87 V V
    88 W W
    89 X X
    90 Y Y
    91 Z Z
    98 a a
    99 b b
    100 c c
    101 d d
    102 e e
    103 f f
    104 g g
    105 h h
    106 i i
    107 j j
    108 k k
    109 l l
    110 m m
    111 n n
    112 o o
    113 p p
    114 q q
    115 r r
    116 s s
    117 t t
    118 u u
    119 v v
    120 w w
    121 x x
    122 y y
    123 z z
    129 Ç Ç
    130 ü ü
    131 é é
    132 â â
    133 ä ä
    134 à à
    135 å å
    136 ç ç
    137 ê ê
    138 ë ë
    139 è è
    140 ï ï
    141 î î
    142 ì ì
    143 Ä Ä
    144 Å Å
    145 É É
    146 æ æ
    147 Æ Æ
    148 ô ô
    149 ö ö
    150 ò ò
    151 û û
    152 ù ù
    153 ÿ ÿ
    154 Ö Ö
    155 Ü Ü
    156 ø ø
    158 Ø Ø
    160 ƒ ƒ
    161 á á
    162 í í
    163 ó ó
    164 ú ú
    165 ñ ñ
    166 Ñ Ñ
    167 ª ª
    168 º º
    172 ½ ½
    173 ¼ ¼
    182 Á Á
    183 Â Â
    184 À À
    199 ã ã
    200 Ã Ã
    209 ð ð
    210 Ð Ð
    211 Ê Ê
    212 Ë Ë
    213 È È
    214 ı ı
    215 Í Í
    216 Î Î
    217 Ï Ï
    223 Ì Ì
    225 Ó Ó
    226 ß ß
    227 Ô Ô
    228 Ò Ò
    229 õ õ
    230 Õ Õ
    232 þ þ
    233 Þ Þ
    234 Ú Ú
    235 Û Û
    236 Ù Ù
    237 ý ý
    238 Ý Ý
    244 ¾ ¾
    252 ¹ ¹
    253 ³ ³
    254 ² ²

    So I have to think that something like ß truly satisfies the sql (at least on my system) even though you probably don't want it in the results. As for
    is a lot easier than writing and compiling a function like the following for each different set of allowable characters.
    I can't imagine why, especially if the problem is one of system settings or incompatibility between operators (*) used in Access that are also used in other platforms. At least the function works.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. simple Switch function question
    By billybeer in forum Reports
    Replies: 3
    Last Post: 10-31-2017, 11:37 AM
  2. Replies: 12
    Last Post: 05-14-2017, 10:11 AM
  3. Alernative to SWITCH function???
    By elender in forum Queries
    Replies: 5
    Last Post: 02-09-2017, 06:54 PM
  4. Using SQL switch function MS Access
    By sandlucky in forum Queries
    Replies: 18
    Last Post: 03-31-2011, 08:49 AM
  5. Select Query in Switch Function
    By sandlucky in forum Queries
    Replies: 0
    Last Post: 03-30-2011, 04:54 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums