Range of ASCII values in criteria (1 Viewer)

murgatroyd

Registered User.
Local time
Tomorrow, 02:14
Joined
Mar 26, 2016
Messages
31
I am trying to create a query that will find records where a "CustomerName" field contains any invalid characters.

Valid characters are (1) space, (2) hyphen, (3) apostrophe, (4) "Basic Latin" (English) letters (Aa-Zz), and (5) "Latin-1 Supplement" (other European) letters (À-ÿ = extended ASCII values 192-255). I know how to include the first four items in the query criteria, but how do I include the last one?

Using
Code:
Like "*[" & Chr(192) & "*]"
works for single ASCII values, but how do I specify a range of values?

I tried using
Code:
Like "*[" & Chr(192) & "-" & Chr(255) & "*]"
but it did not work.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:44
Joined
Oct 29, 2018
Messages
21,358
Hi. Can you just type them in? For example:
Code:
Like "*[ -a-zA-z]*"
(untested)
 

murgatroyd

Registered User.
Local time
Tomorrow, 02:14
Joined
Mar 26, 2016
Messages
31
Thanks for your reply.

Just directly typing in characters works for "Basic Latin" (English) characters but not for "Latin-1 Supplement" (other European) characters.

(a) To search for records where the field contains a single "Basic Latin" (English) letter, say "X", either of these works:
Code:
Like "*X*"
Code:
Like "*[" & Chr(88) & "]*"
(88 is the ASCII value of the letter "X")

(b) To search for records where the field contains any of a range of "Basic Latin" (English) letters, say "X-Z", this works:
Code:
Like "*[X-Z*]"

(c) To search for records where the field contains a single "Latin-1 Supplement" (other European) letter, say "À", either of these works:
Code:
Like "*À*"
Code:
Like "*[" & Chr(192) & "]*"
(192 is the ASCII value of the letter "À")

(d) However, to search for records where the field contains any of a range of "Latin-1 Supplement" (other European) letters, say "À-ÿ" (which is the 64 characters with extended ASCII values of 192-255), this does not work:
Code:
Like "*[À-ÿ]*"

Hence I am looking for a way to achieve (d).
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:44
Joined
Jan 20, 2009
Messages
12,849
Your strategy doesn't work at any level because your tests find any record with at least one valid character and may have any number of invalid characters.

You could put all the invalid characters as records in a table and join using:

Code:
SELECT DISTINCT CustomerName
FROM CustomerNames
INNER JOIN BadCharacters
ON CustomerNames.CustomerName Like "*" & BadCharacters.Character & "*"
 

vba_php

Forum Troll
Local time
Today, 10:44
Joined
Oct 6, 2019
Messages
2,884
murg,

add the actual ASCII decimal numbers in a separate field to the table records where ur latin characters are and then use those to specify a range when ur querying. like this:
Code:
SELECT * FROM table WHERE table.ASCIInumber BETWEEN 192 and 255
you can copy and past the HTML table from this page: https://www.techonthenet.com/ascii/chart.php. It'll paste right into ur Access table, and you can run the query immediately.
 

Attachments

  • ascii_extended_chars.accdb
    1.1 MB · Views: 174

murgatroyd

Registered User.
Local time
Tomorrow, 02:14
Joined
Mar 26, 2016
Messages
31
Thanks for your suggestions.

Why is it that this works ...
Code:
Like "*[X-Z*]"
... but this doesn't?
Code:
Like "*[À-ÿ]*"

If this works for a single character ...
Code:
Like "*[" & Chr(192) & "]*"
why doesn't this work for a range of characters?
Code:
Like "*[" & Chr(192) & "-" & Chr(255) & "*]"
 

vba_php

Forum Troll
Local time
Today, 10:44
Joined
Oct 6, 2019
Messages
2,884
why doesn't this work for a range of characters?
Code:
Like "*[" & Chr(192) & "-" & Chr(255) & "*]"
cuz the proper SQL statement/syntax is the keyword BETWEEN, not the symbol "-"
 

murgatroyd

Registered User.
Local time
Tomorrow, 02:14
Joined
Mar 26, 2016
Messages
31
cuz the proper SQL statement/syntax is the keyword BETWEEN, not the symbol "-"

What is the syntax using the keyword BETWEEN for finding characters with ASCII values in the range 192-255?
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:44
Joined
Jan 23, 2006
Messages
15,364
Murgatroyd,

Here's a function that should be helpful.
It checks each character in the incoming string and returns True if it has only
Valid characters are (1) space, (2) hyphen, (3) apostrophe, (4) "Basic Latin" (English) letters (Aa-Zz), and (5) "Latin-1 Supplement" (other European) letters (À-ÿ = extended ASCII values 192-255). It returns False if any invalid character is found.

Code:
' ----------------------------------------------------------------
' Procedure Name: testnames
' Purpose: To validate that an input string contains only
' space, apostrophe,hyphen,A - Z,a - z ,extended ascii(192 to 255)
' Procedure Kind: Function
' Procedure Access: Public
' Parameter yourname (String):
' Return Type: Boolean  (True if valid, else False)
' Author: Jack
' Date: 24-Nov-19
' ----------------------------------------------------------------
Function testnames(yourname As String) As Boolean
10        On Error GoTo testnames_Error
          Dim i As Integer
          Dim s As Integer
          
20        For i = 1 To Len(yourname)
30            s = Asc(Mid(yourname, i, 1))
40            'Debug.Print s, Chr(s) 'for debugging
50            Select Case s
                  Case 32, 39, 45, 65 To 90, 97 To 122, 192 To 255
                  ' space, apostrophe,hyphen,A - Z,a - z ,extended ascii
60                    testnames = True
70                Case Else
80                    testnames = False
90                    Exit For
100           End Select
110       Next i
         
120       On Error GoTo 0
130       Exit Function

testnames_Error:

140       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testnames, line " & Erl & "."

End Function

You can call it with a query such as this which I used on some sample data (part of older Northwind I think)

Code:
SELECT CustomerA.CompanyName
, CustomerA.City
, CustomerA.Address
FROM CustomerA
WHERE (((testnames([CompanyName]))=False));

Results of query:

Code:
CompanyName	                           City	          Address
FISSA Fabrica Inter. Salchichas S.A.	Madrid	      C/ Moralzarzal, 86
Franchi S.p.A.	                        Torino	      Via Monte Bianco 34
North/South	                            London	      "South House 300 Queensbridge"
Océano Atlántico Ltda.	                Buenos Aires  "Ing. Gustavo Moncada 8585 Piso 20-A"
Split Rail Beer & Ale	                Lander	      P.O. Box 555
You can see that ., numbers, /, & are being rejected.
Good luck.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 11:44
Joined
Jan 23, 2006
Messages
15,364
You are welcome. Good luck with your project.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:44
Joined
Jan 20, 2009
Messages
12,849
Murgatroyd,

Here's a function that should be helpful.
It checks each character in the incoming string and returns True if it has only
Valid characters are (1) space, (2) hyphen, (3) apostrophe, (4) "Basic Latin" (English) letters (Aa-Zz), and (5) "Latin-1 Supplement" (other European) letters (À-ÿ = extended ASCII values 192-255). It returns False if any invalid character is found.

It would be interesting to test the speed of jdraw's function against the following that uses a Regular Expression. It returns True is the string is compliant.

Code:
Public Function RegExIllegalSearch(ByVal SearchTarget As String) As Boolean

Dim re As Object
Dim Match As Variant

    Set re = CreateObject("VBScript.RegExp")

    re.Pattern = "^[ '\-\A-Za-z\xC0-\xFF]*$"
    
    For Each Match In re.Execute(SearchTarget)
        RegExIllegalSearch = Len(Match.Value)
    Next

End Function

I assume it still has to churn through the string but the Regex search itself has the advantage of being compiled.

I've used Late Binding for convenience though Early Binding would be faster. Alternatively initiate the object globally to save recreating it every time.

BTW No more than one match will be returned because the match is greedy by default so the For Each Loop won't really slow it down.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:44
Joined
Jan 23, 2006
Messages
15,364
Galaxiom,

Very good question. I wondered the same and thought about regex before building the function. I don't use regex that often, so for me the function was more familiar.
 

isladogs

MVP / VIP
Local time
Today, 15:44
Joined
Jan 14, 2017
Messages
18,186
It would be interesting to test the speed of jdraw's function against the following that uses a Regular Expression. It returns True is the string is compliant.

I assume it still has to churn through the string but the Regex search itself has the advantage of being compiled.

I've used Late Binding for convenience though Early Binding would be faster. Alternatively initiate the object globally to save recreating it every time.

BTW No more than one match will be returned because the match is greedy by default so the For Each Loop won't really slow it down.

If someone can provide a suitable dataset for testing, I'm happy to compare both methods. I expect it will need to be a reasonably large dataset or need multiple loops for testing purposes.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:44
Joined
Oct 29, 2018
Messages
21,358
I am guessing, just like any other speed comparisons already performed, the difference will probably be minimal. Of course, it would still be nice to have a definitive answer.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:44
Joined
Jan 23, 2006
Messages
15,364
Hi Guys,
I did a quick test using this routine

Code:
' ----------------------------------------------------------------
' Procedure Name: RunTest
' Purpose: Test  QryRegex vs QryNoRegex against ~13,000 world city names.
' Check for name containing only space, hyphen, A-Z,a-z,Asc 192 to 255 chars
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 25-Nov-19
' ----------------------------------------------------------------
Sub RunTest()
          Dim t1 As New clsTimer
10        Debug.Print "Processing table Worldcities with " & DCount("*", "worldcities") & " Records " & vbCrLf _
              & " from https://simplemaps.com/data/world-cities  "
20        Debug.Print "========================================================================="
30        Debug.Print
40        DoEvents
50        Debug.Print "QRYRegex SQL  =================" & vbCrLf & CurrentDb.QueryDefs("qryRegex").SQL
60        Debug.Print
70        Debug.Print "QryRegex started  :" & Now
80        DoCmd.OpenQuery "qryRegex"
90        Debug.Print "QryRegex ended     " & t1.EndTimer & "  ticks"
100       DoEvents
110       Debug.Print
120       Debug.Print "QRYNoRegex SQL  ================" & vbCrLf & CurrentDb.QueryDefs("qryNoRegex").SQL
130       Debug.Print
140       Debug.Print "QryNoRegex started :" & Now
150       DoEvents
160       DoCmd.OpenQuery "qryNoRegex"
170       Debug.Print "QryNoRegex ended   " & t1.EndTimer & " ticks"

End Sub

Processing table Worldcities with 12959 Records
from https://simplemaps.com/data/world-cities
=========================================================================

QRYRegex SQL =================
SELECT City, Country, city_ascii
FROM worldcities
WHERE RegExIllegalSearch([city])=False;


QryRegex started :25-Nov-19 3:48:36 PM
QryRegex ended 0 ticks

QRYNoRegex SQL ================
SELECT City, Country, city_ascii
FROM worldcities
WHERE testnames([city])=False;


QryNoRegex started :25-Nov-19 3:48:36 PM
QryNoRegex ended 97 ticks

Regex is quicker, but we're talking <100 clock ticks on reviewing 13000 records and identifying 1158 non-conforming records. More volume may show greater difference in time between queries.
(my laptop Lenovo I7 2.4 ghz /SSD)
 

isladogs

MVP / VIP
Local time
Today, 15:44
Joined
Jan 14, 2017
Messages
18,186
I am guessing, just like any other speed comparisons already performed, the difference will probably be minimal. Of course, it would still be nice to have a definitive answer.

Whilst many speed comparisons have shown small differences, that certainly isn't always the case. In some cases, significant differences have been observed.

Jack
I believe you've told us that there was a difference of 0.097 sec.
However, as one method apparently gave a time duration of zero(!!!!), I question whether the test was sufficiently rigorous.
That's why I suggested a large dataset or repeated loops (or both). I also recommend taking multiple readings to eliminate external factors
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:44
Joined
Jan 23, 2006
Messages
15,364
Hi Colin,

Yes I agree that more data is really needed.
However, I did look at the timer code and now realize I had measured total elapsed time by using only 1 timer.
The clsTimer is an elapse time timer. So I modified the test routine to use 2 separate timers.
Still using the same data, I now get NoRegex taking slightly less time to complete. But we're talking differences of only 10-20 ticks which indicates "very similar" times to me.

Here's the revised code. Each timer is measuring each query execution separately.

Code:
' ----------------------------------------------------------------
' Procedure Name: RunTest
' Purpose: Test  QryRegex vs QryNoRegex against 12,500 world city names.
' Check for name contining only space, hyphen, A-Z,a-z,Asc 192 to 255 chars
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 25-Nov-19
' ----------------------------------------------------------------
Sub RunTest()
          
10        Debug.Print "Processing table Worldcities with " & DCount("*", "worldcities") & " Records " & vbCrLf _
              & " from https://simplemaps.com/data/world-cities  "
20        Debug.Print "========================================================================="
30        Debug.Print
40        DoEvents
50        'Debug.Print "QRYRegex SQL  =================" & vbCrLf & CurrentDb.QueryDefs("qryRegex").SQL
60        'Debug.Print
          Dim t1 As clsTimer
70        Set t1 = New clsTimer
80        Debug.Print "QryRegex started  :" & Now
90        DoCmd.OpenQuery "qryRegex"
100       Debug.Print "QryRegex ended     " & t1.EndTimer & "  ticks"
110       DoEvents
120       'Debug.Print
130       'Debug.Print "QRYNoRegex SQL  ================" & vbCrLf & CurrentDb.QueryDefs("qryNoRegex").SQL
140       'Debug.Print
          Dim t2 As clsTimer
150       Set t2 = New clsTimer
160       Debug.Print "QryNoRegex started :" & Now
170       DoEvents
180       DoCmd.OpenQuery "qryNoRegex"
190       Debug.Print "QryNoRegex ended   " & t2.EndTimer & " ticks"
200       Debug.Print
End Sub

Here are some of the outputs from repeated runs.


Processing table Worldcities with 12959 Records
from https://simplemaps.com/data/world-cities
=========================================================================

QryRegex started :25-Nov-19 6:25:25 PM
QryRegex ended 49 ticks
QryNoRegex started :25-Nov-19 6:25:26 PM
QryNoRegex ended 34 ticks

Processing table Worldcities with 12959 Records
from https://simplemaps.com/data/world-cities
=========================================================================

QryRegex started :25-Nov-19 6:25:27 PM
QryRegex ended 56 ticks
QryNoRegex started :25-Nov-19 6:25:27 PM
QryNoRegex ended 32 ticks

Processing table Worldcities with 12959 Records
from https://simplemaps.com/data/world-cities
=========================================================================

QryRegex started :25-Nov-19 6:25:27 PM
QryRegex ended 45 ticks
QryNoRegex started :25-Nov-19 6:25:28 PM
QryNoRegex ended 33 ticks

Processing table Worldcities with 12959 Records
from https://simplemaps.com/data/world-cities
=========================================================================

QryRegex started :25-Nov-19 6:25:31 PM
QryRegex ended 47 ticks
QryNoRegex started :25-Nov-19 6:25:31 PM
QryNoRegex ended 30 ticks

Processing table Worldcities with 12959 Records
from https://simplemaps.com/data/world-cities
=========================================================================

QryRegex started :25-Nov-19 6:25:32 PM
QryRegex ended 43 ticks
QryNoRegex started :25-Nov-19 6:25:32 PM
QryNoRegex ended 33 ticks

Processing table Worldcities with 12959 Records
from https://simplemaps.com/data/world-cities
=========================================================================

QryRegex started :25-Nov-19 6:25:34 PM
QryRegex ended 45 ticks
QryNoRegex started :25-Nov-19 6:25:34 PM
QryNoRegex ended 31 ticks

Processing table Worldcities with 12959 Records
from https://simplemaps.com/data/world-cities
=========================================================================

QryRegex started :25-Nov-19 6:26:13 PM
QryRegex ended 43 ticks
QryNoRegex started :25-Nov-19 6:26:13 PM
QryNoRegex ended 36 ticks
 

isladogs

MVP / VIP
Local time
Today, 15:44
Joined
Jan 14, 2017
Messages
18,186
Hi Jack
Please could you upload your app used to run these tests including the clsTimer module and, if not too big, the dataset as well. I'd like to see the timer code and see whether its different to those used in my Timer Comparison Tests article.

Also can you confirm whether each tick is one millisecond
 

Users who are viewing this thread

Top Bottom