[AccessD] Query expression

[Susan Harkins]

That's what I came up with but as a query expression (grid): 


> 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*'


-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Paul Hartland via AccessD
Sent: Monday, November 9, 2020 12:55 PM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Cc: Paul Hartland <paul.hartland at googlemail.com>
Subject: Re: [AccessD] Query expression

Hi Susan,

Just knocked up a quick table put some data in and done the query below, the not like seemed to work
Table1 *Table1*
YourField
L12-34534
Test
46237
asdjhaskld
434586
jfhd87
dsfjhjkl884
lastone

SELECT Table1.YourField
FROM Table1
WHERE (((Table1.YourField) Not Like "*0*" And (Table1.YourField) Not Like "*1*" And (Table1.YourField) Not Like "*2*" And (Table1.YourField) Not Like "*3*" And (Table1.YourField) Not Like "*4*" And (Table1.YourField) Not Like "*5*" And (Table1.YourField) Not Like "*6*" And (Table1.YourField) Not Like "*7*" And (Table1.YourField) Not Like "*8*" And (Table1.YourField) Not Like "*9*"));

Paul

On Mon, 9 Nov 2020 at 16:31, Susan Harkins <ssharkins at gmail.com> wrote:

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


--
Paul Hartland
paul.hartland at googlemail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com