Avatar of pdvsa
Flag for United States of America asked on

Qry and LIke and Unknown character


I need to show records that have a contract number like US-###### (6 digits)
The issue is that sometimes a user forgets the 'dash'

I have developed a qry and not sure if it is correct.
Please see bold part below.
I am not sure if I need to test for 6 digits or not or just have 1 '#' sign.
I think the "#" sign represents a digit but not sure about that.
The "?" represents any character and a space I think.
(excuse me....not a programmer)

thank you

SELECT Projects.ProjectNo, Projects.ContractingEntity
FROM Projects
WHERE (((Projects.ProjectNo) Like "*US?#*") AND ((Projects.ContractingEntity)<>2));
Microsoft AccessSQL

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

<The issue is that sometimes a user forgets the 'dash'>
You should not be forcing the user to intertface with the query grid directly.
Your user interface should include the "-" so there is no need to remember it in the first place.
Make sense?

Something *roughly* like this:

SELECT Projects.ProjectNo, Projects.ContractingEntity
FROM Projects
WHERE (((Projects.ProjectNo) Like "US-" & [Enter Six Digit Project No.]) AND ((Projects.ContractingEntity)<>2));

Or better yet, create a combobox with all existing contract numbers, (from the unique entries in the "Contracts" table) so, again, the user can just select it without having to "remember anything anything...
There is a combobox wizard to walk you through the steps...
Then the SQL simply becomes:

SELECT Projects.ProjectNo, Projects.ContractingEntity
FROM Projects
WHERE (((Projects.ProjectNo)=[Forms]![YourForm]![cboContracts]) AND ((Projects.ContractingEntity)<>2));



Thomas: that works for me.  

Boag:  this field is not only a contract no but has other text in it...it contains contract and reference no and other items.  It captures a lot of stuff instead of having a separate field for each.  
Jeffrey Coachman

If you have a solution that works for you then roll with it.

My point was just that perhaps this system could be simplified.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.