Find "T"s and "Z"s

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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)
 
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
..
 
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
 
Back
Top