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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-11-2019, 08:46 AM   #16
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: RgExp.Pattern

Quote:
Originally Posted by Gasman View Post
Do we even know if it is not working?

If the expression is found Field1data is set and nothing else.?

Or am I missing something obvious?
Hi. I think the problem is we don't have the actual data, so we can't verify it it's working or not.

__________________
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-11-2019, 08:54 AM   #17
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: RgExp.Pattern

Both my last pattern and theDBguy's come back as true for both the sample strings?

Code:
Sub testRegex()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim RgExp As Variant
Set RgExp = CreateObject("VBScript.RegExp")
strSQL = "99039002885/AAAA0001"
'strSQL = "99039002885/00000001"

RgExp.Pattern = "^\d{11}\/[A-Z]{4}\d{4}|^\d{11}\/\d{8}$"
RgExp.Pattern = "\d{11}\/\d{8}|\d{11}\/[A-Za-z]{4}\d{4}"
'99900295953/00000001
Debug.Print RgExp.Test(strSQL)
End Sub
__________________
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 online now   Reply With Quote
Old 11-11-2019, 09:01 AM   #18
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 2,002
Thanks: 444
Thanked 297 Times in 258 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: RgExp.Pattern

Hi Paul. It worked for me too. Guess my Validater isn't as good as I thought!

__________________
- Ciao, John
NauticalGent is online now   Reply With Quote
Old 11-11-2019, 09:05 AM   #19
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: RgExp.Pattern

Quote:
Originally Posted by Gasman View Post
Both my last pattern and theDBguy's come back as true for both the sample strings?
LOL. That's because they're the same, only backwards. Where you had,


Pattern1 OR Pattern2


I had,


Pattern2 OR Pattern1


Cheers!
__________________
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-11-2019, 09:06 AM   #20
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: RgExp.Pattern

Quote:
Originally Posted by NauticalGent View Post
Hi Paul. It worked for me too. Guess my Validater isn't as good as I thought!
I thought that strange, as the site I worked out the pattern on produced two matches for the strings originally posted.
__________________
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 online now   Reply With Quote
Old 11-11-2019, 09:19 AM   #21
hardy1976
Still learning...
 
Join Date: Apr 2006
Location: London
Posts: 200
Thanks: 23
Thanked 0 Times in 0 Posts
hardy1976 is on a distinguished road
Re: RgExp.Pattern

Got it thank you all for your help
"^\d{11}\/[a-zA-Z0-9]{4}\d{4}|^\d{11}\/\d{8}$"
hardy1976 is offline   Reply With Quote
Old 11-11-2019, 09:21 AM   #22
hardy1976
Still learning...
 
Join Date: Apr 2006
Location: London
Posts: 200
Thanks: 23
Thanked 0 Times in 0 Posts
hardy1976 is on a distinguished road
Re: RgExp.Pattern

Wow - thank you.
Quote:
Originally Posted by NauticalGent View Post
Try this:

https://www.access-programmers.co.uk...ighlight=regex

I didnt write the code, but it is what I use when I am testing my patterns. Since it is written in VBA, if I get a successful test, I KNOW it will work...

hardy1976 is offline   Reply With Quote
Old 11-11-2019, 09:21 AM   #23
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: RgExp.Pattern

Quote:
Originally Posted by hardy1976 View Post
Got it thank you all for your help
"^\d{11}\/[a-zA-Z0-9]{4}\d{4}|^\d{11}\/\d{8}$"
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
__________________
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
The Following User Says Thank You to theDBguy For This Useful Post:
hardy1976 (11-11-2019)
Old 11-11-2019, 09:22 AM   #24
hardy1976
Still learning...
 
Join Date: Apr 2006
Location: London
Posts: 200
Thanks: 23
Thanked 0 Times in 0 Posts
hardy1976 is on a distinguished road
Re: RgExp.Pattern

LOL - yes it was...

Quote:
Originally Posted by Gasman View Post
Well the expressions I posted worked on the test site I linked to?

Plus, what is TestDate doing?

Is your code even doing what you think it should be doing?

Code:
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * from [tablename]"


Set rst = CurrentDb.OpenRecordset(strSQL)

RgExp.Pattern = "^\d{11}\/\d{8}$"
'99900295953/00000001

rst.MoveFirst

Do Until rst.EOF
    If RgExp.Test(Nz(rst!field1, 0)) = True Then
        field1data = Left(rst![field1], 11)
    Else
        If TestDate(Nz(rst![PStart Date], 0)) = "valid" Then
            rst.Edit
            rst!field1 = field1data
            rst.Update
        End If
    End If
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing
hardy1976 is offline   Reply With Quote
Old 11-11-2019, 09:23 AM   #25
hardy1976
Still learning...
 
Join Date: Apr 2006
Location: London
Posts: 200
Thanks: 23
Thanked 0 Times in 0 Posts
hardy1976 is on a distinguished road
Re: RgExp.Pattern

Sorry missed all the comments on here as I was trying to crack it - APPRECIATE ALL YOUR HELP EVERYONE
hardy1976 is offline   Reply With Quote
Old 11-11-2019, 09:24 AM   #26
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: RgExp.Pattern

Quote:
Originally Posted by hardy1976 View Post
Sorry missed all the comments on here as I was trying to crack it - APPRECIATE ALL YOUR HELP EVERYONE
No worries. Good job figuring it out on your own then. Cheers!
__________________
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-11-2019, 09:45 AM   #27
hardy1976
Still learning...
 
Join Date: Apr 2006
Location: London
Posts: 200
Thanks: 23
Thanked 0 Times in 0 Posts
hardy1976 is on a distinguished road
Re: RgExp.Pattern

Quote:
Originally Posted by theDBguy View Post
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
Got there eventually - using a function and query approach helped loads.
hardy1976 is offline   Reply With Quote
Old 11-11-2019, 09:48 AM   #28
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: RgExp.Pattern

Quote:
Originally Posted by hardy1976 View Post
Got there eventually - using a function and query approach helped loads.
Access is not hard if you have the patience. Cheers!
__________________
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-11-2019, 09:50 AM   #29
hardy1976
Still learning...
 
Join Date: Apr 2006
Location: London
Posts: 200
Thanks: 23
Thanked 0 Times in 0 Posts
hardy1976 is on a distinguished road
Re: RgExp.Pattern

Quote:
Originally Posted by theDBguy View Post
Access is not hard if you have the patience. Cheers!
I've been trying to solve that for about 3 months on and off! Query approach helped sooooooo much!
hardy1976 is offline   Reply With Quote
Old 11-11-2019, 09:53 AM   #30
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: RgExp.Pattern

Quote:
Originally Posted by hardy1976 View Post
I've been trying to solve that for about 3 months on and off! Query approach helped sooooooo much!
Wow! Now, that's what I would call patience!

__________________
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
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to do a Pattern Replace in VBA? prabha_friend Modules & VBA 12 02-12-2015 01:18 AM
query to check pattern in name johnmerlino Queries 11 10-22-2010 07:43 AM
macro - number pattern realmfighter Macros 2 05-24-2008 02:47 PM
Regular expression: I don't find right pattern Leen Modules & VBA 2 06-14-2007 06:22 AM
Finding Pattern in Field yessir Queries 4 06-11-2004 06:57 AM




All times are GMT -8. The time now is 08:02 AM.


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