Report from query

[hotrod1952 via AccessMonster.com]

I have a report which uses results from a query of my database. Both the
query and report use data(DUE DATE) that I have set up to input when I run
the report. All works well with the report as long as the query has data to
return. Sometimes however the query will not find records past the Due date.
When this occures the header on the report returns as error. The report
header is a textbox as follows:

"OVERDUE BY: " & [DUE DATE mm/dd/yyyy] & " IN -------------- AREA"

The SQL for the query is:

SELECT WO.WOID, Asset.AssetID, WO.DateRequired, EMPLOYEE.Name, WODef.WODefID
FROM ((WO INNER JOIN Asset ON WO.AssetNo = Asset.AssetNo) INNER JOIN EMPLOYEE
ON WO.AssignedToEmployee = EMPLOYEE.ID) INNER JOIN WODef ON WO.WODefNo =
WODef.WODefNo
WHERE (((WO.WOID) Like "pm*") AND ((WO.DateRequired)<DateAdd("d",1,[DUE DATE
mm/dd/yyyy])) AND ((WO.WOStatusNo)<>5) AND ((EMPLOYEE.ID)="1035" Or (EMPLOYEE.
ID)="1303" Or (EMPLOYEE.ID)="1054" Or (EMPLOYEE.ID)="1155" Or (EMPLOYEE.ID)
="1079"))
ORDER BY WO.DateRequired

What I would like the report to return when the query returns no results is a
text header like :

"NO OVERDUE TO REPORT IN ------------AREA BY: " & [DUE DATE mm/dd/yyyy]

and the existing text header when there are results to return.

Any Ideas?

 

[Rick Brandt]

I have a report which uses results from a query of my database. Both
the
query and report use data(DUE DATE) that I have set up to input when
I run the report. All works well with the report as long as the query
has data to return. Sometimes however the query will not find records
past the Due date. When this occures the header on the report returns
as error. The report header is a textbox as follows:

"OVERDUE BY: " & [DUE DATE mm/dd/yyyy] & " IN -------------- AREA"

The SQL for the query is:

SELECT WO.WOID, Asset.AssetID, WO.DateRequired, EMPLOYEE.Name,
WODef.WODefID FROM ((WO INNER JOIN Asset ON WO.AssetNo =
Asset.AssetNo) INNER JOIN EMPLOYEE ON WO.AssignedToEmployee =
EMPLOYEE.ID) INNER JOIN WODef ON WO.WODefNo = WODef.WODefNo
WHERE (((WO.WOID) Like "pm*") AND
((WO.DateRequired)<DateAdd("d",1,[DUE DATE mm/dd/yyyy])) AND
((WO.WOStatusNo)<>5) AND ((EMPLOYEE.ID)="1035" Or (EMPLOYEE.
ID)="1303" Or (EMPLOYEE.ID)="1054" Or (EMPLOYEE.ID)="1155" Or
(EMPLOYEE.ID) ="1079"))
ORDER BY WO.DateRequired

What I would like the report to return when the query returns no
results is a text header like :

"NO OVERDUE TO REPORT IN ------------AREA BY: " & [DUE DATE
mm/dd/yyyy]

and the existing text header when there are results to return.

Any Ideas?

What most people do in these cases is use the NoData event of the report to
display a Message Box and then cancel the opening of the report.

MsgBox "No Data Meeting Your Criteria"
Cancel = True

If you are opening the report from code (like a button on a form) then the
code behind that button will need to trap errors and ignore error number
2501 (raised when the report is cancelled).