Like criteria

[vp]

I created a query which filters a column containing always text with 7
characters.
The criteria is ( Like "*X" ). It keeps all the rows which has the last
character as X.
I saved the query with a name.
The query returns the found rows when used from the inside the database.

But this query does not return any data if imported in Excel for simle
import of data or in Word for the purpose of mail merge; that is when opened
from outside its database.
It seems that Like criteria is not working when used from outside the
database.

I did not tryed something else. It is interesting to know why is that, what
is wrong and what workaround is suggested.

Thank you

 

[MGFoster]

I created a query which filters a column containing always text with 7
characters.
The criteria is ( Like "*X" ). It keeps all the rows which has the last
character as X.
I saved the query with a name.
The query returns the found rows when used from the inside the database.

But this query does not return any data if imported in Excel for simle
import of data or in Word for the purpose of mail merge; that is when opened
from outside its database.
It seems that Like criteria is not working when used from outside the
database.

I did not tryed something else. It is interesting to know why is that, what
is wrong and what workaround is suggested.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use the Excel menu item > Data > Get External Data > Create a Query.
The MSQuery program will run & ask you for a data source. Select
Microsoft Access. Then navigate to the database (where the tables are)
and select the table(s) you need in your query. Follow the dialog box
instructions to complete the query.

For LIKE use the % wildcard instead of the * wildcard.

MS Word can probably use the MSQuery's queries if you save them.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfEYooechKqOuFEgEQKLKACfWu9iCCuxIsjZ4OaWvp1tjb5hYNUAoNXy
mFJlnppf9+WCWzMQRvPtLjsH
=74E6
-----END PGP SIGNATURE-----

 

[vp]

I tried
Like "%X"
and no data was retrieved , even in the current database.

 

[John W. Vinson]

I tried
Like "%X"
and no data was retrieved , even in the current database.

You're speaking the wrong dialect of SQL for Access. % is the "match any
string" wildcard in SQL/Server; in JET/ACE (Access) databases the wildcard you
want is *.

LIKE "*X"

will return all records where the field ends in X.

To find all records where the field contains X anywhere within it use

LIKE "*X*"

 

[vp]

I use mail merge from Word with the results of a chain of aprox 10 queries
in cascade. In the first queries I use Like "*X" criteria. I need it as *X
not *X*.

I think I can't replicate all the cascadeted queries in Word. So I need to
get the data out of the database, somehow, preferably with no manual
intervention.

 

[raskew via AccessMonster.com]

Since you're looking for the rightmost character in a field, why not
something of this nature (change table/field names as appropriate)

SELECT
Customers.CustomerID
, Right(Trim([CompanyName]),1) AS Expr1
FROM
Customers
WHERE
(((Right(Trim([CompanyName]),1))="x"));

Bob

I use mail merge from Word with the results of a chain of aprox 10 queries
in cascade. In the first queries I use Like "*X" criteria. I need it as *X
not *X*.

I think I can't replicate all the cascadeted queries in Word. So I need to
get the data out of the database, somehow, preferably with no manual
intervention.
[quoted text clipped - 11 lines]

LIKE "*X*"