Problem with sql

[oatsybob]

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!

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

[Bob Fitz]

What is the data type of the field called "Organization ID"

[oatsybob]

Integer. It's linked to a field in another table whose data type is also integer.

[Bob Fitz]

I think the single speach marks (') are only required for text so you could try:
"WHERE Flyers.[Organization ID]=" & selected
However, I think there may be something else wrong here.
Is "List30" a multi-select list box?

[oatsybob]

Yes, it's extended multi-select. I tried removing the single speech marks and am getting the same error...

[oatsybob]

But having said that, there will only ever be one item selected for this application. I use the same list box for a variety of things, which is why it's multi-select.

[Bob Fitz]

I have not tested this but you could try this:
Replace:

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

With

For Each var30 In .List30.ItemsSelected
    selected = selected & "[Organization ID]=" & .List30.Column(0, var30) & " OR "
Next var30

If Len(selected) > 0 Then
    selected = Left(selected,Len(selected) - 4)
End If

sfields = "SELECT Flyers.[Flyer ID], Flyers.[Organizations ID], Flyers.FlyDate, Flyers.FlyCount FROM Flyers ORDER BY Flyers.FlyDate " & _
          "WHERE " & selected 
End With

If that doesn't work, can you post a copy of your db.

[oatsybob]

It didn't work, so here's the database. It's a bit of a mess atm, so I apologize for that in advance.

When it opens, you should see this:



Click on "outreach" and you'll see the listbox. Select DIA (only) from the list, and click flyer analytics.



There's only one button on the form, and it's the click action that the code I posted is associated with. Thanks so much for taking a look!

[Bob Fitz]

The good news is that I found two errors with the SELECT statement and have corrected them in the attached db.
1) The ORDER BY has to be after the WHERE not before it.
2) There was a spelling mistake in the name of one of the fields in the statement (it had an "s" on the end of Flyers.[Organizations ID], )

The bad news is that you appear to have another problem at the following line:
ReDim slopes(0 To iSlopes - 1)
I have placed the following comment before it in the code:
'NEXT LINE CAUSES ERROR

You may be able to correct this yourself.

[oatsybob]

Awesome! Thanks so much for the help, I figured out the second problem