Report/Query Error

[Underpaidadmin]

I have a monthly report that uses the data from several queries to calculate
the data used on the report. Those several queries are all added into one
query which is the actual record source for the report. When the report runs
it asks the users to enter the begining date of the month and the ending date
of the month. The report uses the end date to create a title using
'=Format([Enter end date],"mmmm yyyy")' and outputs a title such as "Security
Report Feburary 2006". It also uses these two dates in all the queries to
select the relevant data from multiple tables. My problem occurs when any of
the select queries result in no data, all the titles which were formated by
date give #Error values and the report data is blank. I can run the queries
individually and get results but not together. Currently the report's query
selects all * from all the other queries is this incorrect? and if so how
else can this be done?

 

[Underpaidadmin]

After a bit of sleep I thought it might be appropriate to post the SQL of the
record source of the report:

SELECT [1Investigations].*, [2Recovered].*, [3Recovered(YTD)].*, [4Charge
Offs].*, [5Charge Offs(YTD)].*, [6Legal].*,
[1InvestigationstblSecRep1].[ytd#], [1InvestigationstblSecRep1].[ytd$]
FROM 1Investigations, 2Recovered, [3Recovered(YTD)], [4Charge Offs],
[5Charge Offs(YTD)], 6Legal, 1InvestigationstblSecRep1;

This query evalutates to blank when any of the fields return null values.
Can I include all these fields on the report and NOT have this happen?