Help with String Concatenation

  • Thread starter Thread starter markm
  • Start date Start date
M

markm

This will seem trivial to most others, but I'm having
trouble building a SQL query that uses text qualifiers.
What I'm TRYING to do is to count the number of records in
a Dynaset that is created from a Query and return it in a
variable. I'm having problems with the query part. It
works fine is a Query, but obviously not in VB. I know
that the proble is with the quotes, but I can't find any
rules in the literature that I have available to me.
Could someone explain the rules and help with the SQL
statement and count? Thanks and Happy Holidays!

My code follows:

xQueryCriteria = "SELECT * FROM TASKsubm WHERE
(((TASKsubm.TASK_STAT) Is Null Or (TASKsubm.TASK_STAT)
= "In-Progress" & Or (TASKsubm.TASK_STAT) = "Hold")

Set MyRS = MyDB.OpenRecordset(xQueryCriteria)
MyRS.MoveLast
MyRightRecordCount = MyRS.RecordCount
 
xQueryCriteria = "SELECT * FROM TASKsubm WHERE
(((TASKsubm.TASK_STAT) Is Null Or (TASKsubm.TASK_STAT)
= "In-Progress" & Or (TASKsubm.TASK_STAT) = "Hold")

for vb compilator You need to do that in this way:

xQueryCriteria = "SELECT * " & _
"FROM TASKsubm " & _
"WHERE (((TASKsubm.TASK_STAT) Is Null)
Or " & _
"((TASKsubm.TASK_STAT) Like ""In-
Progress"") Or " & _
"((TASKsubm.TASK_STAT) Like ""Hold""));"

Another way:

xQueryCriteria = "SELECT * " & _
"FROM TASKsubm " & _
"WHERE (((TASKsubm.TASK_STAT) Is Null)
Or " & _
"((TASKsubm.TASK_STAT) Like 'In-
Progress') Or " & _
"((TASKsubm.TASK_STAT) Like 'Hold'));"


Test it using MsgBox:
MsgBox xQueryCriteria

For each text field use operator Like
For each word You need to use: ""word"" or 'word'
For each numeric field use operator =
 
markm said:
This will seem trivial to most others, but I'm having
trouble building a SQL query that uses text qualifiers.
What I'm TRYING to do is to count the number of records in
a Dynaset that is created from a Query and return it in a
variable. I'm having problems with the query part. It
works fine is a Query, but obviously not in VB. I know
that the proble is with the quotes, but I can't find any
rules in the literature that I have available to me.
Could someone explain the rules and help with the SQL
statement and count? Thanks and Happy Holidays!

My code follows:

xQueryCriteria = "SELECT * FROM TASKsubm WHERE
(((TASKsubm.TASK_STAT) Is Null Or (TASKsubm.TASK_STAT)
= "In-Progress" & Or (TASKsubm.TASK_STAT) = "Hold")

Set MyRS = MyDB.OpenRecordset(xQueryCriteria)
MyRS.MoveLast
MyRightRecordCount = MyRS.RecordCount
Click to expand...

From the looks of things your problem is in the embedding of the
double-quote character (") inside a string literal that is itself
delimited by double-quotes. You started with SQL from the query
designer, which I'm going to reformat slightly for clarity:

SELECT * FROM TASKsubm WHERE
(TASKsubm.TASK_STAT Is Null)
Or (TASKsubm.TASK_STAT = "In-Progress")
Or (TASKsubm.TASK_STAT = "Hold")

I've adjusted the parentheses, removing unnecessary ones added by the
query designer but leaving a pair around each subclause of the WHERE
clause.

Now you need to transform this whole thing into one quoted string and
assign it to xQueryCriteria. You could make it into one long string,
but I find it's best to break the string into parts, one on each line,
and concatenate them together -- it's easier to read that way. Taking
that approach, the next incarnation of the statement would be:

'*** NOTE: THE FOLLOWING DOESN'T WORK
xQueryCriteria = _
"SELECT * FROM TASKsubm WHERE " & _
"(TASKsubm.TASK_STAT Is Null) " & _
"Or (TASKsubm.TASK_STAT = "In-Progress") " & _
"Or (TASKsubm.TASK_STAT = "Hold")"

So far, so good, but we've still got a problem caused by the embedded
double-quotes around "In-Progress" and "Hold". They will cause
premature termination of the quoted literal, and confuse the dickens out
of the VBA compiler.

Now, in this case, since you know the text values that must be embedded
in the SQL string, and you know that they won't contain the single-quote
character ('), you can replace the embedded double-quotes with
single-quotes:

xQueryCriteria = _
"SELECT * FROM TASKsubm WHERE " & _
"(TASKsubm.TASK_STAT Is Null) " & _
"Or (TASKsubm.TASK_STAT = 'In-Progress') " & _
"Or (TASKsubm.TASK_STAT = 'Hold')"

That works, because the single- and double-quote characters may both be
used to delimit a text value in SQL (though not in VBA).

A more general solution, when the literal values may contain the
single-quote character, is to "double-up" the double-quote characters
inside the string literal, like this:

xQueryCriteria = _
"SELECT * FROM TASKsubm WHERE " & _
"(TASKsubm.TASK_STAT Is Null) " & _
"Or (TASKsubm.TASK_STAT = ""In-Progress"") " & _
"Or (TASKsubm.TASK_STAT = ""Hold"")"

That works because inside a quoted string literal, a single occurrence
of the quote character may be represented by a pair of that character
placed side by side. For example, the string literal

"ABC""DEF"

evaluates to

ABC"DEF

Another subterfuge often used is to use the function Chr(34) to return
the double-quote character. So you could also write this:

xQueryCriteria = _
"SELECT * FROM TASKsubm WHERE " & _
"(TASKsubm.TASK_STAT Is Null) " & _
"Or (TASKsubm.TASK_STAT = " & _
Chr(34) & "In-Progress" & Chr(34) & ") " & _
"Or (TASKsubm.TASK_STAT = "
Chr(34) & "Hold" & Chr(34) & ")"

That will work, too, but seems a bit obscure for your current purpose.
 
losmac said:
xQueryCriteria = "SELECT * FROM TASKsubm WHERE
(((TASKsubm.TASK_STAT) Is Null Or (TASKsubm.TASK_STAT)
= "In-Progress" & Or (TASKsubm.TASK_STAT) = "Hold")

for vb compilator You need to do that in this way:

xQueryCriteria = "SELECT * " & _
"FROM TASKsubm " & _
"WHERE (((TASKsubm.TASK_STAT) Is Null)
Or " & _
"((TASKsubm.TASK_STAT) Like ""In-
Progress"") Or " & _
"((TASKsubm.TASK_STAT) Like ""Hold""));"

Another way:

xQueryCriteria = "SELECT * " & _
"FROM TASKsubm " & _
"WHERE (((TASKsubm.TASK_STAT) Is Null)
Or " & _
"((TASKsubm.TASK_STAT) Like 'In-
Progress') Or " & _
"((TASKsubm.TASK_STAT) Like 'Hold'));"


Test it using MsgBox:
MsgBox xQueryCriteria

For each text field use operator Like
For each word You need to use: ""word"" or 'word'
For each numeric field use operator =
Click to expand...

Why on earth would you use the Like operator when you want to test for
equality?
 
What I'm TRYING to do is to count the number of records in
a Dynaset that is created from a Query
Click to expand...


' this is the count-up query
strSQL = "SELECT COUNT(*) FROM TaskSubm " & vbNewLine & _
"WHERE TASK_STAT IS NULL " & vbNewLine & _
" OR TASK_STAT = ""In-Progress"" " & vbNewLine & _
" OR TASK_STAT = ""Hold"";"

' get the answer from the database -- using DAO library
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)

' extract the result
dwMyCount = rs.Fields(0)

' and tidy up
rs.Close
Set rs = Nothing



This is functionally equivalent to the following:


' just the WHERE clause without the keyword
strWHERE = "TASK_STAT IS NULL " & vbNewLine & _
" OR TASK_STAT = ""In-Progress"" " & vbNewLine & _
" OR TASK_STAT = ""Hold"";"

' now get VBA to do all the work
dbMyCount = DCount("*", "TaskSubm", strWHERE)

' nothing to tidy up



Hope that helps


Tim F
 
markm said:
This will seem trivial to most others, but I'm having
trouble building a SQL query that uses text qualifiers.
What I'm TRYING to do is to count the number of records in
a Dynaset that is created from a Query and return it in a
variable. I'm having problems with the query part. It
works fine is a Query, but obviously not in VB. I know
that the proble is with the quotes, but I can't find any
rules in the literature that I have available to me.
Could someone explain the rules and help with the SQL
statement and count? Thanks and Happy Holidays!

My code follows:

xQueryCriteria = "SELECT * FROM TASKsubm WHERE
(((TASKsubm.TASK_STAT) Is Null Or (TASKsubm.TASK_STAT)
= "In-Progress" & Or (TASKsubm.TASK_STAT) = "Hold")
Click to expand...


Yes, the quotes are all mixed up and there's an extra & in
there. Try this:

xQueryCriteria = "SELECT * FROM TASKsubm WHERE
TASKsubm.TASK_STAT Is Null Or TASKsubm.TASK_STAT
= ""In-Progress"" Or TASKsubm.TASK_STAT = ""Hold"""

There was an unbalanced parenthesis too, but since they
weren't really needed, I just removed them.
 
losmac said:
xQueryCriteria = "SELECT * FROM TASKsubm WHERE
(((TASKsubm.TASK_STAT) Is Null Or (TASKsubm.TASK_STAT)
= "In-Progress" & Or (TASKsubm.TASK_STAT) = "Hold")

for vb compilator You need to do that in this way:

xQueryCriteria = "SELECT * " & _
"FROM TASKsubm " & _
"WHERE (((TASKsubm.TASK_STAT) Is Null)
Or " & _
"((TASKsubm.TASK_STAT) Like ""In-
Progress"") Or " & _
"((TASKsubm.TASK_STAT) Like ""Hold""));"

Another way:

xQueryCriteria = "SELECT * " & _
"FROM TASKsubm " & _
"WHERE (((TASKsubm.TASK_STAT) Is Null)
Or " & _
"((TASKsubm.TASK_STAT) Like 'In-
Progress') Or " & _
"((TASKsubm.TASK_STAT) Like 'Hold'));"


Test it using MsgBox:
MsgBox xQueryCriteria

For each text field use operator Like
For each word You need to use: ""word"" or 'word'
For each numeric field use operator =
Click to expand...


That looks like it will work ok, but please note that the
only time you should use the LIKE operator is when you are
using wildcard characters. You can use = with text fields
when you only want an exact match.
 
Dirk,

Your explanation was succinct and on-target. Your example
worked great and now I understand how the quotes work.

Thanks for your help!
Mark
 
Thanks Tim,

I was just about to post a continuation to my problem.....
The Query actually wasn't all of it. Ther were a few more
lines, one of which contained text from a Combo box that I
was "Like"ing to a field. It wasn't working when I used
the OpenRecordset method, but works great using the DCount
function. I'd seen the function but didn't know enough
about the parameters.


Thanks to all for ALL the help...have a great Holiday.

Mark
 
It wasn't working when I used
the OpenRecordset method, but works great using the DCount
function.
Click to expand...

In that case, is this just a problem with ADO/ DAO libraries?

B Wishes


Tim F
 
Back
Top