Re: Using * as a literal in query criteria
- From: DanR <DanR@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 27 May 2008 14:14:35 -0700
Michel,
As you surmised, using a "double quote", ", instead of an apostrophe, ', did
not change things.
Using the LEFT (instead of LIKE) works. I'll stick with that for now.
Should this be reported to Microsoft?
--
Dan R
"Michel Walsh" wrote:
I just tested.
SELECT Table39.f1
FROM Table39
WHERE (((Table39.f1) Like [param] & "*" Or (Table39.f1) Like "[*]" &
[param] & "*"));
and it returns the expected records, from a native *Jet table* .
It seems there is a problem between Jet and MS SQL Server (at the ODBC
level, or something like that)
Can you try " instead of ' ? Should not be a problem, in general, I know,
but just in case.
Can also try
LEFT(columnName, 1+ len( [parameter] ) = "*" & parameter
instead of
columnName LIKE "[*]" & parameter & "*"
(but we lose the indexing search on the column). That does not explain the
cause of the problem, but could be a temporary patch.
Vanderghast, Access MVP
"DanR" <DanR@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1577554A-698A-4C6C-97B7-90E10957D107@xxxxxxxxxxxxxxxx
Michel,
Thanks for your quick response!
Yes, the SQL View statement is as you indicate. (I have been writing
queries for a number of years, both in Access and in ORACLE and SQL
Server,
so I am a bit surprised by this behavior.)
Here is the WHERE clause from the SQL View:
WHERE (((tablename.columnname) Like [enter SRC] & '*' Or
(tablename.columnname) Like ('[*]' & [enter SRC] & '*')))
So this seems to be fine. I don't know any way to see what the string
might
look like that is being passed to the database. (In this case the table
happens to be a linked SQL Server 2005 table.) What also seems strange is
the way it "works" to return rows with leading asterisks when I replace
"[*]"
with "?".
--
Dan R
"Michel Walsh" wrote:
What is the SQL statement the grid produces behind the scene (switch from
the graphical view to SQL view) ?
It should be like:
WHERE ( (someFieldOrExpression) LIKE ( [enter SRC] & '*' )) OR (
(someFieldOrExpression) LIKE ('[*]' & [enter SRC] & '*') )
Vanderghast, Access MVP
"DanR" <DanR@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2C25227D-6344-4CCB-AA96-817B5EF2F8BB@xxxxxxxxxxxxxxxx
Allen,
I seem to be having trouble using this technique with a concatenation
operator to look for strings that begin with an asterisk.
Here is the "condition" I have for my query:
Like [enter SRC] & '*' Or Like ('[*]' & [enter SRC] & '*')
When I run the query, it works as if the second part (after the "Or")
were
absent.
If I change "[*]" to "?" I get many results that begin with an
asterisk.
If I change the "Or" to be some specific value, e.g., '[*]07205*', then
the
square brackets seem to do their job.
Am I missing something? Might this be some minor bug?
Thanks in advance for your help.
--
Dan R
"Allen Browne" wrote:
Add square brackets, i.e.:
"A[*]"
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jeannie" <Jeannie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5B4E99A1-FCF1-4254-8450-C26837773541@xxxxxxxxxxxxxxxx
How do I override the wildcard search when I need to reference a
value
that
has the literal * in it. I am searching for anything that has a
code
of
"A*". I get any code starting with an A (e.g. A+, A-,A*, etc)
- Follow-Ups:
- Re: Using * as a literal in query criteria
- From: Michel Walsh
- Re: Using * as a literal in query criteria
- References:
- Re: Using * as a literal in query criteria
- From: Michel Walsh
- Re: Using * as a literal in query criteria
- From: DanR
- Re: Using * as a literal in query criteria
- From: Michel Walsh
- Re: Using * as a literal in query criteria
- Prev by Date: Re: SELECT string with table name of two words problem
- Next by Date: Re: True, False, or All
- Previous by thread: Re: Using * as a literal in query criteria
- Next by thread: Re: Using * as a literal in query criteria
- Index(es):
Relevant Pages
|
