Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-16-2019, 07:50 AM   #16
Dimvlaan
Newly Registered User
 
Join Date: Nov 2019
Posts: 12
Thanks: 2
Thanked 1 Time in 1 Post
Dimvlaan is on a distinguished road
Re: Module needed for "random 6 string" search

Quote:
Originally Posted by theDBguy View Post
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"?

Dimvlaan is offline   Reply With Quote
Old 11-16-2019, 07:53 AM   #17
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,584
Thanks: 58
Thanked 1,444 Times in 1,425 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Module needed for "random 6 string" search

Quote:
Originally Posted by Dimvlaan View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-16-2019, 07:57 AM   #18
Dimvlaan
Newly Registered User
 
Join Date: Nov 2019
Posts: 12
Thanks: 2
Thanked 1 Time in 1 Post
Dimvlaan is on a distinguished road
Re: Module needed for "random 6 string" search

[clientno] like regexpr([importgliberish];"[a-z]\d{5}";False) - like that?

Dimvlaan is offline   Reply With Quote
Old 11-16-2019, 08:01 AM   #19
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,584
Thanks: 58
Thanked 1,444 Times in 1,425 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Module needed for "random 6 string" search

Quote:
Originally Posted by Dimvlaan View Post
[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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-16-2019, 08:07 AM   #20
Dimvlaan
Newly Registered User
 
Join Date: Nov 2019
Posts: 12
Thanks: 2
Thanked 1 Time in 1 Post
Dimvlaan is on a distinguished road
Re: Module needed for "random 6 string" search

I will have a look at that.
Thanks for now!
Dimvlaan is offline   Reply With Quote
Old 11-16-2019, 08:10 AM   #21
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,584
Thanks: 58
Thanked 1,444 Times in 1,425 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Module needed for "random 6 string" search

Quote:
Originally Posted by Dimvlaan View Post
I will have a look at that.
Thanks for now!
Hi. You're welcome. Good luck!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-16-2019, 11:47 AM   #22
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,545
Thanks: 442
Thanked 845 Times in 816 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Module needed for "random 6 string" search

Quote:
Originally Posted by Dimvlaan View Post
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.
Please post your solution for the benefit of others ?

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 11-17-2019, 06:41 AM   #23
Dimvlaan
Newly Registered User
 
Join Date: Nov 2019
Posts: 12
Thanks: 2
Thanked 1 Time in 1 Post
Dimvlaan is on a distinguished road
Re: Module needed for "random 6 string" search

Sure, here is what I did:

Module:

Code:
Option Explicit

Function regexp(StringToCheck As Variant, SearchPattern As String, Optional CaseSensitive As Boolean = True)

  Dim re As New regexp
  re.Pattern = SearchPattern
  re.Global = False
  re.IgnoreCase = Not CaseSensitive
  Dim m
  For Each m In re.Execute(StringToCheck)
    regexp = m.Value
  Next
End Function
In the query, I use this to separate the client number from the rest:

Code:
Client_ID: if(RegExp([originalfield];"[a-z]\d{5}";false)<>" ";RegExp([originalfield];"[a-z]\d{5}";false);"control_it")
Which give me a perfect overview of those who have entered a correct client id and those, who didn't.
Those that didn't, are checked if an ordernumber exists, and if, I combine them to our db. If not, they will be put in a report for the user to check.

Thanks again!
Dimvlaan is offline   Reply With Quote
The Following User Says Thank You to Dimvlaan For This Useful Post:
Gasman (11-17-2019)
Old 11-17-2019, 06:53 AM   #24
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,545
Thanks: 442
Thanked 845 Times in 816 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Module needed for "random 6 string" search

Thanks for posting solution.

Good to see that regex() did in actual fact solve your problem.

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Replacing " | | | ", " | | " With " | " In String, Access 2010 Kayla123 Queries 1 09-15-2014 06:25 PM
Search String using spaces as "Or" themiddlenumber Queries 2 09-30-2006 11:27 AM
Assigning a variable to "LIKE" condition search string pikachew Queries 6 01-27-2005 08:33 PM
Assigning a variable to "LIKE" condition search string pikachew Tables 2 01-26-2005 07:28 AM
Assigning a variable to "LIKE" condition search string pikachew General 2 01-26-2005 07:26 AM




All times are GMT -8. The time now is 02:00 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World