Solved

Entering an aggregate report's date range via a form

Posted on 2008-06-10
17
651 Views
Last Modified: 2012-05-05
Hello,

I have an aggregate query (qry_progressivehealthleads) which is linked to a report (Rpt_PHIladder). In order to load, the query requires the user to enter a date range (for the field LeadDate). This is facilitated through the traditional basic Access messagebox/textbox popup.

I would instead like to have the user enter the date range via a form (frmWhatdates). A picture of the form is below.

To trigger the Rpt_PHIladder, a user would enter the date range, then click the button 'ladder'(Com_ladder) located on frmWhatdates.

This button currently has the following code behind it. Is there a way to substitute the date range the user enters into frmWhatdates, with the date range automatically asked by the query?
Private Sub com_ok_Click()
Dim strReport As String     'Name of report to open.
    Dim strField As String      'Name of your date field.
    Dim strWhere As String      'Where condition for OpenReport.
    Const conDateFormat = "\#mm\/dd\/yyyy\#"
 
    strReport = "rpt_PHIMthly"
    strField = "Leaddate"
 
    If IsNull(Me.txtStartDate) Then
        If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
            strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
        End If
    Else
        If IsNull(Me.txtEndDate) Then       'Start date, but no End.
            strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
        Else                                'Both start and end dates.
            strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
                & " And " & Format(Me.txtEndDate, conDateFormat)
        End If
    End If
 
    ' Debug.Print strWhere                  'For debugging purposes only.
    DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Open in new window

dateform.JPG
0
Comment
Question by:Dozingquinn
  • 9
  • 8
17 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 21757289
If you are using a Where clause inthe Openreport command you don't need any paramaters in the report's query.
0
 

Author Comment

by:Dozingquinn
ID: 21757430
Hi Peter,

If I remove the date range query parameters (i.e the criteria), the query decides to delete the LeadDate field. This means that LeadDate can't be referenced in the query. Unless I'm doing something wrong?
WITH DATE CRITERIA:
 
SELECT Count(L.PHIconsultant) AS CountOfPHIconsultant, L.PHIconsultant, P.PHIconsultantteam
FROM tbl_leaddetails AS LD INNER JOIN (tbl_THC_team AS T INNER JOIN (tbl_PHI_team AS P INNER JOIN tbl_lead AS L ON P.ID = L.PHIconsultant) ON T.ID = L.THCconsultant) ON LD.LeadNumber = L.Leadnumber
WHERE (((L.LeadDate) Between [enter start date] And [enter end date]))
GROUP BY L.PHIconsultant, P.PHIconsultantteam;
 
WITHOUT DATE CRITERIA
 
SELECT Count(L.PHIconsultant) AS CountOfPHIconsultant, L.PHIconsultant, P.PHIconsultantteam
FROM tbl_leaddetails AS LD INNER JOIN (tbl_THC_team AS T INNER JOIN (tbl_PHI_team AS P INNER JOIN tbl_lead AS L ON P.ID = L.PHIconsultant) ON T.ID = L.THCconsultant) ON LD.LeadNumber = L.Leadnumber
GROUP BY L.PHIconsultant, P.PHIconsultantteam;

Open in new window

qryparameters.JPG
0
 
LVL 77

Expert Comment

by:peter57r
ID: 21757573
LeadDate is not part of the query results in either case so it can't be referenced by the Openreport command.

Change the criteria in your query to:

Between forms!formnamehere!txtStartdate and forms!formnamehere!txtenddate
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Dozingquinn
ID: 21765059
Hi Peter,

Under which field should I add this criteria?
Between [forms]![frmWhatdates]![txtStartdate] And [forms]![frmwhatdates]![txtenddate]
0
 
LVL 77

Expert Comment

by:peter57r
ID: 21776365
Replace the existing criteria.
0
 

Author Comment

by:Dozingquinn
ID: 21776414
Hi Peter,

I have replaced the existing LeadDate criteria (pic shown below). However when I try to run the report that the query is linked to I get the message "Enter Parameter value for tbl_lead.LeadDate"

The report does not include the LeadDate field
leaddate.JPG
0
 
LVL 77

Expert Comment

by:peter57r
ID: 21776434
Please post the SQL view of your query.
0
 

Author Comment

by:Dozingquinn
ID: 21776444
Here Peter -
SELECT Count(L.PHIconsultant) AS CountOfPHIconsultant, L.PHIconsultant, P.PHIconsultantteam
FROM tbl_leaddetails AS LD INNER JOIN (tbl_THC_team AS T INNER JOIN (tbl_PHI_team AS P INNER JOIN tbl_lead AS L ON P.ID = L.PHIconsultant) ON T.ID = L.THCconsultant) ON LD.LeadNumber = L.Leadnumber
WHERE (((L.LeadDate) Between [forms]![frmWhatdates]![txtStartdate] And [forms]![frmwhatdates]![txtenddate]))
GROUP BY L.PHIconsultant, P.PHIconsultantteam
HAVING (((Count(L.PHIconsultant)) Between [forms]![frmWhatdates]![txtStartdate] And [forms]![frmwhatdates]![txtenddate]));

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 21776521
Either remove the criteria from the PHIConsultant column -  it doesn't make any sense to say that a count must be between two date values- or change it to something meaningful if you require it.
0
 

Author Comment

by:Dozingquinn
ID: 21800245
Hi Peter,

Apologies about the delay. I believe the SQL code I pasted in was incorrect (sorry!). Below is the correct SQL. The comments I posted above (featuring the picture) still stand.
SELECT Count(L.PHIconsultant) AS CountOfPHIconsultant, L.PHIconsultant, P.PHIconsultantteam
FROM tbl_leaddetails AS LD INNER JOIN (tbl_THC_team AS T INNER JOIN (tbl_PHI_team AS P INNER JOIN tbl_lead AS L ON P.ID = L.PHIconsultant) ON T.ID = L.THCconsultant) ON LD.LeadNumber = L.Leadnumber
WHERE (((L.LeadDate) Between [enter start date] And [enter end date]))
GROUP BY L.PHIconsultant, P.PHIconsultantteam;

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 21829231
WHERE (((L.LeadDate) Between [enter start date] And [enter end date]))

Should be

WHERE (((L.LeadDate) Between [forms]![frmWhatdates]![txtStartdate] And [forms]![frmwhatdates]![txtenddate]))

0
 

Author Comment

by:Dozingquinn
ID: 21843570
Hi Peter,

I've made the alteration to the SQL. Note: The [leaddate] field is unchecked in the query.

When I go to frmWhatdates and enter the required date range, I get the error "Enter Parameter Value - leaddate"

My new SQL is:
SELECT Count(L.PHIconsultant) AS CountOfPHIconsultant, L.PHIconsultant, P.PHIconsultantteam
FROM tbl_leaddetails AS LD INNER JOIN (tbl_THC_team AS T INNER JOIN (tbl_PHI_team AS P INNER JOIN tbl_lead AS L ON P.ID=L.PHIconsultant) ON T.ID=L.THCconsultant) ON LD.LeadNumber=L.Leadnumber
WHERE (((L.LeadDate) Between [forms]![frmWhatdates]![txtStartdate] And [forms]![frmwhatdates]![txtenddate]))
GROUP BY L.PHIconsultant, P.PHIconsultantteam;

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 21844183
I can't see any reason why you should get this parameter prompt.
( I assume you are actually being asked for L.LeadDate)

Are you getting this message when you run the query or only when you run your report?
0
 

Author Comment

by:Dozingquinn
ID: 21851210
Hi Peter,

When I run the query directly, it asks me for the "forms!frmwhatdates!txtstartdate" and "forms!frmwhatdates!txtenddate"

When I run the report through the form, it asks me for "leaddate" (not L.LeadDate). To be honest I don't even know what the "L." in "Leaddate" is - as the field is named "leaddate" and is actually from the table "tbl_lead"
0
 
LVL 77

Accepted Solution

by:
peter57r earned 400 total points
ID: 21852744
The problem in the report is nothing to do with the query.

You need to look at the sorting and grouping settings and fix those by reselecting the fields from the drop down list (even if they look right already).
0
 

Author Comment

by:Dozingquinn
ID: 21852838
Hi Peter,

We're nearly there - I can feel it!

I've re-added the fields to the query and the report, so everything's fresh again. My new SQL below (which shows the new table names)

After I completed this I was still coming across the same parameter error, however I noticed something interesting. It seems that the phrase "Leaddate Between #04/15/2008# And #08/23/2008#" is a default value in the report's Data>Filter section. Once I delete it, all records are shown.

However when I return to design view, the phrase reappears again.
SELECT Count(tbl_lead.PHIconsultant) AS CountOfPHIconsultant, tbl_lead.PHIconsultant, tbl_PHI_team.PHIconsultantteam
FROM (tbl_PHI_team INNER JOIN tbl_lead ON tbl_PHI_team.ID = tbl_lead.PHIconsultant) INNER JOIN tbl_leaddetails ON tbl_lead.leadnumber = tbl_leaddetails.LeadNumber
WHERE (((tbl_lead.LeadDate) Between [forms]![frmWhatdates]![txtStartdate] And [forms]![frmwhatdates]![txtenddate]))
GROUP BY tbl_lead.PHIconsultant, tbl_PHI_team.PHIconsultantteam;

Open in new window

0
 

Author Comment

by:Dozingquinn
ID: 21852906
Hi Peter,

I just realised that the filter entry "Leaddate Between #04/15/2008# And #08/23/2008#" is based on the dates I select on the form frmwhatdates. Silly me for not noticing.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access query expression 6 22
SQL Group on First occurrence 9 26
Opening forms with an input box 5 22
Dcount help 2 17
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question