#Error in report 1

[Aubs010]

When I preview a report that contains no records to display, I had an error about debugging. I've gor tid of that by using On Error GoTo Err_Detail_Format

This time, it displays the report ok, but with #Error in each of the controls.

I have tried (e.g.):

=IIf(Right([Ref],2)="zz","",IIf([ref]="","",[Ref]))
=IIf(Right([Ref],2)="zz","",IIf([ref] Is Null,"",[Ref]))

neither of these do anything, it still shows #Error

Any suggestions greatly appreciated.

Aubs

 

[jrbarnett]

Try

Iif(Right([ref] & "",2)="zz",Null,[Ref])

John

 

[Aubs010]

the bits in red are the ones causing the problem.

=IIf(Right([Ref],2)="zz","",IIf([ref]="","",[Ref]))
=IIf(Right([Ref],2)="zz","",IIf([ref] Is Null,"",[Ref]))

zz is only in [ref] for some records.

Therefore:
if it has zz, show nothing
if it is nothing/record does not exist show nothing
else show it all



Aubs

 

[CosmoKramer]

Aubs,

How about this:

=IIf((Right([Ref],2)="zz") Or ([ref] Is Null),"",[Ref])

 

[Aubs010]

Cosmo,

That would work just as well as the two I posted. The problem is, it's not null, or doesn't seem to be. The problem is, there's no record to display i.e. the table is empty! and it shows #Error in all the controls for the first record, and that's it!

Thanks for helping

Aubs

 

[CosmoKramer]

If there are no records, then in the OnNoData event of the report, put code like this:

MsgBox "No data found for this report"
Cancel = True

This way, the report will not be generated at all......

 

[Aubs010]

Brilliant, thanks Cosmo, next problem from that though, is that I now get an error message:

The OpenReport action was canceled

Any way of getting round this?

Thanks again,

Aubs

 

[CosmoKramer]

Here's an excellent FAQ by garridon that covers this:

faq703-1594