Date Range Issue with No data
Posted on 2005-05-15
Below is the record source to my report.
SELECT DISTINCTROW tblSewerPermits.TPlantID,
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
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.
(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.