Select case with an And...

[Zygor]

I am trying to figure out the right way to write a case statement.

It should go something like the below. Field(0) is a date time field. I am trying to get a date that is today or yesterday after 5 pm. Any help would be appreciated.


Private Sub Command0_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()

Set rst = db.OpenRecordset("SELECT Table1.* FROM Table1;")
rst.MoveLast
rst.MoveFirst

For Inti = 1 To rst.RecordCount
Select Case rst.Fields(0)
Case Is = Format(rst.Fields(0), "Short Date"), Is > Format(DateAdd("d", -1, Format(rst.Fields(0), "Short Date")), "5:00 pm")
MsgBox "First Case selected" & vbCrLf & rst.Fields(0)
Case Else
MsgBox "Case else selected" & vbCrLf & rst.Fields(0)
End Select
rst.MoveNext
Next Inti
rst.Close
Set rst = Nothing
End Sub

 

[oharab]

Well isn't today after 5pm yesterday anyway, so all you really need it to find everything after then:

Select Case rst.Fields(0)
    Case Is > Date - 1 + TimeSerial(17, 0, 0)
        MsgBox "First Case selected" & vbCrLf & rst.Fields(0)
    Case Else
        MsgBox "Case else selected" & vbCrLf & rst.Fields(0)

End Select

Assuming rst.Fields(0) is a date

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[PWise]

how about

Select Case rst.Fields(0)
    Case Case Date, Is > CDate(CStr(Date - 1) & " 5:00pm")
        MsgBox "First Case selected" & vbCrLf & rst.Fields(0)
    Case Else
        MsgBox "Case else selected" & vbCrLf & rst.Fields(0)
End Select

 

[Zygor]

Excellent! Thank you both!