Summary Form Code

[Crocdundee]

I am trying to create a summary form in access 2007 and the code I have so far is not working, it stops on the
" Set rs = db.OpenRecordset(myStrSQL, dbOpenDynaset)"

3 combo boxes
1 command button
1 text box
----------
This is a horse sporting database and I am Counting Selections & summing selections and comparing to the results
---------------------
2 Tables
Ratings
Results
-------------------
the text box i have on the form counts the selections filtered by the date field
----------------------------------------
the code is
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim myStrSQL As String
Dim sDateFrom, sDateTo, sDateCondition, sTrack As String
Private Sub RefreshForm()

sDateFrom = cboDateFrom.Value
sDateTo = cboDateTo.Value
sTrack = cboTrack.Value


If sDateFrom = "" Or IsNull(sDateFrom) Then
sDateCondition = ""
ElseIf sDateTo = "" Or IsNull(sDateTo) Then
sDateCondition = " AND Ratings.RcDATE=#" & sDateFrom & "# "
Else
sDateCondition = " AND Ratings.RcDATE>=#" & sDateFrom & "# AND Ratings.RcDATE<=#" & sDateTo & "# "
End If

If sDateCondition = "" Or IsNull(sDateCondition) Then
If sTrack = "" Or IsNull(sTrack) Then
sDateCondition = ""
Else
sDateCondition = " AND Ratings.TRACK = """ & sTrack & """"
End If
Else
If sTrack = "" Or IsNull(sTrack) Then
sDateCondition = sDateCondition
Else
sDateCondition = sDateCondition & " AND Ratings.TRACK = """ & sTrack & """"
End If
End If

'MsgBox (sDateCondition)

sDateFrom = cboDateFrom.Value
sDateTo = cboDateTo.Value
sTrack = cboTrack.Value


If sDateFrom = "" Or IsNull(sDateFrom) Then
sDateCondition = ""
ElseIf sDateTo = "" Or IsNull(sDateTo) Then
sDateCondition = " AND Ratings.RcDATE=#" & sDateFrom & "# "
Else
sDateCondition = " AND Ratings.RcDATE>=#" & sDateFrom & "# AND Ratings.RcDATE<=#" & sDateTo & "# "
End If

If sDateCondition = "" Or IsNull(sDateCondition) Then
If sTrack = "" Or IsNull(sTrack) Then
sDateCondition = ""
Else
sDateCondition = " AND Ratings.TRACK = """ & sTrack & """"
End If
Else
If sTrack = "" Or IsNull(sTrack) Then
sDateCondition = sDateCondition
Else
sDateCondition = sDateCondition & " AND Ratings.TRACK = """ & sTrack & """"
End If
End If

MsgBox (sDateCondition)

'SHOW Tab COUNT SELECTION

myStrSQL = "SELECT Count(ratings.Tab) AS tab_countsel " & _
"FROM ratings " & _
"WHERE 1=1 " & sDateCondition

Set rs = db.OpenRecordset(myStrSQL, dbOpenDynaset)
Text1.Value = rs("Tab_countsel")
Set rs = Nothing
====================================
Any Ideas and help would be very helpfull
Regards
Graham

 

[CharlesChuckieCharles]

if you could post the MyStrSQL that would help

But in the mean time

try using single ' instead of " around str variables

not
AND Ratings.TRACK = """ & sTrack & """"

but
AND Ratings.TRACK = '" & sTrack & "'