asked on

Date Range Issue with No data

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.

ASKER CERTIFIED SOLUTION

BevinManian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billcute

ASKER

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

BevinManian

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

BevinManian

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

billcute

ASKER

I think we can add some limitation for "Plus" or "minus" of three business days.

squirrl

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.

billcute

ASKER

squirrl
I am not familiar with "aggregate queries" but the only SQL required in the report is provided above.

squirrl

My SQL never was very good.

squirrl.

billcute

ASKER

Squirri,
Any suggestion?

squirrl

Just thinking about it. If I had a data source and a couple of records...

billcute

ASKER

Okay, I'll work on it.

GRayL

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.

billcute

ASKER

Ray,
Your input will be equally appreciated. Can you post your idea?

GRayL

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));

billcute

ASKER

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)

BevinManian

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

billcute

ASKER

Bev,
Wow !! I just tested it, it works great. Thanks for the extra effort.

Bill

billcute

ASKER

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

billcute

ASKER

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

cheers
Bill