Missing Operator in Query Expression

[dollylectric]

I've been going crazy trying to figure this out. I keep getting a syntax error (Missing Operator in Query Expression) and I don't know why. This query works perfectly when I run it in Access (and the query is, in fact, as you'll see in the code I'm posting, querying the same Access 2007 database I tested it on). I'm more used to Access VBA than Excel VBA, so this may be due to some difference in the SQL syntax for Excel that I don't know about. Or maybe it's because I used the Access syntax for a paramaterized query? Anyway, here's the code (I've hilighted the SQL statement in red) - any help would be greatly appreciated:

Private Sub Worksheet_Activate()
 
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim row As Integer
Dim col As Integer

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=I:\User Files\AdministrativeSupervisor\" & _
        "HS DC Documents\Quality-Risk Mgmt\Databases\" & _
        "Physician Auditing\Quality_Test.accdb"

sSQL = "SELECT MRR.[Med_Rec#], Concat(Left(Doctor.Last_Name, 1), Left(Doctor.First_Name, 1))" & _
        "MRR.MRR001, MRR.MRR002, MRR.MRR003, MRR.MRR004, MRR.MRR005," & _
        "[MDMRR1-2].MDMRR001, [MDMRR1-2].MDMRR002, [MDMRR3-4].MDMRR003," & _
        "[MDMRR3-4].MDMRR004, MRR.MRR006, MRR.MRR007, MRR.MRR008," & _
        "MRR.MRR009, MRR.MRR010, MRR.MRR011, MRR.MRR012, MRR.MRR013, " & _
        "MRR.MRR014, MDMRR5.MDMRR005, MDMRR6.MDMRR006, MDMRR7.MDMRR007, " & _
        "MDMRR8.MDMRR008, MRR.MRR015, MRR.MRR016, MRR.MRR017, MRR.MRR018, " & _
        "MRR.MRR019, MRR.MRR020, MRR.MRR021, MRR.MRR022, MRR.MRR023, " & _
        "MRR.MRR024, MRR.MRR025, MRR.MRR026, MRR.MRR027, MRR.MRR028, " & _
        "MRR.MRR029, MRR.MRR030, MRR.MRR031, MRR.MRR032, MDMRR9.MDMRR009, " & _
        "MDMRR10.MDMRR010, MRR.MRR033, MRR.MRR034, MRR.MRR035, MRR.MRR036, " & _
        "MRR.MRR037, MRR.MRR038, MRR.MRR039, MRR.MRR040, MRR.MRR041, MRR.MRR042" & _
        "FROM (((((((((Doctor INNER JOIN MRR ON Doctor.Doctor_ID = " & _
        "MRR.Attending) LEFT JOIN MDMRR10 ON MRR.MRR_ID = MDMRR10.MRR_ID) " & _
        "LEFT JOIN [MDMRR1-2] ON MRR.MRR_ID = [MDMRR1-2].MRR_ID) LEFT JOIN " & _
        "[MDMRR3-4] ON MRR.MRR_ID = [MDMRR3-4].MRR_ID) LEFT JOIN MDMRR5 ON " & _
        "MRR.MRR_ID = MDMRR5.MRR_ID) LEFT JOIN MDMRR6 ON MRR.MRR_ID = " & _
        "MDMRR6.MRR_ID) LEFT JOIN MDMRR7 ON MRR.MRR_ID = MDMRR7.MRR_ID) " & _
        "LEFT JOIN MDMRR8 ON MRR.MRR_ID = MDMRR8.MRR_ID) LEFT JOIN MDMRR9 ON " & _
        "MRR.MRR_ID = MDMRR9.MRR_ID)" & _
        "WHERE (((MRR.Date_Reviewed) Between [Please Enter Start Date] " & _
        "And [Please Enter End Date]));"
Set rs = New ADODB.Recordset
rs.Open sSQL, cn

If Not (rs.BOF Or rs.EOF) Then 'If there are no records, this will be false
    rs.MoveFirst
End If

row = 3

Do While Not rs.EOF 'Start looping through the records
    For col = 0 To rs.Fields.Count - 1
    ActiveWorksheet.Cells(row, col + 2).Value = _
    rs.Fields(col).Value
    Next col
    rs.MoveNext
    row = row + 1
Loop
    
'Close recordset and connection
rs.Close
cn.Close
        
'Clean up
Set rs = Nothing
Set cn = Nothing
    
End Sub

[Norie]

I don't know if it's causing the problem but there's no space between Concat(Left(Doctor.Last_Name, 1), Left(Doctor.First_Name, 1)) and MRR.MRR001.

sSQL = "SELECT MRR.[Med_Rec#], Concat(Left(Doctor.Last_Name, 1), Left(Doctor.First_Name, 1))" & _
        "MRR.MRR001, MRR.MRR002, MRR.MRR003, MRR.MRR004, MRR.MRR005,"

If this code works in Access it will work in Excel.

Well the SQL part anyway.

[dollylectric]

Found the problem! There was a missing space before the FROM keyword. Queries this long/complicated at the end of the day are just asking for a headache.

So, thanks, Norie. While your answer didn't exactly pinpoint the problem, it did point me in the right direction: look for missing spaces at the beginning/end of lines.

The parameterized portion doesn't work in Excel though, I get an error telling me that values for the parameters are missing, i.e., Excel is not prompting for the parameter values the way Access would. I'm going to try to set up some cells where a user can input the information then reference those cells in the SQL. Hopefully that will work.

Wish me luck!

[Norie]

Does that code work in Access and ask the user for the parameters?

PS I knew there were other missing spaces, just thought you might enjoy finding them.

[dollylectric]

I've never tried it through VBA, but if you're just creating a query in Access (embedded or stand-alone), you create parameters in the WHERE clause by enclosing the parameter in brackets (you'll get an input box and whatever you put in those brackets is the prompt the user will see in the input box).

I'm guessing that if you do it through VBA you have to do it the old fashioned way and create an input box for user input then pop that input into the query string. I'll test it.

PS: There was another error in my code: it's "ActiveSheet", not "ActiveWorksheet".

[Norie]

Ah, thought you might have been referring to a saved query.

If you do it using VBA/ADO you have to code for the parameters yourself.

You have various options for user input, cells, inputboxes, a small userform perhaps.

Once you have the input you either need to incorporate it in the SQL statement or use the Parameters collection.