" and '

[Rob Oldfield]

I was looking for something on the web earlier and came across this on an
Outlook VBA group...

function quote(t as string) as string
quote=chr(34)&t&chr(34)
end function

then using quote(str) instead of things like """"&str&""""

I hadn't seen the idea before. Comments?

 

[Wayne Morgan]

Just another way to "skin the cat".

 

[Allen Browne]

A user-defined function call that makes 2 more function calls has to be
about the most inefficient way to execute this, and I'm not convinced it
makes it easier to read. You still have to double up the quotes to get the
embedded quote marks you need for literal values in a WHERE clause, so you
would be using:
"WHERE = [City] = " & quote(quote(Me.txtCity)) & " ORDER BY ...

In terms of execution speed, it would be better to declare a constant in the
General Declarations section (top) of a standard module:
Public stcQuote = """"
You can then build your strings with:
"WHERE [City] = " & strcQuote & strcQuote & Me.txtCity & strcQuote &
strcQuote & " ORDER BY ...

Realistically, nothing is as simple to type and as fast to execute as:
"WHERE [City] = """ & Me.txtCity & """ ORDER BY ...

 

[Dale Fye]

Rob,

I like this. I usually do something like:
strSQL = ... _
& "WHERE [SomeField] = " & chr$(34) & strSomeValue & chr$(34)

because this is alot easier for me to read than """, or was that """"

With this, it would be as simple as:

strSQL = ... _
& "WHERE [SomeField] = " & Quote(strSomeValue)

I'm gonna have to put this trick in my bag.

Thanks

 

[Steve Schapel]

Realistically, nothing is as simple to type and as fast to execute as:
"WHERE [City] = """ & Me.txtCity & """ ORDER BY ...

I agree that I can't see any advantage in using such a function. The
habit I have got into is...
"WHERE [City] = '" & Me.txtCity & "' ORDER BY ...

....or, if there is a chance the data might ihnclude an apostrophe...
"WHERE [City] = " & """" & Me.txtCity & """" & " ORDER BY ...

I find both of these easy and no problem. Allen, how do these compare
with the way you did it?

 

[Allen Browne]

Steve, I never use the single quote character to delimit a string within a
string, because the apostrophe is just too common. (The only exception I
make to that rule is if I have the literal available at design time, so I
know for sure the is no embedded single quote.)

IME, the double-quote character is rare in most databases, the most common
examples being the abbreviation for inches or seconds. If I can get away
with it, I actually block the entry of the double-quote char, by using the
KeyDown event of each form, and documenting it. (I find that's easier than
trying to write generic functions that can be called from other generic
functions where you are not sure whether you have Replace()d your
double-quote with double double-quotes or not.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Realistically, nothing is as simple to type and as fast to execute as:
"WHERE [City] = """ & Me.txtCity & """ ORDER BY ...

I agree that I can't see any advantage in using such a function. The
habit I have got into is...
"WHERE [City] = '" & Me.txtCity & "' ORDER BY ...

...or, if there is a chance the data might ihnclude an apostrophe...
"WHERE [City] = " & """" & Me.txtCity & """" & " ORDER BY ...

I find both of these easy and no problem. Allen, how do these compare
with the way you did it?

 

[Steve Schapel]

Thanks Allen, that clarifies it for me. I guess I often have examples
where I know the data in the text field in question will never include a
' or ", in fact more often than not I would say.

 

[Rob Oldfield]

Interesting. I was primarily thinking that it might aid readability, but
that doesn't seem to be the case.

It also seems we have our own style, which isn't going to help anyone coming
along later to look at our code, but I guess there's not a great deal to be
done about that.

 

[rkc]

I was looking for something on the web earlier and came across this on an
Outlook VBA group...

function quote(t as string) as string
quote=chr(34)&t&chr(34)
end function

then using quote(str) instead of things like """"&str&""""

I hadn't seen the idea before. Comments?

Doesn't anyone use Application.BuildCriteria?

 

[John Nurick]

A good modern compiler should optimise the function call out of
expressions like Chr(34). I don't know whether the VBA compiler
qualifies.

A user-defined function call that makes 2 more function calls has to be
about the most inefficient way to execute this
[snip]

I was looking for something on the web earlier and came across this on an
Outlook VBA group...

function quote(t as string) as string
quote=chr(34)&t&chr(34)
end function

 

[Dirk Goldgar]

Doesn't anyone use Application.BuildCriteria?

I use it when I want to provide a generic criteria-building function
that will need to support that degree of flexibility. I don't use it
when I can pin down the allowable criteria more thoroughly.
BuildCriteria doesn't cope well with embedded double-quotes.

 

[Marshall Barton]

Doesn't anyone use Application.BuildCriteria?

Only when I have really smart, well trained users. This
function provides way too much power for walk-in users to
handle ;-)

In my own code where I know the criteria a priori, it seems
like using a sledge hammer to crack a peanut.

 

[John Welch]

I don't think you'd need to call the quote function twice. The following
(suggested by vba help) seems to work just fine:
"WHERE [City] = " & quote(Me.txtCity) & " ORDER BY ...

If you shorten the function name to qq, then you have to type less:
"WHERE [City] = " & qq(Me.txtCity) & " ORDER BY ...

But it is still as many keystrokes as
"WHERE [City] = """ & Me.txtCity & """ ORDER BY ..., as suggested by Allen.

I wouldn't think the time difference would be worth worrying about unless
you were doing this thousands of times in a row. No?
-John