Solved Format based Query

[KINGOFCHAOS17]

Need some assistance with writing a query and honestly and I'm not even sure how to put this into words. I want the query to find records where the field isn't in a specific format. The field in question is for the location in the warehouse and has a specific alphanumeric format with an optional character at the end for small bins. A correct format would be 0A000A0000 with an optional A at the end. Examples of good formats below. What I'm trying to achieve is if someone entered 5B01D0001 or 5D004G001F or 5AZ001A0001 or 5K004A00451 it would appear on the query.

5A012B0008
5C007B0008F
5A007B0006
5A012C0018
5A007B0005
5B002G0007A
5A003B0015
5F001D0006H
5A010C0019
5V001D0022D

 

[theDBguy]

Hi. Check out regular expressions. That might work for you in this case.

 

[KINGOFCHAOS17]

Hey. I did read some stuff on that, but it made absolutely no sense what so ever. Can you assist further please?

 

[theDBguy]

Hey. I did read some stuff on that, but it made absolutely no sense what so ever. Can you assist further please?

Hi. You could check out the sample code I posted here.

Extract Email Address

This topic is really about Regular Expressions. If you do not know, Regular Expressions are text strings used as a search pattern for matchi...

thedbguy.blogspot.com

 

[namliam]

Should be easy to do if you split the finite string into characters
Char1: Mid(Yourfield,1,1)
Char2: Mid(YourField,2,1)

Etc... then for each column simply put a where clause
Char1 NOT between "0" and "9"
or
Char2 NOT Between "A" and "Z"
etc etc...

dont make it harder than it is. While regexp is powerfull, it has a very steep learning curve and you can do a lot of damage if go wrong.

KISS

 

[MajP]

I am not good at regexp patterns, but these seems to work. Someone can validate or come up with a better pattern.

Public Function PatternFound(StringToCheck As Variant) As Boolean
    Dim Pattern As String
    Dim re As Object
  
  'Pattern is: number, letter, 3 numbers, letter, 4 numbers
    'or
    'number, letter, 3 numbers, letter, 4 numbers, letter

    If Not IsNull(StringToCheck) Then
        Pattern = "^[0-9][A-Z][0-9]{3}[A-Z][0-9]{4}[A-Z]$|^[0-9][A-Z][0-9]{3}[A-Z][0-9]{4}$"
        
       
        Set re = CreateObject("VBScript.RegExp")
        re.Pattern = Pattern
        re.Global = False
        re.IgnoreCase = True
       
        PatternFound = re.test(StringToCheck)
    End If
End Function

Query1 Query1

SELECT
tblFound.Field1,
PatternFound([field1]) AS [Found]
FROM tblFound;

Field1	Found
5A012B0008	True
5C007B0008F	True
5A007B0006	True
5A012C0018	True
5A007B0005	True
5B002G0007A	True
5A003B0015	True
5F001D0006H	True
5A010C0019	True
5B01D0001	False
5D004G001F	False
5AZ001A0001	False
5K004A00451	False

 

[namliam]

Nice User defined function, that will surely help performance [/sarcasm]

 

[MajP]

Nice User defined function, that will surely help performance [/sarcasm]

@namliam, with your sarcasm you seem to be suggesting that UDF will hurt performance. Seems kind of unfounded. I think this UDF will blow away your suggestion of repeated string functions, due to the speed of Regexp. To demo I copied that pattern in excel 100k times (well 96k) and it ran instantaneously. Not sure of your "performance" concern.

 

[KINGOFCHAOS17]

@namliam, with your sarcasm you seem to be suggesting that UDF will hurt performance. Seems kind of unfounded. I think this UDF will blow away your suggestion of repeated string functions, due to the speed of Regexp. To demo I copied that pattern in excel 100k times (well 96k) and it ran instantaneously. Not sure of your "performance" concern.

Thank you ever so much, that worked like a charm and found multiple locations that didn't match the correct pattern.

 

[MajP]

As I said I am not very good with RegExp patterns. If you find this does not work in certain cases, I would PM @arnelgp. He seems to be the resident expert on this forum for RegExp.

 

[namliam]

UDF are always, always a hit to performance over anything you can do in SQL .... ***

If it is noticable with the power of computing on a desktop now a days on this particular function is a different discussion.
As a rule of thumb, anything in SQL over UDF anyday.

Try building a Query qryYourtest

Select Isfound, count(isfound)
from qryTest
Group by IsFound

See how it performs

Now make qryNamliam

SELECT distinct tblData.Data
FROM tblData
WHERE
Mid([Data],1,1) Not Between "0" And "9"
or Mid([Data],2,1) Not Between "A" And "Z"
or Mid([Data],3,1) Not Between "0" And "9"
or Mid([Data],4,1) Not Between "0" And "9"
or Mid([Data],5,1) Not Between "0" And "9"
or Mid([Data],6,1) Not Between "A" And "Z"
or Mid([Data],7,1) Not Between "0" And "9"
or Mid([Data],8,1) Not Between "0" And "9"
or Mid([Data],9,1) Not Between "0" And "9"
or Mid([Data],10,1) Not Between "0" And "9"
or ( Mid([Data],11,1) Not Between "A" And "Z" and Mid([Data],11,1) <> "")

then qryNamliam2

SELECT tblData.Data, IIf(IsNull([qryNamliam].[Data]),1,0) AS IsValid
FROM tblData LEFT JOIN qryNamliam ON tblData.Data = qryNamliam.Data;

Now make the same group by query as above on your query qryNamliam3

SELECT qryNamliam2.IsValid, Count(qryNamliam2.IsValid) AS AantalVanIsValid
FROM qryNamliam2
GROUP BY qryNamliam2.IsValid;

See how that performs

On my machine anyways....

Sub x()
    Dim rs As DAO.Recordset
    Debug.Print Now()
    Set rs = CurrentDb.OpenRecordset("qryYourtest")
    Debug.Print Now()
 
    Set rs = CurrentDb.OpenRecordset("qryNamliam3")
    Debug.Print Now()
    Set rs = Nothing
End Sub

Output:
16-09-2020 16:11:28
16-09-2020 16:19:01
16-09-2020 16:19:09

The difference from 7 minutes to 8 seconds is rather noticable, even if your initial query seems to perform within seconds any knock on effects are dramatic

Edit to add: Not 100% sure the initial query for finding "illegal" codes works per specification but for test purposes it is close enough.

*** always always is a rather explicit statement, but 99,99% of the time ... there always are exeptions to anything and everything.