Opening a report for choosen date range

[krisleech]

I have a button which opens a report and filters the report to show only records between a date range which the user chooses by typing a date into two edit boxes called "start date" and "end date". The VB for the button is:

Dim stDocName As String
Dim stAcView As String
Dim strWhere As String
    
If [print options] = 1 Then stAcView = acViewNormal Else stAcView = acViewPreview
    
strWhere = "[Date] Between #" & [start date] & "# And #" & [end date] & "#"
 
stDocName = [report name]
    
DoCmd.OpenReport stDocName, stAcView, , strWhere

But when the report opens it shows all records, is there something else i need to do to the report or query it is based on to make the filter / WHERE clause work.

Thanks in advance KRis.

 

[Calvin]

Hi Kris,

Try using the Start Date and End Date text box names:

strWhere = "[Date] Between #" & me.txtStartDate & "# And #" & me.txtEndDate & "#"

 

[KelMcc]

There may be a better/easier way, as I've learned access backwards (from a user perspective, so I don't know code)... But I had the same problem. Using a lot of help from these forums, instead of vB code, I used a query:

SELECT table.field
FROM table
WHERE ((table.datefield) Between [Forms]![Formname]![From Date] And [Forms]![Formname]![To Date])

Then, have the report use the query as its control source. My query had a bunch of addtional stuff in it, so I "simplified" it for you and used generic table/field/form names. "From date" and "to date" I hope are self-explanatory.

Let me know if this helps.

 

[Pat Hartman]

Your constant is not correct. I don't know what "stAcView" is but it is not valid in this context.

DoCmd.OpenReport stDocName, acViewPreview, , strWhere

 

[krisleech]

Report <- Date range <- User

Thanks for the responses, I tried your method first Calvin and the result was exactly the same as my previous method. When the report opens all the records are displayed.
If I then put the report in design mode and look at the filter property it has the correct SQL in eg. ([Date] Between #01/06/2003# And #20/06/2003#)
But the Filter is Off, maybe this is the problem, but how do I turn the filter On from a Form button?

Kel - I would try this but I don't want to change the query, I want the query parameters to be set from the form button for other reasons. It was self-explanatory though

Pat - StAcView is a String which I set to either "acViewNormal" or "acViewPreview" depending on the users choice of Print or Previewing the report. It works fine.

Many thanks all, frustrated, Kris.

 

[PaddyIrishMan]

[YourReportName].FilterOn = True

Should turn on the Report Filter

HTH,
Patrick

 

[krisleech]

Paddy - Would turning the filter on be the same as leaving the Filter property turned On, manually turning the filter on and saving the report?

Ive noticed that if i manually filter the report it does not work as expected:

For example if i filter the report to show all records for just May i get only records from May (Correct). Now if i filter for just records for June, i get May and Junes records (incorrect). If i then filter for just July, i get May, June and July's records. etc.

Its almost as if instead of showing records Between two dates that it is showing all records upto the second date.

?? Kris ??

 

[Rich]

strWhere = "[Date] Between #" & Format([start date],"mm/dd/yyyy") & "# And #" & Format([end date],"mm/dd/yyyy") & "#"

 

[krisleech]

dd/mm/yyyy > mm/dd/yyyy

That works Rich!

But why, im assuming the Format re-arranges the users date from dd/mm/yyyy to mm/dd/yyyy, but how come that works since when i use dates in queries i use dd/mm/yyyy and that works fine!
I am in the UK, so i would have thought Windows uses a dd/mm/yyyy format and so should Access, or am i way of the mark?


Kris.

 

[Pat Hartman]

VBA and SQL assume that dates in string formats are m, d, y order unless some other sequence is obvious. For example - 13/4/3 will be interpreted as 13 Mar, 2003. But 12/4/3 will be interpreted as 4 Dec, 2003. Whenever a date is stored in a date data type field of course there is no problem. So the places to be ware are date prompts and hard-coded dates in VBA.

Make sure during your testing, you always test with a date that Access may misinterpret.

I wish one of the UK users would post a synopsis of how to handle dates so that Access interprets them properly. Touch on things like the windows default setting and what that does/does not affect, prompts (does specifically declaring their data type have any impact?), data entry fields (bound and unbound), etc. Then we could all reference it. How about it Rich?

BTW, krisleech
Date is the name of the date function. If you are using it as a column name, you'll run into many problems because Access will at times interpret Date as today's date function and sometimes as your table column. Best to change the name and in the future, as well as avoiding embedded spaces and special characters, steer clear of reserved words when naming your objects.

 

[krisleech]

dd/mm/yyyy or mm/dd/yyyy - dont leave it up to Access to decide

That explains my problem! I like to know why something is happening, not just a solution, thanks Pat.

Moral of the story: If you are getting a date as user input (unbound textbox or prompt) be explicit with specifying format ie. dd/mm or mm/dd, as the format is open to interpretation by Access.

I will also be taking your advice about changing the date fieldname, i should have spotted that myself.

Kris.

 

[Rich]

Krisleech, you only have to specify the US format when comparing/returning dates from strings.
Pat I'll try and put something together, we really do need that FAQ.
Jon!!!!!!!!

 

[misscrf]

Hi, I was reading this post, and I am having the same trouble. I am using a select case with option groups on a form. The first option group on the form passes a variable to the button on the form, to choose what report to open. In the report open, I am trying this code to select from the option group, what date option to choose. 1 is today (me.reportdatefield = Date) 2 is choose a date (form date field)
3 is date range, and 4 is all. It is 3, the date range, that I cannot seem to get right.

Here is my latest attempt:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open

Dim MyFromChoose As Date
Dim MyTo As Date
MyFromChoose = [Forms]![frmReports]![txtFromChoose]
MyTo = [Forms]![frmReports]![txtTo]

Select Case Forms![frmReports]![GrpReportDate]
Case 1
Me.ActivityDate = Date

Case 2
Me.ActivityDate = MyFromChoose

Case 3

Me.ActivityDate Between(MyFromChoose And MyTo)
  
Case 4
Me.ActivityDate = "*"

End Select

Exit_Report_Open:
    Exit Sub

Err_Report_Open:
    MsgBox Err.Description
    Resume Exit_Report_Open
End Sub

Here are some other attempts that I have made at Case 3, that all give me errors:

Me.ActivityDate  >=  Format(Forms![frmReports]![txtFromChoose], "mm/dd/yyyy") And <= Format(Forms![frmReports]![txtTo], "mm/dd/yyyy") 
-------------

"Me.ActivityDate >= #" _
 &     Forms![frmReports]![txtFromChoose] & "#" AND "Me.ActivityDate <= #" _
 &     Forms![frmReports]![txtTo] & "#"
--------------
"Me.ActivityDate >= #" _  & Forms![frmReports]![txtFromChoose] & "#" AND  <= #" _ &   Forms![frmReports]![txtTo] & "#"
-------------
"Me.ActivityDate Between #" & Format(Forms![frmReports]![txtFromChoose],"mm/dd/yyyy") & "# And #" & Format(Forms![frmReports]![txtTo],"mm/dd/yyyy") & "#"
----------------

"Me.ActivityDate >= #" & Forms![frmReports]![txtFromChoose] & "# AND Me.ActivityDate <= #" & Forms![frmReports]![txtTo] & "#"
--------------------

Me.ActivityDate Between & Format(Forms![frmReports]![txtFromChoose], "mm/dd/yyyy") & "# And #" & Format(Forms![frmReports]![txtTo], "mm/dd/yyyy") & "#"

Can anyone please help?

Thanks.