Form with date range and status lookup

[dan231]

I have a form that pulls classes from a date range. This works good as is, but I need to stop it from pulling canceled classes.

Canceled classes can have a status of 200, 220 or 240

The existing code is this:

    strRecordSource = "SELECT * FROM qryBaModDailyAttendanceSheetsMenu " & _
  " WHERE ([StartDate] Between #" & txtDateRangeStart & "# AND #" & txtDateRangeEnd & "#) OR " & _
  "([EndDate] Between #" & txtDateRangeStart & "# AND #" & txtDateRangeEnd & "#) OR " & _
  "(#" & txtDateRangeStart & "# Between [StartDate] AND [EndDate]) OR " & _
  "(#" & txtDateRangeEnd & "# Between [StartDate] AND [EndDate])"

I think this added to the end will filter out the canceled classes.

AND WHERE ([Status] <> " 200 " OR " 220 " OR " 240 ")"

Any guidance on this would be much appreciated.

 

[gemma-the-husky]

no its AND's you need, not ORs

AND WHERE ([Status] <> " 200 " AND Status <> " 220 " AND Status <> " 240 ")"

(is status text, or a number)


i think you can use NOT as an operator, so you may be able to use one of these

you may be able to say this - not sure if IN is a valid operator

AND WHERE NOT [Status] in (" 200 ", " 220 ", " 240 ")


or

AND WHERE NOT [Status] between " 200 " and " 240 "

 

[dan231]

Status is a number.

Thank you for the info.

 

[dan231]

AND WHERE ([Status] <> " 200 " AND Status <> " 220 " AND Status <> " 240 ")"
I am getting a compile error on the " 200 "

Expected: End of Statement

I played around with the syntax and still couldn't get around this error

 

[gemma-the-husky]

if its a number, you dont need the quote marks - just

AND WHERE ([Status] <> 200 AND Status <> 220 AND Status <> 240)

you have to be careful with nested "" marks, which is probably giving you your problem. You can interchange signle and dble quotes, so

EITHER

"where status = 'ABC' "
'where status = "ABC" '

or dble, dble quotes are treated a single quotes so

"where status = ""ABC"" "

 

[dan231]

That did it!

Thank you both so much. I have to wait until after hours to implement and test.

 

[dan231]

OK, that didn't work. Most likely as the listed code is for a hidden button that isn't linked to the outcome.

As far as I can tell the code that generates the class listing is in a subform and here is the code:

Private Sub Form_Load()
    Dim datCurrentDate As Date
    Dim strRowSource As String
    Dim i As Integer
    Dim intDOW As Integer
    Dim strDow(7) As String
    Dim strDay As String
    
    intNextDate = 0
    intLastDate = cboGetDate.ListCount
    
    
    EstablishUserSecurity
    
    strDow(1) = "(Sun)"
    strDow(2) = "(Mon)"
    strDow(3) = "(Tue)"
    strDow(4) = "(Wed)"
    strDow(5) = "(Thu)"
    strDow(6) = "(Fri)"
    strDow(7) = "(Sat)"
    
    strRowSource = ""
    If IsLoaded("frmBaSubMain") Then
        datCurrentDate = Date - 14
        txtDateRangeStart.Value = datCurrentDate
    End If
    
    If IsLoaded("frmBaModClass") Then
        txtCurrentDate = [Forms]![frmBaModClass]![txtStartDate]
        datCurrentDate = txtCurrentDate - 14
        txtDateRangeStart.Value = datCurrentDate
    End If
    
''''datCurrentDate = DATE - 14
''''txtDateRangeStart.Value = datCurrentDate
    
    For i = 0 To 28
        intDOW = DatePart("w", datCurrentDate)
        strDay = " " & strDow(intDOW)
        strRowSource = strRowSource & datCurrentDate & "; " & datCurrentDate & strDay & ";"
        
        datCurrentDate = datCurrentDate + 1
        intDOW = DatePart("w", datCurrentDate)
        
        If intDOW = 1 Then
            datCurrentDate = datCurrentDate + 1
        End If
    Next i
    
    txtDateRangeEnd.Value = datCurrentDate - 1
    cboGetDate.RowSource = strRowSource
    cboGetDate.Requery
    cboGetDate.Value = Date
    Me.Requery
    
End Sub

It looks to me like this is the right place to add the exceptions for the canceled classes, but I'm not sure where as this code is different from the SELECT stmts I am used to.

Any suggestions on where to start with this? Is this even the right code section?