Find "T"s and "Z"s

[EllenM]

Hello,
I need a query to find "T"s and "Z"s which are preceded by a single number
and followed by 4 numbers. For instance, "1Z0235" or "8Z4086". This pattern
is found in the midst of a lot of other verbage.

Thanks so much for your help,
Ellen

 

[Chinny03]

You can use wildcards. For example, "1Z0235" you would want to enter *Z* to
find the records with Z. You would do the same thing with T's except enter
*T* . It looks for characters before the letter you are looking for and
also after the character. Hopefully this helps.

 

[Steve]

In the field you are searching in your query, put the following in the
criteria:

Like "#" & "T" & "####"

and underneath that where it says Or, put the following:

Like "#" & "Z" & "####"

This will give you all the "T"s and "Z"s which are preceded by 1 number and
succeeded by 4 numbers.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John Spencer]

LIKE "*#[TZ]####*"

If you are using ANSI compliant SQL
LIKE '%[0-9][TZ][0-9][0-9][0-9][0-9]%'

That will find records where the value is contained in the field. It will
not find and highlight the text within the field.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[David Cox]

As I read the question you may be looking for multiple T's and Z's amongst a
long string of verbage.
If so it needs to use the instr and mid functions.and possibly a loop.

e.g. outline air code

intM=2
intN= Instr(intM,verbage,"T")
If intN>0 then
strpossible = mid(verbage&" ",intM-1,6)
'test strpossible for correct format

else
intN=Instr(intM,verbage,"Z")
If intN>0 then
strpossible = mid(verbage&" ",intM-1,6)
'test strpossible for correct format
....
endif
endif
'if we need to look for more
if intN >0 then
verbage = mid(verbage&" ",intN+4)
' and loop until verbage searched completely