Interesting Report Problem

[nigeve]

Morning Gentlemen. I have the following code in my report. All I want to do is show the dates the report is filtered on in the report header. These are the text boxes txtStart and txtEnd. The title is “List of jobs between txtStartDate and txtEndDate”. Or of the user doesn’t select a date then the caption just reads “List of IT helpdesk jobs”. Works great when the report is previewed (DoCmd.OpenReport rptName, acViewPreview, , wherecondition:="[Date Open]Between #" & StartDate & "# And #" & EndDate & "#") But when I use acViewNormal the text boxes are blank.
(Dates are selected from the form frmReports which is a listbox full of report names with two text boxes txtStartDate and txtEndDate)
Is my code in the wrong place..?

Private Sub Report_Activate()
On Error GoTo Error_Report_Activate
Dim strStart As String
Dim strEnd As String
Dim f As Form
If FIsLoaded("frmReports") Then
Set f = Forms!frmReports
If Not IsNull(f!txtStartDate) And Not IsNull(f!txtEndDate) Then
strStart = f!txtStartDate
strEnd = f!txtEndDate

Me.txtStart = strStart
Me.txtEnd = strEnd
Else
Me.txtRptTitle.Caption = "IT Helpdesk Jobs"
Me.txtAnd.Caption = ""

End If
End If
Exit_Report_Activate:
Exit Sub
Error_Report_Activate:
MsgBox Err.Description
Resume Exit_Report_Activate
End Sub
I hope this makes sense..?

 

[namliam]

In your textboxes are dates entered in the US date format of MM/DD/YYYY?

 

[nigeve]

Hi namliam, the data is entered as dd/mm/yyyy and the report seems to be returning the correct number of records, so the wherecondition is working. The report is bound to a query. It displays perfectly fine in preview, just not when I use DoCmd.OpenReport rptName, acViewNormal, , _
wherecondition:="[Date Open]Between #" & StartDate & "# And #" & EndDate & "#"

 

[namliam]

Are you always using the same dates?
For example in DD/MM/YYYY a date like 24/01/2014 will still work, while 12/01/2014 will break.
Month 24 doesnt exist therefor access will correctly interpret it to be DD/MM/YYYY.
While month 12 does exist and access will "fall" to its native MM/DD/YYYY format. making it not 12 Jan but Dec 1, causing your recordset to fail.

Personally I never use such a syntax, more like:

DoCmd.OpenReport rptName, acViewNormal, , "[Date Open]Between #" & StartDate & "# And #" & EndDate & "#"

 

[namliam]

Hmz, may not have read the OP close enough :/

I usually put code like this in the On Open event not On Activate, perhaps this is worth an attempt?

 

[nigeve]

namliam, you were heading in the right direction. Unbeknownst to me the report was not even filtering correctly because it was interpreting my entries as American format.
I used the following code and I'm now getting the data I expect in the reports.
DoCmd.OpenReport rptName, acViewPreview, , "[Date Open]Between CDATE('" & StartDate & " 00:00:00') And CDATE('" & EndDate & " 00:00:00')"
Still not showing dates on printing but moving in the right direction

 

[namliam]

No need for the cdate function or the time part in there... the CDate will still interpret your DD/MM/YYYY in the wrong way, you still need to force your startdate to the US format or be sure it is entered by the user that way.

I mentioned earlier trying the ON OPEN event, perhaps you can try creating the textboxes as bound to your form directly or passing the discriminent values to the OPENARGS part and pick them up from there.
Is the problem that your code isnt reading your values or is the problem it isnt writing them?

 

[nigeve]

Ok, I’m posting this in the interests of thoroughness. As I stated in my introduction I’ve been away from development for a few years (teaching Maths) which would explain these schoolboy errors. The code has been moved from the Report_Activation to the ReportHeader_Format event. The code now prints as well as previews. For some reason when the code was in the Report_Activation event it was running only when the report previewed and not when it printed…

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim strStart As String
Dim strEnd As String
Dim f As Form

If FIsLoaded("frmReports") Then
Set f = Forms!frmReports
strStart = f!txtStartDate
strEnd = f!txtEndDate
Me.txtStart = strStart
Me.txtEnd = strEnd
End If

End Sub

 

[nigeve]

And the code that opens the report is...

rptName = Me.lstReport
DoCmd.OpenReport rptName, acViewNormal, , _
"[Date Open]Between CDATE('" & Me.txtStartDate & "') And CDATE('" & Me.txtEndDate & "')"

Using CDATE ensures the correct dates are pulled from the underlying table