SQL WHERE STMT PROBLEM

[Joanne]

I have the following statement that is failing after the first WHERE
statement. If I comment out the portion of the statement after the
second line, I get a recordset, else I get bsSql=Nothing. In both
instances the variable 'sGroupName' has the correct value passed to
it.

My question is how do I incorporate 2 WHERE conditions in one sql
statement? I think I have a syntax problem here and I don't know how
to fix it.

BsSql = "SELECT tblDocumentList.DocNamePath FROM tblDocumentList" & _
" WHERE (((tblDocumentList.Bookmarks) = True))" & _
" WHERE (((tblDocumentList.GroupName) = """ & _
sGroupName = """));"

Thank you for your time and knowledge
Joanne

 

[Brendan Reynolds]

WHERE FirstCondition AND SecondCondition

The keyword "WHERE" appears only once, but you can have multiple conditions
joined with "AND" and/or "OR". Use parentheses as necessary to control the
order of evaluation, eg ...

WHERE FirstCondition AND (SecondCondition OR ThirdCondition)

.... is different from ...

WHERE (FirstCondition AND SecondCondition) OR ThirdCondition

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Joanne]

Brendan
Thanks for your help on this

I added the AND to my where statement, but am still having a problem.
I suspect that it is the improper use of either the " marks or the
parens. I keep getting the

Compile Error Expected: end of statement

error msgbox. I tried parens and quotes in diff places and diff
multiples of them, but since I don't really know the where and whys of
the parens and quotes (can't figure out when to use 1, 2, or 3 of
them), I am only guessing, and cannot seem to guess correctly.

Choosing debug, in the vbeditor, the last line of this statement is
highlighted in yellow, indicating that the error is there?

BsSql = "SELECT tblDocumentList.DocNamePath FROM tblDocumentList" & _
" WHERE (((tblDocumentList.Bookmarks) = True AND" & _
(tblDocumentList.GroupName) = """ & _
sGroupName = """);"

Again, if I eliminate everything after True with ))" following true,
the rst does not = False, but what I expect to see in the rst is
there.

Please point me to an explanation of why this doesn't work.
Thank You
Joanne

 

[Brendan Reynolds]

BsSql = "SELECT tblDocumentList.DocNamePath FROM tblDocumentList" & _
" WHERE (((tblDocumentList.Bookmarks) = True AND" & _

This next bit is ouside of the quotes when it should be inside

(tblDocumentList.GroupName) = """ & _

Is sGroupName a string variable? And GroupName is a text field? Then we need
single quote delimiters, see below.

sGroupName = """);"

Sometimes it's easier to drop the line breaks, get the code working with one
long SQL string, and then break it up for readability afterwards. Try ...

BsSQL = "SELECT tblDocumentList.DocNamePath FROM tblDocumentList WHERE
tblDocumentListBookmarks = True AND tblDocumentList.GroupName = '" &
sGroupName & "'"

That's a single quote followed by a double quote after the final = sign, and
a single quote between two double quotes at the end.

I've left out the parentheses and the final semi-colon for simplicity. There
wasn't, as far as I could see, anything wrong with them, but they're not
required. Parentheses become important if there is an 'OR' involved, but we
don't have one, and Access always adds the semi-colon, but the Jet database
engine doesn't require it.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Joanne]

Brendan
sGroupName is the variable I use to send the name of the 'group' of
files that I want to take action on.

GroupName is the table 'field' that holds the group that the files are
sorted into. There are 7 such groups in this app.

Function SetBkms(sGroupName As String)
Dim oWordapp As Object
Dim oDocName As Object
Dim oRst As DAO.Recordset
Dim BsSql As String 'sql stmt to get docs with bookmarks
Dim sFilename As String
Dim BMRange As Range

Set oWordapp = CreateObject("Word.Application")

BsSql = "SELECT tblDocumentList.DocNamePath FROM tblDocumentList
WHERE tblDocumentList.Bookmarks = True AND tblDocumentList.GroupName =
'" & sGroupName = "'"

Set oRst = CurrentDb.OpenRecordset(BsSql)

If Not oRst Is Nothing Then
Do While Not oRst.EOF
sFilename = "" & oRst("DocNamePath")
If Len(Dir$(sFilename)) > 0 Then
Set oDocName = oWordapp.Documents.Open(sFilename)
If Not oDocName Is Nothing Then
With oWordapp.ActiveDocument.Bookmarks
If oDocName.Bookmarks.Exists("FName") = True Then
UpdateBookmark "Fname", Nz(Me!FName, "")
End If
etc, etc

I used your instruction and eliminated the parens and line breaks
(sure is a lot easier to read and 'try to understand') and used the
single and dbl quotes as instructed to produce the above BsSql
statement, but I still get stopped at the line:

Set oRst = CurrentDb.OpenRecordset(BsSql)

At this point: BsSql = False

You point out that

This next bit is ouside of the quotes when it should be inside

Does that mean that I should have a dbl quotes after the ---
AND tblDocumentList.GroupName --- and before the final = sign and
the quotes I corrected according to the following instruction?

That's a single quote followed by a double quote after the final = sign, and
a single quote between two double quotes at the end.

Am still a bit confused
Joanne

 

[Brendan Reynolds]

If you're a little confused, Joanne, I'm afraid I'm *very* confused! BsSql
is defined as a String variable, it is therefore not possible for it to ever
equal the Boolean value False, although it would be possible for it to
contain the String value "False" (not at all the same thing as far as a
programming language like VBA or a query language like SQL are concerned).
However, there is nothing in the code that you have posted that could
possibly assign the value "False" to the BsSql variable.

Here are two suggestions you can try.

First, it's a good idea when opening a DAO recordset to first declare a DAO
Database variable and assign the result of CurrentDb to that, rather than
using CurrentDb directly ...

Dim db As DAO.Database
Dim oRst As DAO.Recordset
....
Set db = CurrentDb
Set oRst = db.OpenRecordset(BsSql)

Second, immediately after the line that builds the SQL string, add a line
....
Debug.Print BsSql

This will print the finished SQL string to the Immediate window, which often
helps to make clear any syntax problems in the SQL string.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.