Qry and LIke and Unknown character

pdvsa
pdvsa used Ask the Experts™
on
experts,

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));
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Yes, "?" represents a single character (including a space). And "#" looks for a single digit, so if you need to match for 6 digits, you need to put 6 #'s.


http://www.techonthenet.com/access/queries/like.php
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<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));


;-)

JeffCoachman
pdvsaProject finance

Author

Commented:
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 CoachmanMIS Liason
Most Valuable Expert 2012

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

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

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial