?
Solved

Date Range Issue with No data

Posted on 2005-05-15
19
Medium Priority
?
393 Views
Last Modified: 2012-08-14
Below is the record source to my report.

SELECT DISTINCTROW tblSewerPermits.TPlantID,
Sum(IIf([SConnTypeID]="NC" And
IsNull([PermitNo])=False,1,0)) AS NCCount,
Sum([SanFlow]/4) AS [San Flow], [San Flow]*0.65 AS
SAAverage, Sum(IIf([SConnTypeID]="PL" And
IsNull([PermitNo])=False,1,0)) AS PLCount,
Sum(tblSewerPermits.PlugFlow) AS SumOfPlugFlow,
Sum([PlugFlow]*0.65) AS PLAverage, ([San
Flow]*0.65)-(Sum([PlugFlow]*0.65)) AS [Net Flow] FROM
tblSewerPermits WHERE
(((tblSewerPermits.SewerTypeID)="SA" Or
(tblSewerPermits.SewerTypeID)="CM") And
((tblSewerPermits.PermitEDate)>=forms![Report Date
Range]!BeginDate And
(tblSewerPermits.PermitEDate)<=Forms![Report Date
Range]!EndDate)) GROUP BY tblSewerPermits.TPlantID
HAVING (((tblSewerPermits.TPlantID) Is Not Null));


Overview: The user enters BeginDate and EndDate on a form and on click of a button, this report opens up.

Problem:
(a). If the user enters a date between 1st Jan 2005 and 9th Dec 2005, the report shows
     accurate but misleading figures. This is because there is no data after 31st Mar 2005.

If data is not available in the table of my records for the dates in question. User must get a message that "his request is out of range"

Users should get a report ONLY if he is withing the range of data. How can I solve this  misleading problem?

Any help will be appreciated.

0
Comment
Question by:billcute
  • 10
  • 4
  • 3
  • +1
19 Comments
 
LVL 5

Accepted Solution

by:
BevinManian earned 2000 total points
ID: 14005759
You've got two issues here.
1. Data no within the date range

2. Data within date range, but misleading date header in the report
Correct me if i am wrong...
You have data until 31-Mar-05.  But the user has keyed in 31-Aug-05 as the ending date parameter.  The query will surely retrieve data - no doubt about that coz, the records will fall between
1-Jan-05  to 31-Aug-05


For (1.) you can use...
If Get_Record_Count(Me.BeginDate, Me.EndDate) = 0 Then
            MsgBox "Nothing to Print!"
        Else
openthe report
endif

My last post has got this.

For (2.), you might give a footer note or something like that.

I can write something out for u if this is the direction you want to be heading....

Bev
0
 
LVL 4

Author Comment

by:billcute
ID: 14005975
Bev,
(1). I have got data until 31-Mar-05.  But the user has keyed in 31-Aug-05 as the ending
      date parameter, or,

(2). You have data from Febrauary 2005 until 31-Mar-05.  But the user has keyed in 1st
      January 05 as the ending date parameter.

The query for the two examples should give an error message that user is "out of Range".

- because if the "BeginDate" and the "EndDate did not fall between and so should give  a message not in range" to user..

Similarly, if the "BeginDate' falls within the date but the EndDate" falls outside range of the data, then reject the query and give message "out of Range".

Again if the Begin Date is outside the data range but the "EndDate" is within the data for Selim - You still have to reject the query and give message "out of Range" to user.

Regards,
Bill
0
 
LVL 5

Expert Comment

by:BevinManian
ID: 14006015
What if there is data until 31-Mar-05...

The user has keyed in end date as 15-Apr-05

How can this be "out of range"......?

Bev
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:BevinManian
ID: 14006019
Or even better..

What if you have data until 27-Mar-05
and the user has keyed in 31-Mar-05


For sure, this cant be out of range, can it?

Bev
0
 
LVL 4

Author Comment

by:billcute
ID: 14006036
I think we can add some limitation for "Plus" or "minus" of three business days.
0
 
LVL 5

Expert Comment

by:squirrl
ID: 14006046
Bill,

Does the method you describe work with aggregate queries?  I have used it a good deal myself but the queries do not contain GROUP BY.

squirrl.
0
 
LVL 4

Author Comment

by:billcute
ID: 14006065
squirrl
I am not familiar with "aggregate queries" but the only SQL required in the report is provided above.
0
 
LVL 5

Expert Comment

by:squirrl
ID: 14006144
My SQL never was very good.

squirrl.
0
 
LVL 4

Author Comment

by:billcute
ID: 14006204
Squirri,
Any suggestion?
0
 
LVL 5

Expert Comment

by:squirrl
ID: 14006260
Just thinking about it.  If I had a data source and a couple of records...
0
 
LVL 4

Author Comment

by:billcute
ID: 14006331
Okay, I'll work on it.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 14006919
The SQL is logically correct.  Why not put in a Min(tblSewerPermits.PermitEDate) and Max(tblSewerPermits.PermitEDate) so the user will see the actual range of the data within his/her posted start and end dates.
0
 
LVL 4

Author Comment

by:billcute
ID: 14006932
Ray,
Your input will be equally appreciated. Can you post your idea?

0
 
LVL 44

Expert Comment

by:GRayL
ID: 14006977
Add the two fields to the end of the SELECT statement:

SELECT DISTINCTROW
tblSewerPermits.TPlantID,
Sum(IIf([SConnTypeID]="NC" And IsNull([PermitNo])=False,1,0)) AS NCCount,
Sum([SanFlow]/4) AS [San Flow],
[San Flow]*0.65 AS SAAverage,
Sum(IIf([SConnTypeID]="PL" And IsNull([PermitNo])=False,1,0)) AS PLCount,
Sum(tblSewerPermits.PlugFlow) AS SumOfPlugFlow,
Sum([PlugFlow]*0.65) AS PLAverage,
([San Flow]*0.65)-(Sum([PlugFlow]*0.65)) AS [Net Flow],
Min(tblSewerPermits.PermitEDate) As MinPermitEDate,  
Max(tblSewerPermits.PermitEDate) As MaxPermitEdate
FROM
tblSewerPermits WHERE
(((tblSewerPermits.SewerTypeID)="SA" Or
(tblSewerPermits.SewerTypeID)="CM") And
((tblSewerPermits.PermitEDate)>=forms![Report Date Range]!BeginDate And
(tblSewerPermits.PermitEDate)<=Forms![Report Date Range]!EndDate))
GROUP BY tblSewerPermits.TPlantID
HAVING (((tblSewerPermits.TPlantID) Is Not Null));

0
 
LVL 4

Author Comment

by:billcute
ID: 14007269
GrayL,
I did try your suggested expression, I didnt notice any difference in the output of my report after the addition compared to my original SQL output report.

Could please provide me with further info as to the expected result with the additional SQL expression below:

Min(tblSewerPermits.PermitEDate) and Max(tblSewerPermits.PermitEDate)
0
 
LVL 5

Expert Comment

by:BevinManian
ID: 14009160
Bill,
I worked out a function for you...

Rules for Date Range Entry in the form:
1. Beginning date must be less than ending date
2. Beginning date must be equal to or greater than the earliest date in the table where
   SewerTypeID='SA' Or SewerTypeID='CM' AND
   TPlantID Is Not Null
3. Ending date can be maximum 3 days greater than the latest date in the table for the above conditions.

Note that dcount, dlookup, dsum, dmax, dmin have all been eliminated...


The below code will be on ur form's OnClick property of the button.
****************
If Get_Record_Count(Me.BeginDate, Me.EndDate) = 0 Then
            MsgBox "Nothing to Print!"
        Else
          If Get_Report_Date_Conflict(Me.BeginDate, "BEGIN") < 0 And Not Me.input_year.Visible Then
                MsgBox "The Beginning Date is out of range with the data!"
          Else
            If Get_Report_Date_Conflict(Me.EndDate, "END") > 3 And Not Me.input_year.Visible Then
                  MsgBox "The Ending Date is out of range with the data!"
            Else
                If Me.PrintDecide = 1 Then
                    DoCmd.OpenReport "rptQuarters", acViewPreview
                Else
                    DoCmd.OpenReport "rptQuarters", acViewNormal
                End If
            End If
          End If
 End If


The 1st function to get record count
****
Public Function Get_Record_Count(bd As Date, ed As Date) As Integer

Dim rst As DAO.Recordset
Dim sSql As String
Dim ctr As Integer

ctr = 0
sSql = "SELECT DISTINCTROW TPlantID, Sum(IIf([SConnTypeID]='NC' And IsNull([PermitNo])=False,1,0)) " & _
       "AS NCCount, Sum([SanFlow]/4) AS [San Flow], [San Flow]*0.65 AS SAAverage, " & _
       "Sum(IIf([SConnTypeID]='PL' And IsNull([PermitNo])=False,1,0)) AS PLCount, " & _
       "Sum(PlugFlow) AS SumOfPlugFlow, Sum([PlugFlow]*0.65) AS PLAverage, " & _
       "([San Flow]*0.65)-(Sum([PlugFlow]*0.65)) AS [Net Flow] FROM tblSewerPermits " & _
       "WHERE (SewerTypeID='SA' Or SewerTypeID='CM') And " & _
       "PermitEDate>=#" & Trim(Str(bd)) & "# And PermitEDate<=#" & Trim(Str(ed)) & "# " & _
       "GROUP BY TPlantID HAVING TPlantID Is Not Null;"

Set rst = CurrentDb.OpenRecordset(sSql)
Do While Not rst.EOF
    ctr = ctr + 1
    If ctr > 0 Then Exit Do
rst.MoveNext
Loop

Get_Record_Count = ctr

End Function

The 2nd function to get check the date ranges individually.
****
Public Function Get_Report_Date_Conflict(dt As Date, dttype As String) As Integer
Dim rst As DAO.Recordset
Dim sSql As String
Dim footnotedate As Date

sSql = "SELECT PermitEDate FROM tblSewerPermits " & _
       "WHERE (SewerTypeID='SA' Or SewerTypeID='CM') And " & _
       "(TPlantID Is Not Null) AND (PermitEDate Is Not Null) " & _
       "ORDER BY PermitEDate DESC;"

Set rst = CurrentDb.OpenRecordset(sSql)

If dttype = "END" Then
    If Not rst.EOF Then footnotedate = rst!PermitEDate
Else
    rst.MoveLast
    footnotedate = rst!PermitEDate
End If


Get_Report_Date_Conflict = DateDiff("d", footnotedate, dt)

End Function


Bev
0
 
LVL 4

Author Comment

by:billcute
ID: 14009219
Bev,
Wow !! I just tested it, it works great. Thanks for the extra effort.

Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 14009224
To All,
I want to thank you all for your efforts. Bev's codes works great for me the way I wanted it. I appreciate all your efforts.

Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 14009228
Bev,
Sorry, the credit is for your code dated 05/16/2005 07:08AM EDT. - an error on my part.

cheers
Bill
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

621 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