Solved Format based Query (1 Viewer)

KINGOFCHAOS17

Member
Local time
Today, 18:52
Joined
Mar 20, 2020
Messages
31
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

I’m here to help
Staff member
Local time
Today, 11:52
Joined
Oct 29, 2018
Messages
21,358
Hi. Check out regular expressions. That might work for you in this case.
 

KINGOFCHAOS17

Member
Local time
Today, 18:52
Joined
Mar 20, 2020
Messages
31
Hey. I did read some stuff on that, but it made absolutely no sense what so ever. Can you assist further please?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:52
Joined
Oct 29, 2018
Messages
21,358
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.
 

namliam

The Mailman - AWF VIP
Local time
Today, 19:52
Joined
Aug 11, 2003
Messages
11,696
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

You've got your good things, and you've got mine.
Local time
Today, 14:52
Joined
May 21, 2018
Messages
8,463
I am not good at regexp patterns, but these seems to work. Someone can validate or come up with a better pattern.
Code:
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
Code:
SELECT
tblFound.Field1,
PatternFound([field1]) AS [Found]
FROM tblFound;

Field1Found
5A012B0008True
5C007B0008FTrue
5A007B0006True
5A012C0018True
5A007B0005True
5B002G0007ATrue
5A003B0015True
5F001D0006HTrue
5A010C0019True
5B01D0001False
5D004G001FFalse
5AZ001A0001False
5K004A00451False
 
Last edited:

namliam

The Mailman - AWF VIP
Local time
Today, 19:52
Joined
Aug 11, 2003
Messages
11,696
Nice User defined function, that will surely help performance [/sarcasm]
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:52
Joined
May 21, 2018
Messages
8,463
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.
 

Attachments

  • FindPattern.accdb
    4.9 MB · Views: 125

KINGOFCHAOS17

Member
Local time
Today, 18:52
Joined
Mar 20, 2020
Messages
31
@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

You've got your good things, and you've got mine.
Local time
Today, 14:52
Joined
May 21, 2018
Messages
8,463
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

The Mailman - AWF VIP
Local time
Today, 19:52
Joined
Aug 11, 2003
Messages
11,696
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
Code:
Select Isfound, count(isfound)
from qryTest
Group by IsFound
See how it performs

Now make qryNamliam
Code:
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
Code:
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
Code:
SELECT qryNamliam2.IsValid, Count(qryNamliam2.IsValid) AS AantalVanIsValid
FROM qryNamliam2
GROUP BY qryNamliam2.IsValid;

See how that performs

On my machine anyways....
Code:
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.
 
Last edited:

Users who are viewing this thread

Top Bottom