#Error on Report 1

[cariengon]

I have searched through the forums to find an answer to my dilema, but nothing seems to work!

I have a control that is summing up all of the records that are returned on the main form. If there are no records that are generated by the query, it is returning an #Error.

I've tried the following:

=IIf(IsNull(Sum([LE_Acct_1])),0,Sum([LE_Acct_1]))
=IIf(NZ(Sum([LE_Acct_1])),0,Sum([LE_Acct_1]))
=IIf(Sum([LE_Acct_1])IsNull,0,Sum([LE_Acct_1]))

And I still get the #Error.

The only way I didn't get the error is if I did it this way:
=IIf(NZ([LE_Acct_1])),"0.00",Sum([LE_Acct_1]))

BUT the problem was that it looked at only one record in the returned recordset and assigned the value. I need to keep the returned records null (for report appearances).

Anyone know why it doesn't work if I have the NZ(Sum([LE_Acct_1)???

Thanks -
Carie

 

[PaulBricker]

Try this. Summing 0's is going to give you 0 so you don't need the IIF.

=Sum(Nz([LE_Acct_1],0))

Paul

 

[cariengon]

Thanks - That worked for if there were records returned, but I'm now getting the #Error if there are no records that were returned by the query.

Do I need to add an Iif statement for this?

 

[PaulBricker]

An IIf isn't going to help when no records are returned. If you are just Printing the Report out without previewing it, you could just use the OnNoData Event. If you open the Report for preview from a button, you can do something like this.

Dim rst as DAO.Recordset
Set rst = CurrentDb.OpenRecordset("QueryThatSuppliesReport",dbopendynaset)
rst.moveFirst
If rst.EOF or rst.BOF Then
MsgBox "There are no records for this Report"
Else
DoCmd.OpenReport "Report Name"
End If


Paul

 

[cariengon]

Thanks Paul -

I just may use this. I swear I had this issue on a previous database I built, but I think I forced the 0.00 in the query. For appearances, my report looks much cleaner with out the 0.00 in each individual record - but the client may just want it.

I appreciate your help!
Carie

 

[PaulBricker]

Carie, you certainly could force the 0's in your query using the Nz function.
MyLE_Acct_1:NZ([LE_Acct_1],0)
That would give you some additional options within the report because MyLE_Acct_1 would be available to the Report as a Field instead of a Calculated Field within the Report. As for not returning any records, that would probably still need to be handled with some type of code.

Paul

 

[cariengon]

I'm really at a roadblock with this. I can't use the OnNoData event for this main form that I have. It's made up with several SubReports for a Grand Total.

I'm getting the #Error because this is the "meat" of my report and is on the main report (and not on a subreport). If there are no records - I need to put this on this main report so the user (and others that will view the results via the report) are aware of this.

Does the #Error return a value that you can overwrite?? Can't I add something like...

Iif ([field1]=#Error,"There are no records",[field1])

It seems like you should be able too as the field really isn't returning a null - or is it??!! Or is this another one of Access's quirks?

Does this make sense???

Thanks,
Carie

 

[PaulBricker]

Carie, look at the HasData property. It is specifically for SubReports.

Me!SubReportControl.Visible = Me!SubReportControl.Report.HasData

I haven't used this property in a long time. Don't remember exactly where to put this code but you can try the Open event for the Main Report. Or the Format Event for the section of the Main Report the subreports are in. You would have to add one line for each subreport.

Paul

 

[cariengon]

Thanks Paul! I'll try this first thing tomorrow!! I just hope it's not limited to subreports...

I also did a lookup in the forums on HasData and I found a reference to this!

This Knowledge Base article should help:

http://support.microsoft.com/support/kb/articles/Q138/9/06.asp?PR=ACCT

Thanks Again -
Carie

 

[PaulBricker]

And look at that, they have it wrapped in an IIf() function.

Paul

 

[cariengon]

Hi Paul!

I got it to work!!!

I created a Text box that is set at Visible = False. If there is no data, the text box is visible and the controls that show #Error are changed to Visible = False.

Here's the code I used:

Private Sub Report_NoData(Cancel As Integer)

If Me.Report.HasData = False Then
Me.ctl_HasNoData.Visible = True
Me.ctl_HasNoData = "There are no Unmatched Entries for these Legal Entities."
Me.Text53.Visible = False
Me.Text54.Visible = False
Me.Text55.Visible = False
Me.Text63.Visible = False
Me.Text64.Visible = False
Me.Text65.Visible = False
Me.Text58.Visible = False
Me.Text59.Visible = False
Me.Text60.Visible = False
Else
Me.ctl_HasNoData.Visible = False
End If

End Sub

Thanks for pointing me in the right direction!



Carie

 

[PaulBricker]

Awesome Carie. Nice job.

Paul