Dates in Report

[Lori]

I have a database for administrators to enter events that
happen to our servers. They enter the date the event
started, ended, and the server name. I then have a query
that will list ALL servers, and the date of the events.
This means that if no event happened to the server, the
date field is empty.

When running the report, the administrator enters the date
span they would like for the report. Say it covers 6
months. The query behind the report has a field that sums
minutes between start and end. On the report I am
grouping the sums by months. But if no events happened to
the server, that month (of course) is skipped on the
report. I would like to have the report show ALL months,
and if no event happened to that server, it would show 0
minutes as the sum.

In the query, on the sum minutes field, I already have it
set so that MinutesDown: IIf(IsNull([Minutes]),0,[Minutes])
But since the report uses the date as criteria, I can't
get the month with "0" minutes to show, because the query
uses the administrators date span to pull the data.

 

[Marshall Barton]

I have a database for administrators to enter events that
happen to our servers. They enter the date the event
started, ended, and the server name. I then have a query
that will list ALL servers, and the date of the events.
This means that if no event happened to the server, the
date field is empty.

When running the report, the administrator enters the date
span they would like for the report. Say it covers 6
months. The query behind the report has a field that sums
minutes between start and end. On the report I am
grouping the sums by months. But if no events happened to
the server, that month (of course) is skipped on the
report. I would like to have the report show ALL months,
and if no event happened to that server, it would show 0
minutes as the sum.

In the query, on the sum minutes field, I already have it
set so that MinutesDown: IIf(IsNull([Minutes]),0,[Minutes])
But since the report uses the date as criteria, I can't
get the month with "0" minutes to show, because the query
uses the administrators date span to pull the data.

Create a table named tblMonthNums with one integer field
named Num and add rows with 1 through 12 in the field.

Then modify the the report's original query to use an outer
join to the new table.

SELECT . . .
FROM sometable RIGHT JOIN tblMonthNums
ON Month(sometable.datefield) =tblMonthNums.Num
WHERE . . .

Now the report will get a record of Null values when there
is no data for the month.

 

[Guest]

Thanks for your help, I tried that. It tells me that is not a good join. Here is the SQL code for my query

SELECT tblServerAndSource.SvrName, tblServerAndSource.HardOrSoft, qryConcantonateServerType.Start, qryConcantonateServerType.End, tblReasonDown.ReasonDown, qryConcantonateServerType.Comments, IIf(IsNull([PercentUpTime]),1,[PercentUpTime]) AS UpTime, IIf(IsNull([Minutes]),0,[Minutes]) AS MinutesDow
FROM (tblReasonDown RIGHT JOIN (tblServerAndSource LEFT JOIN qryConcantonateServerType ON tblServerAndSource.txtServerType = qryConcantonateServerType.ServerType) ON tblReasonDown.ReasonCode = qryConcantonateServerType.Reason)
WHERE (((qryConcantonateServerType.Start) Between [forms]![frmServerAvailabilityReport]![txtBeginSpan] And [forms]![frmServerAvailabilityReport]![txtEndSpan] Or (qryConcantonateServerType.Start) Is Null))

Since the date field for any server with 0 minutes is blank, when I run the report based on the query, it cannot put those records in the report because the date field is blank. Is there some way to make the query or report realize the date span the user entered for blank date fields and have those months listed in the report with 0 minutes

----- Marshall Barton wrote: ----

Lori wrote

I have a database for administrators to enter events that
happen to our servers. They enter the date the event
started, ended, and the server name. I then have a query
that will list ALL servers, and the date of the events.
This means that if no event happened to the server, the
date field is empty

When running the report, the administrator enters the date

span they would like for the report. Say it covers 6
months. The query behind the report has a field that sums
minutes between start and end. On the report I am
grouping the sums by months. But if no events happened to
the server, that month (of course) is skipped on the
report. I would like to have the report show ALL months,
and if no event happened to that server, it would show 0
minutes as the sum

In the query, on the sum minutes field, I already have it

set so that MinutesDown: IIf(IsNull([Minutes]),0,[Minutes]
But since the report uses the date as criteria, I can't
get the month with "0" minutes to show, because the query
uses the administrators date span to pull the data.

Create a table named tblMonthNums with one integer fiel
named Num and add rows with 1 through 12 in the field

Then modify the the report's original query to use an oute
join to the new table

SELECT . .
FROM sometable RIGHT JOIN tblMonthNum
ON Month(sometable.datefield) =tblMonthNums.Nu
WHERE . .

Now the report will get a record of Null values when ther
is no data for the month

 

[Marshall Barton]

Getting the Join to work a matter of getting the syntax
straight, but it wouldn't matter anyway because the Where
clause would eliminate those records.

How about creating a new query that joins the MonthNums
table to your existing query. That would get the filtered
results of you query and then add the null filled rows.
--
Marsh
MVP [MS Access]

Thanks for your help, I tried that. It tells me that is not a good join. Here is the SQL code for my query.

SELECT tblServerAndSource.SvrName, tblServerAndSource.HardOrSoft, qryConcantonateServerType.Start, qryConcantonateServerType.End, tblReasonDown.ReasonDown, qryConcantonateServerType.Comments, IIf(IsNull([PercentUpTime]),1,[PercentUpTime]) AS UpTime, IIf(IsNull([Minutes]),0,[Minutes]) AS MinutesDown
FROM (tblReasonDown RIGHT JOIN (tblServerAndSource LEFT JOIN qryConcantonateServerType ON tblServerAndSource.txtServerType = qryConcantonateServerType.ServerType) ON tblReasonDown.ReasonCode = qryConcantonateServerType.Reason)
WHERE (((qryConcantonateServerType.Start) Between [forms]![frmServerAvailabilityReport]![txtBeginSpan] And [forms]![frmServerAvailabilityReport]![txtEndSpan] Or (qryConcantonateServerType.Start) Is Null));


Since the date field for any server with 0 minutes is blank, when I run the report based on the query, it cannot put those records in the report because the date field is blank. Is there some way to make the query or report realize the date span the user entered for blank date fields and have those months listed in the report with 0 minutes?

----- Marshall Barton wrote: -----

I have a database for administrators to enter events that
happen to our servers. They enter the date the event
started, ended, and the server name. I then have a query
that will list ALL servers, and the date of the events.
This means that if no event happened to the server, the
date field is empty.

When running the report, the administrator enters the date

span they would like for the report. Say it covers 6
months. The query behind the report has a field that sums
minutes between start and end. On the report I am
grouping the sums by months. But if no events happened to
the server, that month (of course) is skipped on the
report. I would like to have the report show ALL months,
and if no event happened to that server, it would show 0
minutes as the sum.

In the query, on the sum minutes field, I already have it

set so that MinutesDown: IIf(IsNull([Minutes]),0,[Minutes])
But since the report uses the date as criteria, I can't
get the month with "0" minutes to show, because the query
uses the administrators date span to pull the data.

Create a table named tblMonthNums with one integer field
named Num and add rows with 1 through 12 in the field.

Then modify the the report's original query to use an outer
join to the new table.

SELECT . . .
FROM sometable RIGHT JOIN tblMonthNums
ON Month(sometable.datefield) =tblMonthNums.Num
WHERE . . .

Now the report will get a record of Null values when there
is no data for the month.