[AccessD] Query expression

Mon Nov 9 15:42:59 CST 2020

VBA patterns don't really do RegEx.

In VBA create a reference to Microsofot VBScript Regular Expressions 5.5

Create a function

Function HasNumbers(s As String) As Long
Dim re As New RegExp
re.Pattern = "[0-9]"
HasNumbers = re.Test(s)
End Function

Filter on that.

On 9 Nov 2020 at 19:47, McGillivray, Don wrote:

>  . . .   and I should have scrolled further to see the rest of the
>  thread before sending.
> 
> James's solution is going in my notebook.
> 
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of
> McGillivray, Don Sent: Monday, November 9, 2020 11:43 AM To: Access
> Developers discussion and problem solving
> <accessd at databaseadvisors.com> Subject: Re: [AccessD] Query expression
> 
> Dunno that it's any simpler, but you can also do a comparison between
> the length of the value and the result of a 10-layer nested Replace()
> that swaps all of the numbers for an empty string.  Any where that
> delta is zero is a candidate.  Greater than zero is left out.
> 
> Select * from MyTable where (len(ColName) -
> len(replace(replace(replace(replace(replace(replace(replace(replace(re
> place(replace(ColName,"0","") ,"1","") ,"2","") ,"3","") ,"4","")
> ,"5","") ,"6","") ,"7","") ,"8","") ,"9",""))) = 0
> 
> I just typed that freehand, so don't shoot me if I missed a
> parenthesis or comma in there.  You get the idea, I'm sure.
> 
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of
> Susan Harkins Sent: Monday, November 9, 2020 10:22 AM To: 'Access
> Developers discussion and problem solving'
> <accessd at databaseadvisors.com> Subject: Re: [AccessD] Query expression
> 
> The expression I came up with works fine -- I don't need code. AT this
> point, I would only recommend  a change if it were a simpler 
> 
> I guess I didn't explain my question very well. I was wondering why my
> attempts at using ![0-9] in a query expression and then NOT IN in the
> SQL window didn't work? 
> 
> Susan H. 
> 
> 
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of
> rocky smolin Sent: Monday, November 9, 2020 1:07 PM To: 'Access
> Developers discussion and problem solving'
> <accessd at databaseadvisors.com> Subject: Re: [AccessD] Query expression
> 
> At this point I would use a bit of code to create a temp table using
> Paul's snip to include only the data without numbers. You could end
> the code with a call to a query to display the temp table records
> linked to the same field in the source table.  
> 
> This assumes that the values in the field being tested are unique.  
> 
> If not, you'd have to use the PK for the link. 
> 
> If no PK you'd have to create a temp table with all the same fields as
> the source table and copy all the fields there. 
> 
> HTH
> 
> r
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of Susan Harkins Sent: Monday, November 09, 2020 10:00 AM To: 'Access
> Developers discussion and problem solving' Subject: Re: [AccessD]
> Query expression
> 
> No -- Val() returns the numbers only if they're at the beginning -- so
> it doesn't catch something like L12-345-56. Both L12-345-56 and John
> Doe return 0. 
> 
> Susan H. 
> 
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of
> rocky smolin Sent: Monday, November 9, 2020 12:29 PM To: 'Access
> Developers discussion and problem solving'
> <accessd at databaseadvisors.com> Subject: Re: [AccessD] Query expression
> 
> Oh, yes, huh. :)  Maybe the Val function?
> 
> https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.
> techonthenet.com%2Faccess%2Ffunctions%2Fnumeric%2Fval.php&data=04%
> 7C01%7Cdmcgillivray%40ctc.ca.gov%7Ca86d81f0ecce4a2f3d4c08d884e7cff3%7C
> 78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405479191003631%7CUnknow
> n%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLC
> JXVCI6Mn0%3D%7C3000&sdata=fzjJVM5AfzubvwB2rxSgX0Gvxd63OouMhjjEUnLc
> AMg%3D&reserved=0
> 
> Not sure but the notes and examples indicate that this might work
> 
> r
> 
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of Susan Harkins Sent: Monday, November 09, 2020 9:12 AM To: 'Access
> Developers discussion and problem solving' Subject: Re: [AccessD]
> Query expression
> 
> It's a text field. 
> 
> Susan H. 
> 
> 
> How about Not Isnumeric(field)?
> 
> ---------
> 
> 
> Someone wanted help sorting a field that contained random values:
> 
> L12-345-56
> John Doe
> L12-345-56
>  IN 123-456-78
> 234
> Betty Jones
> Mary Michaels
> 
> Not my design, so no fussing!  
> 
> Anyway, she needs a sort that removes any value that contains a
> numeric digit. The simple things that I thought should work, didn't. I
> ended up with the following: 
> 
> Not Like '*1*' And Not Like '*2*' And Not Like '*3*' And Not Like
> '*4*' And Not Like '*5*' And Not Like '*6*' And Not Like '*7*' And Not
> Like '*8*' And Not Like '*9*' And Not Like '*0*'
> 
> I tried various versions of wildcards, such as ![0-9], in Design view
> and even NOT IN in the SQL window. I continually got syntax errors or
> they didn't work.
> 
> I'm baffled -- I'm sure it's something simple. Any thoughts? I'm
> really rusty too -- haven't used Access in a long time. 
> 
> Susan H 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatab
> aseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcg
> illivray%40ctc.ca.gov%7Ca86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93caf
> d497081b54e5074e42910%7C0%7C0%7C637405479191003631%7CUnknown%7CTWFpbGZ
> sb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3
> D%7C3000&sdata=pA9isrN2bGKp1I7rHJt3hmBv2zb%2FFuKlK9qapmw10Lw%3D&am
> p;reserved=0 Website:
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.d
> atabaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C
> a86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93cafd497081b54e5074e42910%7C
> 0%7C0%7C637405479191003631%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDA
> iLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=ChB6O
> q7pa7bfW8DX8B9zPolPHcfnPmsB1M32QIbgcZU%3D&reserved=0
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatab
> aseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcg
> illivray%40ctc.ca.gov%7Ca86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93caf
> d497081b54e5074e42910%7C0%7C0%7C637405479191003631%7CUnknown%7CTWFpbGZ
> sb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3
> D%7C3000&sdata=pA9isrN2bGKp1I7rHJt3hmBv2zb%2FFuKlK9qapmw10Lw%3D&am
> p;reserved=0 Website:
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.d
> atabaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C
> a86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93cafd497081b54e5074e42910%7C
> 0%7C0%7C637405479191003631%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDA
> iLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=ChB6O
> q7pa7bfW8DX8B9zPolPHcfnPmsB1M32QIbgcZU%3D&reserved=0
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatab
> aseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcg
> illivray%40ctc.ca.gov%7Ca86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93caf
> d497081b54e5074e42910%7C0%7C0%7C637405479191003631%7CUnknown%7CTWFpbGZ
> sb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3
> D%7C3000&sdata=pA9isrN2bGKp1I7rHJt3hmBv2zb%2FFuKlK9qapmw10Lw%3D&am
> p;reserved=0 Website:
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.d
> atabaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C
> a86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93cafd497081b54e5074e42910%7C
> 0%7C0%7C637405479191003631%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDA
> iLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=ChB6O
> q7pa7bfW8DX8B9zPolPHcfnPmsB1M32QIbgcZU%3D&reserved=0
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatab
> aseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcg
> illivray%40ctc.ca.gov%7Ca86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93caf
> d497081b54e5074e42910%7C0%7C0%7C637405479191013587%7CUnknown%7CTWFpbGZ
> sb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3
> D%7C3000&sdata=iY3AeW3RgXuOjlV8uQMTqUIJuhwWYwxYGDCgbJwjSF8%3D&
> reserved=0 Website:
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.d
> atabaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C
> a86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93cafd497081b54e5074e42910%7C
> 0%7C0%7C637405479191013587%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDA
> iLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=uUemC
> 3vjKn6NJbC5yD%2FV9sWLzcd4LaVmhD%2BgFO02Kwk%3D&reserved=0
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatab
> aseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcg
> illivray%40ctc.ca.gov%7Ca86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93caf
> d497081b54e5074e42910%7C0%7C0%7C637405479191013587%7CUnknown%7CTWFpbGZ
> sb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3
> D%7C3000&sdata=iY3AeW3RgXuOjlV8uQMTqUIJuhwWYwxYGDCgbJwjSF8%3D&
> reserved=0 Website:
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.d
> atabaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C
> a86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93cafd497081b54e5074e42910%7C
> 0%7C0%7C637405479191013587%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDA
> iLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=uUemC
> 3vjKn6NJbC5yD%2FV9sWLzcd4LaVmhD%2BgFO02Kwk%3D&reserved=0
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatab
> aseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcg
> illivray%40ctc.ca.gov%7Ca86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93caf
> d497081b54e5074e42910%7C0%7C0%7C637405479191013587%7CUnknown%7CTWFpbGZ
> sb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3
> D%7C3000&sdata=iY3AeW3RgXuOjlV8uQMTqUIJuhwWYwxYGDCgbJwjSF8%3D&
> reserved=0 Website:
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.d
> atabaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C
> a86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93cafd497081b54e5074e42910%7C
> 0%7C0%7C637405479191013587%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDA
> iLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=uUemC
> 3vjKn6NJbC5yD%2FV9sWLzcd4LaVmhD%2BgFO02Kwk%3D&reserved=0
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatab
> aseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcg
> illivray%40ctc.ca.gov%7Ca86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93caf
> d497081b54e5074e42910%7C0%7C0%7C637405479191013587%7CUnknown%7CTWFpbGZ
> sb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3
> D%7C3000&sdata=iY3AeW3RgXuOjlV8uQMTqUIJuhwWYwxYGDCgbJwjSF8%3D&
> reserved=0 Website:
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.d
> atabaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C
> a86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93cafd497081b54e5074e42910%7C
> 0%7C0%7C637405479191013587%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDA
> iLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=uUemC
> 3vjKn6NJbC5yD%2FV9sWLzcd4LaVmhD%2BgFO02Kwk%3D&reserved=0
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatab
> aseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcg
> illivray%40ctc.ca.gov%7Ca86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93caf
> d497081b54e5074e42910%7C0%7C0%7C637405479191023541%7CUnknown%7CTWFpbGZ
> sb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3
> D%7C3000&sdata=IR0ZuyGE3q6a%2BhoP0%2FI%2Fcs1q4nfxOO%2BhDjgzYIydmQc
> %3D&reserved=0 Website:
> https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.d
> atabaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C
> a86d81f0ecce4a2f3d4c08d884e7cff3%7C78276a93cafd497081b54e5074e42910%7C
> 0%7C0%7C637405479191023541%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDA
> iLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=R4q04
> s1C59vm3b54GdDCBVjJuXZCAGVv8NypZdAnqUs%3D&reserved=0
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com