VBA rejecting SQL produced by Access 2010 Query Designer (Windows 7, 32 bit)

[DrChocolate]

Greetings: I run a referral nonprofit (mostly for support groups) and have constructed a db in Access. Now I need to take the data in the db and convert it into HTML for our new website.

There are 102 categories; each support group fits into one or more categories; when a client requests information, he/she is sent to the groups in one category. I have constructed a query so that I can put together a list of paragraphs for each category showing details about each group in that category. It operates by iterating through each row and concatenating paragraphs for each group in that category. Unfortunately, when I try to open the recordset for that query and make the strSQL the same as the SQL produced by the query designer, I get a vague error message requesting a label, statement, or end of statement. For starters here's the DAO approach with the SQL --

Private Sub cmdDesignSearchResultsHTML_Click()

' This sub iterates qryWebAllCatsTheirGroupsTheirPublicContacts for which groups belong in each category and which contacts belong to each group to develop the HTML for
' the search results page of the web site.

' Bring in an array of all categories 3 digit numbers to use in the loop

Dim CatNumberList(1 To 200) As Integer
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT tblCategories.OurNumber, tblCategories.ID_Category, tblCategories.NameInAccess FROM tblCategories ORDER BY tblCategories.OurNumber"

Set rs = db.OpenRecordset(strSQL)

' Check for no categories found in list in categories -- just here to prevent program from crashing in weird situation

If Not rs.EOF Then
    rs.MoveFirst
    Else: MsgBox "No category numbers found -- very strange. Please investigate.", vbOKOnly + vbCritical, "Something's very wrong!"
        Exit Sub
End If

' Loop through SQL from qrySortCatOurNumberbyOurNumber to assign each 3 digit category number to the CatNumberList array

rec = 0

Do While Not rs.EOF

    rec = rec + 1
    CatNumberList(rec) = rs!OurNumber
    
    rs.MoveNext

Loop
 
' Close everything for a reiteration
rs.Close
Set rs = Nothing
Set db = Nothing
rec = 0

' SQL from the qryWeballCatsTheirGroupsTheirPublicContacts copied here from design wizard's SQL page -- something's rotten in Denmark.

SELECT tblCategories.ID_Category, tblCategories.OurNumber, tblGroup_Contact_Join.Status, tblGroup_Contact_Join.GroupID, tblGroups.DisplayPosition, tblGroups.GroupName, tblGroups.Description, tblContacts.ID_Contacts, tblContacts.LastName, tblContacts.FirstName, tblContacts.Status, tblContactPhonePublic.Prelude, tblContactPhonePublic.PhoneNum, tblContactPhonePublic.PhoneExt, tblContactPhonePublic.Notes, tblContacts.ConfEmail, tblGroups.WSW_URL
FROM (tblGroups INNER JOIN (tblCategories INNER JOIN tblGroup_Category_Join ON tblCategories.ID_Category = tblGroup_Category_Join.CategoryID) ON tblGroups.ID_Group = tblGroup_Category_Join.GroupID) INNER JOIN ((tblContacts INNER JOIN tblContactPhonePublic ON tblContacts.ID_Contacts = tblContactPhonePublic.RefNum) INNER JOIN tblGroup_Contact_Join ON tblContacts.ID_Contacts = tblGroup_Contact_Join.ContactID) ON tblGroups.ID_Group = tblGroup_Contact_Join.GroupID
WHERE (((tblGroup_Contact_Join.Status)<>"Inactive"))
ORDER BY tblCategories.OurNumber, tblGroups.DisplayPosition DESC , tblGroups.GroupName;

.

I did some obvious things (running it first rather than second, changing the " to ' or "") but to no avail. Alas.

I'm new here and couldn't find a way to post a jpg screen shot of the query design, but when I plugged this SQL into a blank SQL screen for Access, poof, it worked perfectly and backwardsly created the same query structure I put forth in the designer. Hmmm...

Thank you for kind consideration -- and any suggestions where I have unfortunately and unintentionally broken protocol.

Dr.C.

[pbaldy]

Where does it error exactly, and what's the error? I don't see any relationship between the SQL for the recordset and the other SQL you posted. The recordset SQL looks pretty basic, and shouldn't error. You should be able to attach a picture by going into the "Go Advanced" area and then the "Manage Attachments" button.

[DrChocolate]

OK, here's the story. Once upon a time, about 30 minutes ago, I hear this knock-knock-knock on our front door. Realizing that the Big Bad Wolf doesn't hang in our neighborhood, I opened the door to find 3 guys in helmets, plain brown uniforms, and an ampersand on their pockets. Oh yeah, they all had pocket protectors, too. They announced themselves very politely -- they were the dreaded "Concatenation Police". They said that they had heard rumors that I had improperly concatenated an SQL statement in VBA; they showed me that there were two ways to concatenate:

1) total = piece1 & piece2 & piece3 OR
2) total= total & piece1
total= total & piece2
total = total & piece3

Actually, I received your reply saying that you felt that SQL code was OK; that increased my confidence and pointed me toward my manner of expressing it being a problem. Your question about where the error appeared also pushed me in that direction -- the whole SQL statement lit up bright red pointing to a problem with the entire code structure rather than error with a parenthesis or something like that.

Just the before the CP arrived, I happened to be talking with a friend/programmer and he got sick of my whining and suggested that VBA doesn't seem to like method #1, but does like #2. When I changed the format to #2, the code compiled and functioned perfectly. I can now iterate that query to my hearts content.

Fortunately, the CP said that because this was my first offense I was going to be let off with just a warning. But I better be careful in the future.

Thanks for keeping me out of jail! Problem solved! I better go now -- I feel the Dragnet theme coming on.

DrC

P.S. Is there a way to mark the thread as problem solved (but obviously keep it open for others to comment or ask questions)?

[pbaldy]

LOL! That was truly funny.

That said, I'm not clear on the problem. You don't do any concatenation in the posted code, unless I've gone blind. I'm also not sure I understand or believe the concatenation police. This:

    strSQL = "SELECT account_name, dispatch_contact_email, SendEmailStatements, AddExcelToEmail, PrintAndEmail, account_code " _
           & "FROM tbl_customers " _
           & "WHERE account_code = '" & rsInvoices!customer_code & "'"

would be more efficient than this (I've tested):

strSQL = "SELECT account_name, dispatch_contact_email, SendEmailStatements, AddExcelToEmail, PrintAndEmail, account_code "
strSQL = strSQL & "FROM tbl_customers " 
strSQL = strSQL & "WHERE account_code = '" & rsInvoices!customer_code & "'"

I always use the former method, though I suppose some people might find the second easier to read or write. You can mark the thread solved using "Thread Tools" near the top of the page.

[DrChocolate]

Paul -- Although I have no medical information which permits me to make a useful assessment of your visual acuity, I think I wuzn't too clear: the "concatenation" I'm speaking of is not that of DATA, but of CODE in the long SQL statement from one line to the next.

In that sample of 1 and only 1 SQL statement, I saw that the former method of splitting out the very long SQL code worked using method #2 and not method #1. The only difference I can see in your examples above and my SQL statement is that the first section of mine (from SELECT to FROM) was so long that it had to be "split" to more than one line. All the splitting in your example was at the beginning of a "section" of the SQL statement (i.e. SELECT, FROM, WHERE, ORDER...). Is it possible that somewhere something in SQL or Windows doesn't like line splits in the middle of a SQL statement section? Ah, well.

One event of course doesn't prove a darn thing, but I sure appreciate your help in getting my particular piece of SQL code to run very happily. I'm in the middle of coding the site and your help was crucial in getting rid of a major roadblock. Thanks again. Don't make yourself a stranger. -- Dr.C.

P.S. Think Raleigh.

[pbaldy]

I understood that you meant the SQL in code, but in the example above there is only the one short bit. The SQL at the bottom does not appear to be part of the code. The example I gave was purposely short, but I often have longer SQL that I break up into multiple lines to keep it all on screen at once (so I don't have to scroll right). The code only cares that a valid SQL string is the result of the concatenation, not the method of concatenation or how it's broken up. Here's a longer example from a db I happen to have open:

  strSQL = "SELECT tblRONumbers.*, CAR_RECORDS.Company_no, CAR_RECORDS.loc, Make, Model, Handicap, " _
         & "CAR_RECORDS.type, tblMechanics.MechanicName, CAR_RECORDS.VehicleLocation, " _
         & "Alias.SortOrder, CAR_RECORDS.fuel_type, CAR_RECORDS.CharterBus " _
         & "FROM (tblMechanics RIGHT JOIN (tblRONumbers INNER JOIN CAR_RECORDS " _
         & "ON tblRONumbers.car_no = CAR_RECORDS.car_no) " _
         & "ON tblMechanics.MechanicID = tblRONumbers.Mechanic_no) " _
         & "INNER JOIN (SELECT * FROM tblStatus WHERE StatusType = 'r') AS Alias " _
         & "ON tblRONumbers.ROStatus = Alias.Status " _
         & "WHERE tblRONumbers.CloseDateTime Is Null AND tblRONumbers.[MechBody]= '" & strShop & "'"

As you can see, I break in the middle of all the sections (the code goes on to conditionally add to the WHERE clause depending on user selections).

In any case, I'm glad you got back on track and hope the project proceeds as hoped!

[DrChocolate]

Thanks Paul. I really appreciate your help. All is going well if I can just figure out my flow chart! -- DrC