Input dates not passed to query and resultant report

  • Thread starter virtualkeeper via AccessMonster.com
  • Start date
V

virtualkeeper via AccessMonster.com

Hi guys,

I've got a stickler of a problem. I'm trying to get a data within a date
range output to a form and I need the dates collected from a query/queries
and passed through to a form/forms.

Why isn't this working?

Private Function GetReportCondition(ByVal ReportID As String) As String
Dim sRet As String
Dim sCondition As String
Dim sFrom As String
Dim sTo As String
Dim asFrom() As String
Dim asTo() As String
Dim i As Long

If Not IsNull(txtFrom.Value) Then
txtFrom.SetFocus
sFrom = txtFrom.Value
asFrom = Split(sFrom, "/")
End If
If Not IsNull(txtTo.Value) Then
txtTo.SetFocus
sTo = txtTo.Value
asTo = Split(sTo, "/")
End If


If sFrom <> "" And sTo <> "" Then
If CDate(sTo) < CDate(sFrom) Then
MsgBox "Date To must be greater or equal to Date From"
GetReportCondition = "-1"
Exit Function
End If
Select Case ReportID
Case 1, 2, 4, 5, 6, 9, 12, 19 ' Daily
If sFrom <> sTo Then
sCondition = "Date Between #" & sFrom & "# And #" & sTo &
"#"
Else
sCondition = "Date = #" & sFrom & "#"
End If
Case 7, 15, 16 ' monthly
sCondition = "(Year=" & asFrom(2) & " And Month>=" & asFrom(0)
& ")"
sCondition = sCondition & " Or (Year = " & asTo(2) & " And
Month<=" & asTo(0) & ")"
For i = 1 To asTo(2) - asFrom(2) - 1
sCondition = sCondition & " Or Year = " & asFrom(2) + i
Next
Case 3, 8, 11, 13, 14, 17, 20 ' yearly
If asFrom(2) <> asTo(2) Then
sCondition = "Year between " & asFrom(2) & " And " & asTo
(2)
Else
sCondition = "Year = " & asFrom(2)
End If
Case Else

End Select
End If
GetReportCondition = sCondition
End Function
 
Top