Between Dates Problem

[Tyler08]

As my report opens, this code runs:

Private Sub Report_Open(Cancel As Integer)

sdate = InputBox("Please Enter Shift Report Start Date", "Report Start Date")
edate = InputBox("Please Enter Shift Report End Date", "Report End Date")

If IsDate(sdate) = True And IsDate(edate) Then
Me.Filter = "[QryShifts].[ShiftDate] Between #" & Format(sdate, "dd/mm/yy") & "# And #" & Format(edate, "dd/mm/yy") & "#"
Else
Me.Filter = ""
End If

End Sub

The sdate and edate work ok as the input dates are displayed in the report header but the results returned from the query are not limited to the dates, all the data records in the query are displayed in the report.

Can anyone tell me where my error is?

Thanks

Tyler

 

[namliam]

Dates in filters and queries must be in American (mm/dd/yyyy) format.
Doing it in european (dd/mm/yyyy) format will cause "strange" behaviour.

 

[Tyler08]

Cheers Mailman.

I thought the American date format was the problem and changed it to (mm/dd/yy). The problem existed before this change. I'll change it back.

Is there anything else I can look at?

Tyler

 

[Kempes]

Hi,

Am I right in thinking that you are calling this report from a form?

Filtering on a form merely acts as a view filter, so although what is displayed on a form looks corrects, the underlying query/table will still have all the records present, hence your report will do the same.

Try this for a starting point.

Copy your query.
Change your control source of the report to look at the copy of the query.
within the query, reflect the above requirements within the criteria section for this field.
save the query then try opening it. It should prompt for dates.
Now add a new button (for testing purposes) that triggers your report from the form and it should prompt you for the dates. (This is the query prompting you, not the vb code, which you shouldn't need).

You can also pass values from the form itself so it automatically shows the relevant information.

Let me know if this makes any sense. If not I will try to explain in more detail.

 

[Kempes]

This should be in the query criteria under your date

Between [Start Date] And [End Date]

Hope this helps

 

[Kempes]

I may have misunderstood how you are doing this.

Having looked closer, you can do it your way which would work. The only thing you are missing is to turn the filter on.

me.filteron = true

add this after adding your filter and you're away.

Don't forgot also the define your variables at the top of the code too.

Dim sdate as Date
Dim edate as Date

 

[namliam]

Inputbox returns a string value therefor the Format doesnt work and the subsequent conversion to a date brakes your query.

Best thing you can do is make 2 date controles on a form and have them be picked up by your query/report.

 

[Kempes]

It doesn't return a string if you define what it is first..

Dim sdate as Date
Dim edate as Date

It works ok on my test db.

 

[namliam]

Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a String containing the contents of the text box.

Predefining your variables is offcourse a good idea in general. BUT inputbox will ALWAYS return a string, so there will always be a conversion from string to date.

The fact that it works "now" doesnt mean it is the right solution in this case. Having Implicit conversions (Getting a string from Inputbox and putting that into a date variable) is "sloppy" at best.

One should not use Inputbox anyway. An inputbox gives you way to little control over the input etc. And you are ALWAYS stuck with a string!

 

[Kempes]

I agree that making the query do the work is probably best practice. I was merely suggesting that, sloppy as it may be, this solution would also work.

 

[Tyler08]

Thanks for your help boys.

I have changed the way in which the Report opens in that the code is no longer used but "Between [Report Start Date] And [Report End Date]" is now in the query Criteria. As a result I get a more robust date entry, ie I can just type 1/4 for 1st April.

The problem now is that sdate and edate were also used to populate a field in the report header to display the report start and end dates. The report runs from a form on the After Update event of a Combo Box in order to limit the report to the selection. I'll add some date controls and try it that way.

Thanks for your help.

Tyler

 

[Kempes]

Just add 2 text boxes in your report header.

set the control source to be

min([datefield])

and the other

max([datefield])

That should sort it

 

[Tyler08]

I now have report dates back in my header!

Thanks for that Kempes

Regards

Tyler