[AccessD] Query expression
McGillivray, Don
DMcGillivray at ctc.ca.gov
Mon Nov 9 13:43:03 CST 2020
Dunno that it's any simpler, but you can also do a comparison between the length of the value and the result of a 10-layer nested Replace() that swaps all of the numbers for an empty string. Any where that delta is zero is a candidate. Greater than zero is left out.
Select * from MyTable where (len(ColName) - len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(ColName,"0","") ,"1","") ,"2","") ,"3","") ,"4","") ,"5","") ,"6","") ,"7","") ,"8","") ,"9",""))) = 0
I just typed that freehand, so don't shoot me if I missed a parenthesis or comma in there. You get the idea, I'm sure.
-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Susan Harkins
Sent: Monday, November 9, 2020 10:22 AM
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://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.techonthenet.com%2Faccess%2Ffunctions%2Fnumeric%2Fval.php&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554698816%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=AUTm6YqkZmx3k3bITlSmZI%2F%2B6kwNr5YgK1E8BwNBvJE%3D&reserved=0
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
https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554698816%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=DKRJGdmj6lvQ48OVYsVQ5u48PXAk%2F66KikaRTClKRk4%3D&reserved=0
Website: https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.databaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554698816%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=%2BWFqA0%2Bn97U0CczEAkVNKoGqXQujANn5I2CTKuWQYF8%3D&reserved=0
--
AccessD mailing list
AccessD at databaseadvisors.com
https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554698816%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=DKRJGdmj6lvQ48OVYsVQ5u48PXAk%2F66KikaRTClKRk4%3D&reserved=0
Website: https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.databaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554708774%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Fvo7fyPMYzjhaRH3i19Hrl2hR9JGO8efOEjCVB6JEV4%3D&reserved=0
--
AccessD mailing list
AccessD at databaseadvisors.com
https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554708774%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=k6aHbsyivZehnaQIL9O%2BnHP4DgVPqdQ1qWeKSDyE0G4%3D&reserved=0
Website: https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.databaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554708774%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Fvo7fyPMYzjhaRH3i19Hrl2hR9JGO8efOEjCVB6JEV4%3D&reserved=0
--
AccessD mailing list
AccessD at databaseadvisors.com
https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554708774%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=k6aHbsyivZehnaQIL9O%2BnHP4DgVPqdQ1qWeKSDyE0G4%3D&reserved=0
Website: https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.databaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554708774%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Fvo7fyPMYzjhaRH3i19Hrl2hR9JGO8efOEjCVB6JEV4%3D&reserved=0
--
AccessD mailing list
AccessD at databaseadvisors.com
https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554708774%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=k6aHbsyivZehnaQIL9O%2BnHP4DgVPqdQ1qWeKSDyE0G4%3D&reserved=0
Website: https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.databaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554708774%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Fvo7fyPMYzjhaRH3i19Hrl2hR9JGO8efOEjCVB6JEV4%3D&reserved=0
--
AccessD mailing list
AccessD at databaseadvisors.com
https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554708774%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=k6aHbsyivZehnaQIL9O%2BnHP4DgVPqdQ1qWeKSDyE0G4%3D&reserved=0
Website: https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.databaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554708774%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Fvo7fyPMYzjhaRH3i19Hrl2hR9JGO8efOEjCVB6JEV4%3D&reserved=0
--
AccessD mailing list
AccessD at databaseadvisors.com
https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554708774%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=k6aHbsyivZehnaQIL9O%2BnHP4DgVPqdQ1qWeKSDyE0G4%3D&reserved=0
Website: https://gcc02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.databaseadvisors.com%2F&data=04%7C01%7Cdmcgillivray%40ctc.ca.gov%7C1ebc45610a96493e85d808d884dc6b68%7C78276a93cafd497081b54e5074e42910%7C0%7C0%7C637405429554708774%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=Fvo7fyPMYzjhaRH3i19Hrl2hR9JGO8efOEjCVB6JEV4%3D&reserved=0
More information about the AccessD
mailing list