Report/Query Error 2

[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 beginning 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 February 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 formatted 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]

Heh after reading what I posted again I realized how vague my subject is, unfortunately I cannot change it so I apologize for my ambiguity.

 

[dhookom]

To solve your #Error issue, use
=IIf([HasData],"Security Report for " & Format ([Enter end date],"mmmm yyyy"),"No Data To Report")

It sounds like you might have bigger issues with your query and why you don't show any records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[underpaidadmin]

Agreed. I too have come to this conclusion but since I don't understand where I am going to post it and hope someone can see something I cannot. The record source for the report is one massive query that I was hoping would combine 9 queries into one. I will post the control source query first:

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;

I am not going to post all of those 'inner' queries SQL just a few since most of them are similar:

Query 1Investigations:

SELECT Count(tblCasesMain.CaseNum) AS CountOfCaseNum, Sum(qryCurrentBalance.CurBalqry) AS SumOfCurBalqry
FROM tblCasesMain LEFT JOIN qryCurrentBalance ON tblCasesMain.CaseNum = qryCurrentBalance.CaseNum
WHERE (((tblCasesMain.CloseDate)>=[Enter the first day of the month] Or (tblCasesMain.CloseDate) Is Null) AND ((tblCasesMain.OpenDate)<=[Enter the last day of the month]) AND ((tblCasesMain.PendingDate)>=[Enter the first day of the month] Or (tblCasesMain.PendingDate) Is Null) AND ((tblCasesMain.CaseStatus)<>"BSA"));

Query 1InvestigationstblSecRep1:

SELECT tblSecRep1.type, tblSecRep1.desc, tblSecRep1.mmddyyyy, tblSecRep1.[ytd#], tblSecRep1.[ytd$]
FROM tblSecRep1
WHERE (((tblSecRep1.type)=1) AND ((tblSecRep1.mmddyyyy) Between [Enter the first day of the month] And [Enter the last day of the month]));

Query 2Recovered:

SELECT Count(tblPaymentslst.CaseNum) AS CountOfCaseNum, Sum(tblPaymentslst.PaymentAmount) AS SumOfPaymentAmount
FROM tblPaymentslst
WHERE (((tblPaymentslst.PaymentDate) Between [Enter the first day of the month] And [Enter the last day of the month]));

Query 3Recovered(YTD):

SELECT Count(tblPaymentslst.CaseNum) AS CountOfCaseNum, Sum(tblPaymentslst.PaymentAmount) AS SumOfPaymentAmount
FROM tblPaymentslst
WHERE (((tblPaymentslst.PaymentDate) Between [Enter the first day of the YEAR] And [Enter the last day of the month]));

I have to apologize for the amount of code I just posted and thank you deeply for even attepting to read it ;-)

 

[dhookom]

Have you considered using subreports for the individual queries?

Are the above queries related in any way? It looks like they might be retrieving single records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sdk]

Hi

The problem is that you are using a cartesian intersection of the results. If one query returns an empty dataset, then there can be no matching results.

Duane's advice is correct - look at subreports, or look to shape your dataset differently (unions etc). If there is no data for a report, the report's "NoData" event can be used to control the behaviour of the controls.

Cheers

S

 

[underpaidadmin]

Actually I just learned about subreports early this morning trying to look up some more information on my problem. I don't know anything about them much currently and don't have my DB on hand to look it up in the help file. I am guessing it is something like subforms so that there is some linking field and I can use many subreports? When I get access to Access I will see if I can figure out how to set them up and post back here either way. Thank you for also bringing that up, although I am still not sure why

=Format ([Enter end date],"mmmm yyyy")

was not working?

 

[underpaidadmin]

Whoops! the code I posted in the previous post was not the correct code. Here it is:

=Format([Enter the last day of the month],"mmmm yyyy")

I used the same variable as in the queries so the user only had to enter it once.

 

[dhookom]

Subreports are much like subforms and often use the Link Master Child properties.

You have several queries that will return only one row and there won't be a field to link to any field on a main report. Therefore on these subreports, you won't use the Link Master Child.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[underpaidadmin]

One problem I foresee. If I use multiple sub-reports it will keep asking the user to enter the same criteria 8 times for a total of 16 dialog boxes. Is there some way to have it ask just once and pass that info to all subreports?

 

[dhookom]

You should never make your users enter values to parameter prompts. Always use controls on forms for entering criteria. Then you query criteria might look something like:
Between Forms!frmDates!txtStart and Forms!frmDates!txtEnd

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[underpaidadmin]

I am straight but, I could kiss you! That is it! And opened up a lot of other options to me now. Thank you so much!