Module needed for "random 6 string" search (1 Viewer)

Dimvlaan

Registered User.
Local time
Today, 06:18
Joined
Nov 16, 2019
Messages
12
Hello everyone,

I need your help. I am in search for a module (or whatever might work) for my Access 2016.

I need to find a "random" 6 string value within a field from a table. We are processing transactions, and we need to more or less easily find the client-number in this field. Unfortunately not everyone (or almost no one) only enters the client number in the field for bank deposit.
I am searching for (example) X12345 - so, it is one letter in the beginning, followed by 5 numbers. I have searched severall sites, but to no avail, yet, so I hope someone from here might be able to help me.
Already now, I would like to say a big thank you, to whoever has the brainpower, that I am missing.

Have a great weekend, y'all!
Dimvlaan
 

vba_php

Forum Troll
Local time
Today, 00:18
Joined
Oct 6, 2019
Messages
2,884
I need to find a "random" 6 string value within a field from a table.

Unfortunately not everyone (or almost no one) only enters the client number in the field for bank deposit.

I am searching for (example) X12345 - so, it is one letter in the beginning, followed by 5 numbers.
I'm not convinced that regEx() will do what you want, and I also don't think u gave us enuf info to answer u. Is the client number *always* in the form of "X#####"? when it *is* present in the field? what about those records where a client number is not present at all? what do u search for then? and please, define "random 6 string value"? if ur searching for a client number when it *is* present in the field and some other string when it is *not* present in the field, you are *not* performing a random search - you're performing a conditional search.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 16:18
Joined
Jan 20, 2009
Messages
12,849
If it is one alpha character of either upper or lower case, followed by five digits, the regex expression required is:
Code:
[a-zA-Z]\d{5}

However you would be better to use validation to ensure the field only accepts the proper format.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:18
Joined
Jan 23, 2006
Messages
15,364
Here's a sample function from a couple of years back with an example of alpha then
5 numerics
Code:
'---------------------------------------------------------------------------------------
' Procedure : RegExpr
' Author    : mellon
' Date      : 13/08/2015
' Purpose   : Check a string for a pattern  and
' return all occurrences
'
'**needs a reference to Microsoft Scripting Runtime **
'---------------------------------------------------------------------------------------
'
Function RegExpr( _
  StringToCheck As Variant, _
  PatternToUse As String, _
  Optional CaseSensitive As Boolean = True) As String

          Dim Re As New Regexp
          Dim rslt As Variant
          
10       On Error GoTo RegExpr_Error

20        Re.Pattern = PatternToUse
30        Re.Global = True
40        Re.IgnoreCase = CaseSensitive
          Dim M
50        For Each M In Re.Execute(StringToCheck)
60           rslt = rslt & M.Value & ","
70        Next

80            RegExpr = Mid(rslt, 1, Len(rslt) - 1)

90       On Error GoTo 0
100      Exit Function

RegExpr_Error:

110       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RegExpr of Module AWF_Related"
        
End Function

Sample function call and result

Code:
?regexpr("absy6785,d23456kkR97687k","[a-z]\d{5}",true )
d23456,R97687
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:18
Joined
Oct 29, 2018
Messages
21,358
Hi. I could be wrong, but I was just wondering, why are you asking for a module? How about running a query?

SELECT * FROM YourTable WHERE YourFieldName Like "*" & [Enter Client Number] & "*"
 

Dimvlaan

Registered User.
Local time
Today, 06:18
Joined
Nov 16, 2019
Messages
12
I'm not convinced that regEx() will do what you want, and I also don't think u gave us enuf info to answer u. Is the client number *always* in the form of "X#####"? when it *is* present in the field? what about those records where a client number is not present at all? what do u search for then? and please, define "random 6 string value"? if ur searching for a client number when it *is* present in the field and some other string when it is *not* present in the field, you are *not* performing a random search - you're performing a conditional search.

Hi,

yes, the "client" is always this format, and if it is not present, or missing some letters, which I would check, after running any script, I would look for an order-number, which also might be present, but which we are trying to eliminate for the ease of use. But this is a long process, as it seems.

So, yes, it is always one letter (a-z) followed by 5 digits (0-9).
And in this term, I am searching for a kind of a random string. If the search is called conditional or random - never thought about that :)
 

Dimvlaan

Registered User.
Local time
Today, 06:18
Joined
Nov 16, 2019
Messages
12
Hi. I could be wrong, but I was just wondering, why are you asking for a module? How about running a query?

SELECT * FROM YourTable WHERE YourFieldName Like "*" & [Enter Client Number] & "*"

Hi,

reason why I look for a module-solution is, we import the file from our banking-system into access, extract this clientnumber, and then combine it with the data from our clients, residing in our db (which is not connected to the bank). We need to export it again in a different format, so our internal payment-system (not connected to the bank, too) can process the payments towards our clients in our crm.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:18
Joined
Oct 29, 2018
Messages
21,358
Hi,

reason why I look for a module-solution is, we import the file from our banking-system into access, extract this clientnumber, and then combine it with the data from our clients, residing in our db (which is not connected to the bank). We need to export it again in a different format, so our internal payment-system (not connected to the bank, too) can process the payments towards our clients in our crm.
Are you saying running a query in your local db is out of the question? Just curious...


PS. Is the purpose of the module to find "potential" client numbers?
 

Dimvlaan

Registered User.
Local time
Today, 06:18
Joined
Nov 16, 2019
Messages
12
Are you saying running a query in your local db is out of the question? Just curious...


PS. Is the purpose of the module to find "potential" client numbers?

At some point, yes. Since the banksystem only gives me a csv with the data. Running a query in our db could be done, but I need to extract the client number from this one specific field, first, else I can't connect them to the payments made.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:18
Joined
Oct 29, 2018
Messages
21,358
At some point, yes. Since the banksystem only gives me a csv with the data. Running a query in our db could be done, but I need to extract the client number from this one specific field, first, else I can't connect them to the payments made.
Hi. Do you have a table with the valid client numbers? If so, then even more so, you should be able to use a query.
 

Dimvlaan

Registered User.
Local time
Today, 06:18
Joined
Nov 16, 2019
Messages
12
If it is one alpha character of either upper or lower case, followed by five digits, the regex expression required is:
Code:
[a-zA-Z]\d{5}

However you would be better to use validation to ensure the field only accepts the proper format.

Sir, this is a very good answer. Actually the one I was searching - but sometimes one searches for things with absolute wrong expressions, so an answer might never be found.

I used this in my db, and now I have the data separated from the rest, which is what I needed.
Perfect! Thanks!
 

Dimvlaan

Registered User.
Local time
Today, 06:18
Joined
Nov 16, 2019
Messages
12
Here's a sample function from a couple of years back with an example of alpha then
5 numerics
Code:
'---------------------------------------------------------------------------------------
' Procedure : RegExpr
' Author    : mellon
' Date      : 13/08/2015
' Purpose   : Check a string for a pattern  and
' return all occurrences
'
'**needs a reference to Microsoft Scripting Runtime **
'---------------------------------------------------------------------------------------
'
Function RegExpr( _
  StringToCheck As Variant, _
  PatternToUse As String, _
  Optional CaseSensitive As Boolean = True) As String

          Dim Re As New Regexp
          Dim rslt As Variant
          
10       On Error GoTo RegExpr_Error

20        Re.Pattern = PatternToUse
30        Re.Global = True
40        Re.IgnoreCase = CaseSensitive
          Dim M
50        For Each M In Re.Execute(StringToCheck)
60           rslt = rslt & M.Value & ","
70        Next

80            RegExpr = Mid(rslt, 1, Len(rslt) - 1)

90       On Error GoTo 0
100      Exit Function

RegExpr_Error:

110       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RegExpr of Module AWF_Related"
        
End Function

Sample function call and result

Code:
?regexpr("absy6785,d23456kkR97687k","[a-z]\d{5}",true )
d23456,R97687

You also pointed me to the right direction, yet Galaxiom was a bit earlier, so I was on the path already.

Thanks!
 

Dimvlaan

Registered User.
Local time
Today, 06:18
Joined
Nov 16, 2019
Messages
12
Hi. Do you have a table with the valid client numbers? If so, then even more so, you should be able to use a query.

Yes, but since the field, I need to compare the client-number with, in its imported state looks something like: "DimvlaanA23142Order#722638445" I need to separate the client number first - then I can do the query and verify the correctness.
Does that make sense? Anyway, I found the solution, I was searching for, and that is what matters most. Thanks for your - and all the others' participation.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:18
Joined
Oct 29, 2018
Messages
21,358
Yes, but since the field, I need to compare the client-number with, in its imported state looks something like: "DimvlaanA23142Order#722638445" I need to separate the client number first - then I can do the query and verify the correctness.
Does that make sense? Anyway, I found the solution, I was searching for, and that is what matters most. Thanks for your - and all the others' participation.
Hi. Glad to hear you have a solution. But if you already have a table of client numbers, then you can skip "separating" the client numbers from the data and simply join it with your clients table using a non-equi join. In any case, good luck with your project.
 

Dimvlaan

Registered User.
Local time
Today, 06:18
Joined
Nov 16, 2019
Messages
12
Hi. Glad to hear you have a solution. But if you already have a table of client numbers, then you can skip "separating" the client numbers from the data and simply join it with your clients table using a non-equi join. In any case, good luck with your project.

Alright mate, now you got me. What on earth is a "non-equi join"?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:18
Joined
Oct 29, 2018
Messages
21,358
Alright mate, now you got me. What on earth is a "non-equi join"?
Hi. It's very simple really. Instead of using the "=" sign in your query join, you use non-equal signs like: <, >, <=, >=. But in your case, you would use the Like operator.
 

Dimvlaan

Registered User.
Local time
Today, 06:18
Joined
Nov 16, 2019
Messages
12
[clientno] like regexpr([importgliberish];"[a-z]\d{5}";False) - like that?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:18
Joined
Oct 29, 2018
Messages
21,358
[clientno] like regexpr([importgliberish];"[a-z]\d{5}";False) - like that?
No. Like so:

...INNER JOIN ImportTable.DataField Like "*" & Clients.ClientNo & "*"

Remember, no function (regex) is necessary.
 

Users who are viewing this thread

Top Bottom