Re: Using * as a literal in query criteria
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Tue, 27 May 2008 16:25:22 -0400
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: DanR
- 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
- Prev by Date: Re: SELECT string with table name of two words problem
- Next by Date: Re: Top Values Not Working
- 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
|
