Hey guys, I can't figure out what's wrong with this sub and could use a little help. I get an error here: "Set r = CurrentDb.OpenRecordset(sfields)". The error is "Syntax error (missing operator) in query expression 'Flyers.FlyDate WHERE Flyers.[Organization ID]='53''. Any thoughts why this might be happening?
Thanks in advance!
Code:Private Sub Command8_Click() On Error GoTo Err_Command8_Click Dim sfields As String Dim slopes() As Double Dim sMed As Double Dim r As Recordset Dim i As Integer Dim iSlope As Long Dim iSlopes As Long Dim n0 As Long ' # of brochures after you leave a new batch as the site Dim j As Integer Dim nrow As Long Dim rc As Variant Dim d As Date Dim dd As Date Dim d0 As Date Dim dp As Date Dim np As Double Dim c As Double Dim cc As Double Dim var As Variant Dim dates As Date Dim brochures As Long Dim selected As String Dim var30 As Variant With Forms!media For Each var30 In .List30.ItemsSelected selected = .List30.Column(0, var30) Next var30 sfields = "SELECT Flyers.[Flyer ID], Flyers.[Organizations ID], Flyers.FlyDate, Flyers.FlyCount FROM Flyers ORDER BY Flyers.FlyDate " & _ "WHERE Flyers.[Organization ID]='" & selected & "'" End With ' this should work for a single flyer batch Set r = CurrentDb.OpenRecordset(sfields) var = r.RecordCount rc = r.GetRows(var) nrow = (UBound(rc, 2)) + 1 iSlopes = fact(nrow) / (fact(nrow - 2) * 2) ReDim slopes(0 To iSlopes - 1) iSlope = 0 n0 = rc(2, 0) d0 = rc(1, 0) 'ReDim dates(0 To nrow - 1) 'ReDim brochures(0 To nrow - 1) For i = 0 To nrow - 2 d = rc(1, i) c = rc(2, i) For j = i + 1 To nrow - 1 dd = rc(1, j) cc = rc(2, j) If (dd <> d) Then slopes(iSlope) = (cc - c) / (dd - d) Else slopes(iSlope) = 0 End If iSlope = iSlope + 1 Next j Next i sMed = median(slopes) dp = d0 - n0 / sMed np = n0 + sMed * (dp - d0) For i = 0 To nrow - 1 'dates(i) = rc(1, i) dates = CDate(Me.Flydate) brochures = n0 + sMed * (dates - d0) Next i Erase slopes Me.Text1 = dp Me.Text3 = brochures 'interquartile range 'type lower case Exit_Command8_Click: Exit Sub Err_Command8_Click: MsgBox Err.Description Resume Exit_Command8_Click End Sub