Query looks for only partial information in a field

[Guest]

I have a field in my Contract table called Number that has 2 alpha characters
followed by 5 digits. I want to create a query that looks at the first 2
digits in the Number field. How can I code this?

 

[fredg]

I have a field in my Contract table called Number that has 2 alpha characters
followed by 5 digits. I want to create a query that looks at the first 2
digits in the Number field. How can I code this?

1) If the data ia always in the form of "AB12345" then use
Exp:Mid([FieldName],3,2)

2) Number is a reserved Access/VBA/Jet word and should not be used as
a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[Guest]

Number is a Reserved word and should be avoided. For lists of the Reserved
words to avoid, please see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335

If you want to view only the first two-digit sequence for each record, then
try:

SELECT Mid(Number, 3, 2) AS Digits
FROM tblMyTable;

.. . . where tblMyTable is the name of the table.

If you want only the records for the specific first two-digit sequence of
the five digits, such as "12," then try:

SELECT Mid(Number, 3, 2) AS Digits
FROM tblMyTable
WHERE (Mid(Number, 3, 2) = "12")

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Tom Ellison]

Dear Lorraine:

In addition to the responses you already have, here's another suggestion.

If the Number column has 3 (or more) components, then it should be 3 (or
more) columns. It's relatively easy to combine columns in code. It's more
laborious to decompose a column. There is a principle of database design
that no column should be a composite of portions that need to be considered
separately. This is called "atomicity."

Tom Ellison