Basically the same thing you already did, wouldn't you say?
Probably have two textboxes, txtStartDate and txtEndDate.
And then suppose your dateColumn is called OrderDate
Dim startDate as Date, endDate as Date
If Len(txtstartDate) > 0 or Len(txtEndDate) > 0 Then
On Error Goto InvalidDate
startDate = CDate(txtStartDate)
endDate = CDate(txtEndDate)
InvalidDate: if err.NumBer > 0 Then
MsgBox("Bad date.")
exit sub
end if
strSql = StrSql & " OrderDate >= " & quote & startDate & quote & " Order Date <= " & quote & endDate & Quote & " AND "
On Error Goto 0 'resets it to regular error mode
End if
Something like that, but I didn't test it.
Thank you... we are getting close...
Here is how the code looks like now.
Private Sub Set_Filter_Click()
Dim quote As String
Dim strSql As String
quote = Trim(" ' ") 'single quote
If Filter1 <> "" Then strSql = strSql & " Employee = " & quote & Filter1 & quote & " AND "
If Filter2 <> "" Then strSql = strSql & " Company = " & quote & Filter2 & quote & " AND "
If Filter3 <> "" Then strSql = strSql & " Project = " & quote & Filter3 & quote & " AND "
If Filter4 <> "" Then strSql = strSql & " Task = " & quote & Filter4 & quote & " AND "
Dim startDate As Date, endDate As Date
If Len(txtStartDate) > 0 Or Len(txtEndDate) > 0 Then
On Error GoTo InvalidDate
startDate = CDate(txtStartDate)
endDate = CDate(txtEndDate)
InvalidDate: If Err.Number > 0 Then
MsgBox ("Bad date.")
Exit Sub
End If
strSql = strSql & " Date >= " & quote & startDate & quote & " Date <= " & quote & endDate & quote & " AND "
On Error GoTo 0 'resets it to regular error mode
End If
If Len(strSql) > 5 Then strSql = Left(strSql, Len(strSql) - 5)
Reports![Main_Database_Query].Filter = strSql
Reports![Main_Database_Query].FilterOn = True
When I execute... I get the folowing error:
"Syntax error (missing operator) in querry expression '(Date >='01/10/2008' Date <='01/10/2008')' "