SQL for "My brother's brother"

SQL for "My brother's brother"

Post by David W. Fores » Wed, 23 Jul 1997 04:00:00



Create your " using a variable equal to chr$(34).
q$ =3D chr$(34)
searchstr$ =3D "My brother's brother"
slqstr$ =3D "Select * from table1 where field1 =3D " & q$ & searchstr$ & =
q$

--=20
David W. Forest
LAN Specialist
Information Technologies=20
Lear Corporation - Ford Division
SBN Level 2
http://www.concentric.net/~dforest/

If you're not scared, you don't understand.=20

Jonathan Lin wrote in article =

Quote:>Hi there,

>Does anyone know how to make a SQL sentence to search a string like "My
>brother's brother".

>Thanks,

 
 
 

SQL for "My brother's brother"

Post by Jonathan Li » Fri, 25 Jul 1997 04:00:00


Hi, David,

Thanks for your reply.

I am using a SQL like this:
SELECT * FROM table WHERE field="My brother's brother";
I found it works fine under Access97. But if fails when I execute this  SQL
from VB DAO. I think that the problem should be the symbol ' .  Because
once I took the 's out of my string, it works fine under DAO.

Any idea?
Thanks,

Jonathan



Create your " using a variable equal to chr$(34).
q$ = chr$(34)
searchstr$ = "My brother's brother"
slqstr$ = "Select * from table1 where field1 = " & q$ & searchstr$ & q$

--
David W. Forest
LAN Specialist
Information Technologies
Lear Corporation - Ford Division
SBN Level 2
http://www.concentric.net/~dforest/

If you're not scared, you don't understand.

Jonathan Lin wrote in article

Quote:>Hi there,

>Does anyone know how to make a SQL sentence to search a string like "My
>brother's brother".

>Thanks,

----------

 
 
 

SQL for "My brother's brother"

Post by Andy » Sat, 26 Jul 1997 04:00:00



> Hi, David,

> Thanks for your reply.

> I am using a SQL like this:
> SELECT * FROM table WHERE field="My brother's brother";
> I found it works fine under Access97. But if fails when I execute this  SQL
> from VB DAO. I think that the problem should be the symbol ' .  Because
> once I took the 's out of my string, it works fine under DAO.
> Any idea?

Yeah, it thinks you're terminating the string.
My preferred approach is to stop the users typing ' or " in the field if
this is a reasonable option....

Otherwise maybe this'll help, from Access 97 help:

Quotation Marks in Strings

See Also         Example

In situations where you must construct strings to be concatenated, you
may need to embed a string within another string, or a string variable
within a string. Situations in which you might need to nest one string
within another include:

     When specifying criteria for domain aggregate functions.
     When specifying criteria for the Find methods.
     When specifying criteria for the Filter property of a form.
     When building SQL strings.

In all of these instances, Microsoft Access must pass a string to the
Microsoft Jet database engine. When you specify a criteria argument for
a domain aggregate function, for example, Microsoft Access must evaluate
any variables, concatenate them into a string, and then pass the entire
string to the Jet database engine.
If you embed a numeric variable, Microsoft Access evaluates the variable
and simply concatenates the value into the string. If the variable is a
text string, however, the resulting criteria string will contain a
string within a string. A string within a string must be identified by
string delimiters. Otherwise, the Jet database engine won't be able to
determine which part of the string is the value you want to use.

The string delimiters aren't actually part of the variable itself, but
they must be included in the string in the criteria argument. You can
use either single (') or double (") quotation marks. There are three
different ways to construct the string in the criteria argument. Each
method results in a criteria argument that looks like one of the two
following examples.

"[LastName] = 'Smith'"

?or?

"[LastName] = "Smith""

Include Single Quotation Marks

You should include single quotation marks in the criteria argument in
such a way that when the value of the variable is concatenated into the
string, it will be enclosed within the single quotation marks. For
instance, suppose your criteria argument must contain a string variable
called strName. You could construct the criteria argument as in the
following example:

"[LastName] = '" & strName & "'"

When the variable strName is evaluated and concatenated into the
criteria string, the criteria string becomes:

"[LastName] = 'Smith'"

Note   This syntax does not permit the use of apostrophes (') within the
value of the variable itself. If the value of the string variable
includes an apostrophe, Microsoft Access generates a run-time error. If
your variable may represent values containing apostrophes, consider
using one of the other syntax forms discussed in the following sections.

Include Double Quotation Marks

You should include double quotation marks within the criteria argument
in such a way so that when the value of the variable is evaluated, it
will be enclosed within the quotation marks. Within a string, you must
use two sets of double quotation marks to represent a single set of
double quotation marks. You could construct the criteria argument as in
the following example:

"[LastName] = """ & strName & """"

When the variable strName is evaluated and concatenated into the
criteria argument, each set of two double quotation marks is replaced by
one single quotation mark. The criteria argument becomes:

"[LastName] = 'Smith'"

This syntax may appear more complicated than the single quotation mark
syntax, but it enables you to embed a string that contains an apostrophe
within the criteria argument. It also enables you to nest one or more
strings within the embedded string.

Include a Variable Representing Quotation Marks

You can create a string variable that represents double quotation marks,
and concatenate this variable into the criteria argument along with the
value of the variable. The ANSI representation for double quotation
marks is Chr$(34); you could assign this value to a string variable
called strQuote. You could then construct the criteria argument as in
the following example:

"[LastName] = " & strQuote & strName & strQuote

When the variables are evaluated and concatenated into the criteria
argument, the criteria argument becomes:

"[LastName] = "Smith""

--
Andy O'Neill

 
 
 

1. SQL for 'My brother's brother'

Hi Jonathan,

           Try this sql statement.

SELECT * FROM [Table Name] WHERE Like "*My brother's brother*"

Hope this helps you,

Venkatesh S

Aditi Corp.
http://www.aditi.com
Visit our FAQ Page At : http://www.aditi.com/knowledge/vb/index.html

 Aditi Corp was formerly known as NetQuest Inc.

2. Difficult Parent Child SQL statement

3. Accessing data from a 'brother' file

4. Which DB connection should I use?

5. Brother's Keeper and btrieve?

6. Foreign key constraint conflicts

7. Printing Labels to Brother PC Label

8. BRAIN KILLER-- Ranking 'measures members' when 'measures members' are the columns

9. Sieko, Brother label printers

10. Access 2000 Reports on Brother printer

11. CAN′T GET BROTHER PCL COMANDS ACTIVED!!!

12. max of ("...","...","..")

13. I work for big brother