[AccessD] Query expression

[Susan Harkins]

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