[AccessD] Query expression

Susan Harkins ssharkins at gmail.com
Mon Nov 9 16:11:50 CST 2020 

And there it is.. James mentioned that to me earlier and I totally get it
now. 

Susan H. 

Not Like "*[0-9]*"  works for such a simple criterion that doesn't really
need the power of Regex :)

(You were testing for a single character because you didn't have the "*"
before and after)


On 9 Nov 2020 at 13:44, Susan Harkins wrote:

> Brilliant -- thank you so much! Works great! 
> 
> I didn't think to try # it all. 
> 
> Not Like '[0-9]'
> ![0-9]
> 
> Didn't work -- which I find curious
> 
> Susan H. 
> 
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of 
> James Button via AccessD Sent: Monday, November 9, 2020 1:40 PM To:
> 'Access Developers discussion and problem solving'
> <accessd at databaseadvisors.com> Cc: James Button 
> <jamesbutton at blueyonder.co.uk> Subject: Re: [AccessD] Query expression
> 
> Ah!  Sorry  didn't notice the actual question you ware asking - But 
> for the test  the following works for me  losing all the entries with 
> digits in them (Access 2016 part of 365 Home under x64 win-10-Pro
> 2004)
> 
> " SELECT JimsB. Ref
> FROM JimsB
> WHERE (((JimsB. Ref) Not Like '*#*'));"
> 
> 
> I didn't go as far as checking it did not exclude entries with , £ % 
>   etc.
> 
> -----Original Message-----
> From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of 
> Susan Harkins Sent: Monday, November 9, 2020 6:22 PM 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://www.techonthenet.com/access/functions/numeric/val.php
> 
> 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
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

More information about the AccessD mailing list