how to query using Left & Mid

[Guest]

I have a table called "Data" with a column called "Sort"
the format for a row in sort is like
A0002719902

how can i create a query that selects all rows where
the first four characters equal "A820"
and
the next three = 56, 59, 61 or 66

I can't seem to get it to work in design view.
Appreciate any help,
thanks
jay

 

[Jason Lepack]

SELECT *
FROM [Data]
WHERE left([Sort],4) = "A820"
AND mid([Sort],5,2) IN ("56","59","61","66")

Cheers,
Jason Lepack

 

[Guest]

perfect, thanks so much.
if i wanted to add numbers where left 4 = 826 and mid 5,2 are 43,59,90 - do
i need to do a separate query and join them, or can it be done in one query
thanks again

SELECT *
FROM [Data]
WHERE left([Sort],4) = "A820"
AND mid([Sort],5,2) IN ("56","59","61","66")

Cheers,
Jason Lepack

I have a table called "Data" with a column called "Sort"
the format for a row in sort is like
A0002719902

how can i create a query that selects all rows where
the first four characters equal "A820"
and
the next three = 56, 59, 61 or 66

I can't seem to get it to work in design view.
Appreciate any help,
thanks
jay

 

[Jason Lepack]

SELECT *
FROM [Data]
WHERE (left([Sort],4) = "A820"
AND mid([Sort],5,2) IN ("56","59","61","66"))
OR (left([Sort],4) = "A826"
AND mid([Sort],5,2) IN ("43","59","90"))

Past this point I do no more modifications... You have the tools to do
the work.

Cheers,
Jason Lepack

perfect, thanks so much.
if i wanted to add numbers where left 4 = 826 and mid 5,2 are 43,59,90 - do
i need to do a separate query and join them, or can it be done in one query
thanks again

SELECT *
FROM [Data]
WHERE left([Sort],4) = "A820"
AND mid([Sort],5,2) IN ("56","59","61","66")

Cheers,
Jason Lepack

- Show quoted text -

 

[Ron2006]

perfect, thanks so much.
if i wanted to add numbers where left 4 = 826 and mid 5,2 are 43,59,90 - do
i need to do a separate query and join them, or can it be done in one query
thanks again

SELECT *
FROM [Data]
WHERE left([Sort],4) = "A820"
AND mid([Sort],5,2) IN ("56","59","61","66")

Cheers,
Jason Lepack

- Show quoted text -

just a thought..

If you think that you may be changing the middle set of number
periodically AND maybe need it in more than one query:
Create a small table with a record for each of these "Selecting
Numbers"
Then change the query into two queries.
First query will dynamically create all needed data with two generated
fields (the left 4 characters and the middle 2)

Then have your final query simply joined to the selecting numbers and
use an inner join (only when both records are there).

Then you will NOT need to remember where all of the special queries
are that need changes whenever that set changes. Simply change the
table and you will get what you want. (You could even do that for the
first set of 4 too so that you could possibly include more than One
value.)

Maybe something to consider.

Ron

 

[Guest]

Thank you Jason!

SELECT *
FROM [Data]
WHERE (left([Sort],4) = "A820"
AND mid([Sort],5,2) IN ("56","59","61","66"))
OR (left([Sort],4) = "A826"
AND mid([Sort],5,2) IN ("43","59","90"))

Past this point I do no more modifications... You have the tools to do
the work.

Cheers,
Jason Lepack

perfect, thanks so much.
if i wanted to add numbers where left 4 = 826 and mid 5,2 are 43,59,90 - do
i need to do a separate query and join them, or can it be done in one query
thanks again

SELECT *
FROM [Data]
WHERE left([Sort],4) = "A820"
AND mid([Sort],5,2) IN ("56","59","61","66")

Cheers,
Jason Lepack

I have a table called "Data" with a column called "Sort"
the format for a row in sort is like
A0002719902

how can i create a query that selects all rows where
the first four characters equal "A820"
and
the next three = 56, 59, 61 or 66

I can't seem to get it to work in design view.
Appreciate any help,
thanks
jay- Hide quoted text -

- Show quoted text -