Re: SQL WHERE STMT PROBLEM
From: Brendan Reynolds (brenreyn)
Date: 10/01/04
- Next message: Allen Browne: "Re: Dates: Month and Year"
- Previous message: burg: "Dates: Month and Year"
- In reply to: Joanne: "Re: SQL WHERE STMT PROBLEM"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 1 Oct 2004 10:25:50 +0100
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. "Joanne" <JoBobBuss@sbcglobal.net> wrote in message news:%23V4gIpzpEHA.1988@TK2MSFTNGP09.phx.gbl... > 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 >>> (tblDocumentList.GroupName) = """ & _ > 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 wrote: > >> >>> 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. >> > >
- Next message: Allen Browne: "Re: Dates: Month and Year"
- Previous message: burg: "Dates: Month and Year"
- In reply to: Joanne: "Re: SQL WHERE STMT PROBLEM"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
