Opening a report based on a date range

[Jason]

Newb question:
I've got a report that I want to filter by date based on
user input. Here is the code that I wrote, which I am
sure is missing something, as it doesn't work:

Private Sub Report_Open(Cancel As Integer)
Dim dteStartDate As Date
Dim dteEndDate As Date
dteStartDate = InputBox("Enter start date of report as
mm/dd/yyyy", "Start Date")
dteEndDate = InputBox("Enter end date as
mm/dd/yyyy", "End Date")
DoCmd.ApplyFilter , dteStartDate < txtInjuryDate <
dteEndDate
End Sub

This generates the prompts to ask for the dates, but then
gives the error "You entered an expression that has no
value"

Any help would be much appreciated.

 

[Stephan Kassanke]

Newb question:
I've got a report that I want to filter by date based on
user input. Here is the code that I wrote, which I am
sure is missing something, as it doesn't work:

Private Sub Report_Open(Cancel As Integer)
Dim dteStartDate As Date
Dim dteEndDate As Date
dteStartDate = InputBox("Enter start date of report as
mm/dd/yyyy", "Start Date")
dteEndDate = InputBox("Enter end date as
mm/dd/yyyy", "End Date")
DoCmd.ApplyFilter , dteStartDate < txtInjuryDate <
dteEndDate
End Sub

This generates the prompts to ask for the dates, but then
gives the error "You entered an expression that has no
value"

Any help would be much appreciated.

Jason,

split the filter condition and combine them with 'AND'


dteStartDate < txtInjuryDate
AND
txtInjuryDate < dteEndDate

instead of dteStartDate < txtInjuryDate < dteEndDate

HTH,
Stephan

 

[Phil Hunt]

or if you prefer:

txtInjuryDate between dteStartDate and dteEndDate

 

[Tim Ferguson]

DoCmd.ApplyFilter , _
dteStartDate < txtInjuryDate < dteEndDate

First of all, this is good maths notation, but no good in basic
programming. As noted elsewhere, you need two boolean comparisons:

dteStartDate < txtInjuryDate AND txtInjuryDate < dteEndDate

Actually, this is not good enough either, because you are comparing text
strings that are not sorted on anything sensible (i.e. alphabetic). I am
really worried about this txtInjuryDate -- is this really a Text-type
database field containing a date? How is this formatted? Another problem is
that there is no error trapping on the user input. And you are using less-
thans, when you shold be using less-than-or-equals. Finally, and most
basic, is the fact that the database knows nothing of any of the variables
you are throwing at it: you have to pass the values not the identifiers.
And if you send values, you have to quote them properly. And the parameter
is a string, not an expression.

So we have:

strStartDate = InputBox(...)
strEndDate = InputBox(...)

' catch user input
if isdate(strStartDate) then
dteStartDate = CDate(strStartDate)
else
Stop ' or do something useful, like go round again
endif

if isdate(strEndDate) then
dteEndDate = CDate(strEndDate)
else
Stop ' ditto
endif

' build the Criterion
' use international date formats, but you can use USian
' ones if you prefer.
' We have to use CDate to convert the txtInjuryDate to a
' date value in order to get chronological comparisons
' rather than alphabetic ones
'
strWhere = "(" & _
Format$(dteStartDate, "\#yyyy\-mm\-dd\#") & "<= " & _
"CDate(txtInjuryDate))"

strWhere = strWhere & -
" AND (CDate(txtInjuryDate) <= " & _
Format(dteEndDate, "\#yyyy\-mm\-dd\#") & ")"

' remove this once you know it's working right
MsgBox strWhere

' okay, now apply filter
DoCmd.ApplyFilter strWhere



Hope that helps


Tim F

 

[Guest]

Wow, thank you very much for that detailed explanation.
In answer to your question, txtInjuryDate represents a
textbox who's control source is Injury Date from a table
formatted as mm/dd/yyyy.

Thanks
Jason

 

[Tim Ferguson]

In answer to your question, txtInjuryDate represents a
textbox who's control source is Injury Date from a table
formatted as mm/dd/yyyy.

In that case, you want to do the comparison on the InjuryDate field (which
is different for each record) rather than the txtInjuryDate control (which
will stay the same until you edit the form) shirley...

Tim F