[AccessD] Query expression

[Susan Harkins]

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