syntax error

[ronda]

The error is occurring on RsSQL_1 line
GROUP is highlighted and the error is "Expected end of statement"

I'm sure it's a simple thing - I just can't find it...
Any suggestions?



Private Sub Command7_Click()
Dim DB As DAO.Database, Rs1 As DAO.Recordset, Rs2 As DAO.Recordset
Dim i As Integer, j As Integer
Dim RsSQL_1 As String
Dim RsSQL_2 As String
Dim CurrentValue As Variant
Dim CurrentField As Variant
Dim Workbook As Object
Dim xlApp As Object
Dim AllBDMs As Object
Dim OldBDMs As Object

Set DB = DBEngine.Workspaces(0).Databases(0)

RsSQL_1 = "SELECT qryConsolidateDivisionsCalendarTest.[CONUM], Count(qryConsolidateDivisionsCalendarTest.BDMORDER) AS CountOfBDMORDER, qryConsolidateDivisionsCalendarTest.CONAME, qryConsolidateDivisionsCalendarTest.BDMORDER FROM qryConsolidateDivisionsCalendarTest WHERE POSTINGWEDATE BETWEEN #" & ([Forms]![frmExcelMTDAllDepts]![StartDate]) & "#" And ([Forms]![frmExcelMTDAllDepts]![EndDate]) & "#" GROUP BY qryConsolidateDivisionsCalendarTest.CONUM, qryConsolidateDivisionsCalendarTest.CONAME, qryConsolidateDivisionsCalendarTest.BDMORDER;"
Set Rs1 = DB.OpenRecordset(RsSQL_1, dbOpenDynaset)

 

[dcx693]

Try changing this line:
"#" And ([Forms]![frmExcelMTDAllDepts]![EndDate]) & "#" GROUP BY
to:
"# And #" & ([Forms]![frmExcelMTDAllDepts]![EndDate]) & "# GROUP BY etc...

Consider using the VBA _ line continuation character to make your code more readable. For example:

strSQL="SELECT * FROM tblEmployees " & _
     "WHERE [LastName]='Johnson';"

 

[ronda]

thanks very much-worked like a charm!!!