query with wildcard, etc

  • Thread starter Thread starter hedrew3
  • Start date Start date
H

hedrew3

I am trying to query for data of the form AB1200R, AB3400R, AB5600R,
etc. In other words, the 3rd and 4th chars could be anything.

I tried

select * from mytable where mycol like '*AB??00R*'.

No luck. Also tried '*AB__00R*'.

Any hints?

TIA

Pete
 
What type of table:: Jet/Access, SQL Server, other?

Why are you using * at the beginning if you always want to do wildcard on
the 3rd and 4th characters? Putting * (for Jet/Access) at beginning means
that your query could return records where the ? match is on character
positions other than 3rd and 4th, depending upon the data that are in that
field.
 
JET back-end or some other database engine?

Are you using the Access interface to run the Query or by code? If by code,
ADO code or DAO code?

If you use JET back-end and the Access Query interface then

... WHERE [mycol] Like "AB??00R"

should work.

Are you sure the 5th & 6th letters are "0" and not "O"?

HTH
Van T. Dinh
MVP (Access)
 
Van T. Dinh said:
If you are not using ADO code then the criteria should work.
Click to expand...


PLease explain this comment. "ADO Code" means VB code? "criteria
should work" means ???

TIA

Pete
 
In Access, you have number of different ways of getting the data returned
from a Query:

1. You simply use the Access Query interface which will return selected
data in a datasheet.

2. You can use VB code with the OpenQuery action to run the Query which
results to a Datasheet the same as above.

3. You can create a Recordset (think of it as the data but instead of
presented in the Datasheet, it is retained in memory for further
manipulation). There are 2 different Libraries that you can use in VB code
to create Recordsets: DAO Library (specifically designed for JET - the
database engine used in Access) and ADO Library (for general database
engine).

If you don't understand 2 & 3, then you use 1 above and the criteria I
posted worked fine when I tested.
 
Van T. Dinh said:
In Access, you have number of different ways of getting the data returned
from a Query:

1. You simply use the Access Query interface which will return selected
data in a datasheet.

2. You can use VB code with the OpenQuery action to run the Query which
results to a Datasheet the same as above.

3. You can create a Recordset (think of it as the data but instead of
presented in the Datasheet, it is retained in memory for further
manipulation). There are 2 different Libraries that you can use in VB code
to create Recordsets: DAO Library (specifically designed for JET - the
database engine used in Access) and ADO Library (for general database
engine).

If you don't understand 2 & 3, then you use 1 above and the criteria I
posted worked fine when I tested.
Click to expand...



Thanks for the clarification.
I am definitely using option 1 -- simply a query inside Access.
However, your suggestion doesn't quite work. What I am trying to do is
"like '*AB__CD*'.

TIA

Pete
 
Back
Top