Code Help for Date Range Report (1 Viewer)

[STEVENCV]

Hi,

I used some code I found here: http://allenbrowne.com/casu-08.html to try and run my report based on a date range.

I have modified it only to customise the report name/field name. However, when I run this (with a date range of 01/01/2012 to 30/01/2012) I get an error with this line of code:

strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"

Can anybody help me?


My full code is:

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
    strReport = "Basic Hours Report"      'Put your report name in these quotes.
    strDateField = "[DateofVisit]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If
    
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
    Exit Sub
    
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

 

[jzwp22]

When I first read your post I was concerned about your date format dd/mm/yyyy since SQL always interprets dates as mm/dd/yyyy, but I see Allen took care of that with the strcJetDate. I tried the code myself, and it worked fine. Is the date of visit field name spelled exactly as it is in the report's record source including any spaces or special characters (i.e. Date Of Visit or Date_Of_Visit)?

 

[STEVENCV]

Thank you for the reply. I checked the code and am still unable to get it working.

Initially (in the code supplied above) I put [DateofVisit] which is the actual field in the table.

On the report that this code runs and supposedly filters by date, there is a text box called 'rptdate' that summons the date from the table.

I have tried both of these field names and it still gives an error at the same point.

 

[pr2-eugin]

Not sure if this wil help, but try to add the field name without quotes.. like..

insted of : strDateField = "[DateofVisit]"
TRY: strDateField = [DateofVisit].Value

I believe the [DateofVisit] is the FieldName of the Form and not the Table.

 

[jzwp22]

Are you using a query for the report's record source? If so, could you provide the SQL text of the query? By chance did you give the date of visit field an alias in that query?

 

[STEVENCV]

No, I am not using a query. I am quite new to access and I have not used queries before.

 

[STEVENCV]

Not sure if this wil help, but try to add the field name without quotes.. like..

insted of : strDateField = "[DateofVisit]"
TRY: strDateField = [DateofVisit].Value

I believe the [DateofVisit] is the FieldName of the Form and not the Table.

I tried this but it says it cannot find the field name. I tried [DateofVisit].value (which is the name of the table field) and [rptdate].value and both came back with the same error

 

[pr2-eugin]

Okay are you sure that the Name of the Control i.e. the TextField is [DateOfVisit] not the ComtrolSource right?

Chec the attachment.

 

[STEVENCV]

The textfield Name in the report is "rptdate". It's control source is =[DateofVisit].

The report lists all clients from the Clients Table, alongside all related visits records (one-to-many) from the Visits Table. I want to just show the visits that fall within a date range (or all if I leave the dates blank).

If I leave them blank, it runs the report fine. It's only when I add dates that there is a problem.

 

[jzwp22]

Is there any chance that you could zip & post a copy of the database with any sensitive data removed?

 

[STEVENCV]

I've attached it. Just created 3 records. 2 with visits on them, 1 without. There are dates in January 2012 and February 2012 for testing purposes.

It was created in Access 2010.

Thank you.

 

[jzwp22]

I'm still not sure whether the following will work with your date format since my regional settings automatically put the date in the mm/dd/yyyy format, but it might be worth a shot. It worked for me when I replaced the following:

strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"

with this:

strWhere = strWhere & "(" & strDateField & " < " & Format(DateAdd("d", 1, Me.txtEndDate), strcJetDate) & ")"

 

[STEVENCV]

You are a genius. Thank you very much.

That has worked. At first it didn't recognise the field name, so I changed it to the field name from the table and it's worked like a charm.

For anyone else who visits here with similar problems, my final, working code is:

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
    strReport = "Detailed Hours Report"      'Put your report name in these quotes.
    strDateField = "[DateofVisit]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(DateAdd("d", 1, Me.txtEndDate), strcJetDate) & ")"
    End If
    
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
    Exit Sub
    
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

 

[jzwp22]

You're welcome. I'm glad that worked for you!

My apologies for not telling you that I reset the variable back to the field name in your table.

 

[STEVENCV]

No worries, I'm just glad it's working.

I do have an issue though. My Basic Report used to list each client, and a total of number of visits and total hours.

However, now, it is showing each visit individually.

How do I change it back to show eahc client once with totals?

 

[jzwp22]

Do you want to filter via the dates for the basic report as well? If so, it will probably not work properly because of the query you are using for the report's record source. You are using an aggregate query. I would recommend a non-aggregate query and let the report do the summing and counting.

You can also use Allen Browne's form for both the basic and detailed reports if you provide a combo box with the 2 report names (and adjust the code accordingly). See attached DB.

BTW, I renamed your basic report as Basic Hours Report 2 so that you can see how I changed the query that provides the records for the report.

 

[STEVENCV]

Thank you. I like the report choice section, that is excellent.

My report has definately changed somehow though in all my changes.

Before I started using this code, my basic report looked like this:

It had one row per client, and totalled how many visits there were and the total hours.

I noticed it wasn't showing clients that had no visits, but I managed to fix that.

Now however, when I run the basic report it shows each visit in turn. I get that info on the detailed report, so how do I get the basic report to just show totals?

Also, when there are no dates entered on the form, it should show all records. This is only showing all records from "Visits" and I need it to show all records from "Clients"

 

[jzwp22]

Now however, when I run the basic report it shows each visit in turn. I get that info on the detailed report, so how do I get the basic report to just show totals?

What I did in the database I posted was to rework the query behind the basic hours report. This is the query from the database I posted

SELECT [Crisis Calls Attended].ClientName, [Crisis Calls Attended].ClientAddress, ([totalhoursprovided]*60+[TotalMinutesProvided]) AS TotalMinutes, [Crisis Calls Attended].Salutation, [Crisis Calls Attended].LogNo, Visits.LogNo, Visits.DateofVisit
FROM [Crisis Calls Attended] LEFT JOIN Visits ON [Crisis Calls Attended].LogNo = Visits.LogNo;



I also had to add a grouping level based on the client name to that report. You will have to do the same for your main database; you can use the database I posted as a guide. For the report choice combo box, I just used a value list and altered the code to reference the combo box rather than the report name itself.

Also, when there are no dates entered on the form, it should show all records. This is only showing all records from "Visits" and I need it to show all records from "Clients"

The LEFT JOIN shown in the query above does as you describe.

 

[STEVENCV]

Thank you for the reply.

I have got it showing all clients now regardless of there being any visits.

However, I am still struggling with getting it to group a clients visits together.

The Basic Hours Report should only show each client once. The total visits should count up how many visits that client has had (it is set to count the LogNo field from the Visits table), and in the total hours it should add up the hours from all of the visits (either all records, or records that fall within the date range provided).

Currently, it is showing each visit record per client individually, rather than a total.

The detailed report is supposed to show each visit individually, which it does. The basic report is meant to just be a quick snapshop per client.

 

[jzwp22]

The Basic Hours Report should only show each client once. The total visits should count up how many visits that client has had (it is set to count the LogNo field from the Visits table), and in the total hours it should add up the hours from all of the visits (either all records, or records that fall within the date range provided).

Did you add a group level based on client to the report? If you are using Access 2007 or 2010 & are in design view of the report, click the Group & Sort icon on the ribbon. A new pane will open below the report pane. You would set the group level there. Alternatively, you could import the report from the database I posted into your main database.