[AccessD] Query expression

[Susan Harkins]

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