SQL in Reports Problem

[shelby55]

Hi

I am using Access 2003.

I have a report with various graphs on it. The report itself is unbound but each graph has an SQL statement for it.

There is also a graph dialog box for the user to enter the information required for the entire report and it is called frmGraphDialog.

For the graphs for total days disposition the code is:

SELECT DateSerial(Year([DisDate]),Month([DisDate]),1) AS MonthEnd, Avg(FLO_Data.TotalDays) AS ALOS
FROM FLO_Data
WHERE (((FLO_Data.InstName)=[forms]![frmGraphDialog]![lstInst2]))
GROUP BY DateSerial(Year([DisDate]),Month([DisDate]),1)
HAVING (((DateSerial(Year([DisDate]),Month([DisDate]),1))>=[forms]![frmGraphDialog]![txtStartDateA] And (DateSerial(Year([DisDate]),Month([DisDate]),1))<=[forms]![frmGraphDialog]![txtEndDateB]))
ORDER BY DateSerial(Year([DisDate]),Month([DisDate]),1);

However, the above shows all months in the database and doesn't just select the dates noted on the graph dialog box.

The next issue is that for another graph it isn't selecting the initial timeframe as a data point. If I double click on the datasheet I can see where there is nothing beside the initial data point but all subsequent ones are 1, 2, 3 etc. The SQL for that graph is:

SELECT qryBedTurnsB.MonthEnd, IIf(qryBedTurnsB!Unit In ("OS Medicine","OS Surgery"),[Separations]/[Beds],[BedsTurned]) AS BedTurns
FROM qryBedTurnsB LEFT JOIN tblOS_BedTurns ON (qryBedTurnsB.MonthEnd = tblOS_BedTurns.Month) AND (qryBedTurnsB.Unit = tblOS_BedTurns.Unit)
WHERE (((qryBedTurnsB.MonthEnd) Between [forms]![frmGraphDialog]![txtStartDateA] And [forms]![frmGraphDialog]![txtEndDateB]) AND ((qryBedTurnsB.Unit)=[forms]![frmGraphDialog]![lstInst2]));

Hopefully someone can advise what's wrong? Thanks.

 

[shelby55]

Hi

I changed the SQL statement to select on the actual date for start to end on from the report dialog box. When I run the query created by my SQL statement I see the correct number of months that would exist from the start and end date entry in the dialog box. However, the first month is not showing up. I changed the axis so all months showed up but nothing for initial month.

Can anyone explain why this would be happening? Thanks.