Allen Browne said:Add square brackets, i.e.:
"A[*]"
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Jeannie said: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)
DanR said: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 said:Add square brackets, i.e.:
"A[*]"
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Jeannie said: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)
Michel Walsh said: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 said: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 said:Add square brackets, i.e.:
"A[*]"
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
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)
DanR said: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 said: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 said: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
:
Add square brackets, i.e.:
"A[*]"
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
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)
Michel Walsh said: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 said: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 said: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
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
:
Add square brackets, i.e.:
"A[*]"
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
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)
DanR said: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 said: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 said: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
:
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
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
:
Add square brackets, i.e.:
"A[*]"
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
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)