How to get rid of #Error on reports

• May 17, 2004

• #2

SnaveBelac

Programmer

Oct 21, 2003

89

GB

You could prevent the report from appearing if the recordset is empty. Just pop up a message box instead.

----------------------------
SnaveBelac - Adventurer
----------------------------

 

Upvote 0 Downvote

• May 17, 2004

• #3

danvlas

Programmer

Jul 30, 2002

2,446

RO

=IIf(IsError(Expression), 0, Expression)

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

Upvote 0 Downvote

• May 17, 2004

• Thread starter
• #4

devGarfield

Programmer

Mar 23, 2004

31

GB

I tried iif(IsError(expression),0,expression) and it gave me the same answer.

This is what I have

=IIf(IsError(Sum(Exam Fee Cost]),0,Sum([Exam Fee Cost])) which gives me #Error

Also, the data for the form is based on a query, which in this case is returning an empty recordset. Probably need a way to check for empty recordsets before displaying any data

Any ideas?

 

Upvote 0 Downvote

• May 17, 2004

• #5

SnaveBelac

Programmer

Oct 21, 2003

89

GB

Write an SQL statement (or copy the one created by the query) and run that prior to opening the report, in the event of an EOF, display a message box instead of opening the report.

----------------------------
SnaveBelac - Adventurer
----------------------------

 

Upvote 0 Downvote

• May 17, 2004

• Thread starter
• #6

devGarfield

Programmer

Mar 23, 2004

31

GB

The report is based on a automatically generated SQL query, how can I check for EOF within the report, I'm thinking of adding some code in the OPEN_REPORT event but don't know what to add for this check

 

Upvote 0 Downvote

• May 17, 2004

• #7

PHV

MIS

Nov 8, 2002

53,708

FR

And what about the NoData event procedure ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

Upvote 0 Downvote

• May 17, 2004

• #8

SnaveBelac

Programmer

Oct 21, 2003

89

GB

I am not an expert in access but I have been programming it for many years now - I had never come across the NoData event before - That should do the trick !!

----------------------------
SnaveBelac - Adventurer
----------------------------

 

Upvote 0 Downvote

• May 17, 2004

• Thread starter
• #9

devGarfield

Programmer

Mar 23, 2004

31

GB

Never heard of the NoData Event, but I tried it as suggested, but it didn't fire.

Any other suggestions?

 

Upvote 0 Downvote

• May 17, 2004

• #10

danvlas

Programmer

Jul 30, 2002

2,446

RO

You have a missing bracket in your expression:
=IIf(IsError([Sum(Exam Fee Cost]),0,Sum([Exam Fee Cost]))

I'd make the calculation text box (Sum([Exam Fee Cost]) invisible so that the Sum is triggered only once...

Place another textbox on the report to get the value from the invisible one:
=IIf(IsError(TotalTextBox), 0, TotalTextBox)



The NoData event should fire all right if you use the syntax:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "Oops, no data..."
Cancel = True
End Sub

Remember to trap the error 2501 (Action was canceled) in the calling code.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

Upvote 0 Downvote

• May 17, 2004

• #11

SnaveBelac

Programmer

Oct 21, 2003

89

GB

How is you query being built? If you are creatng your recordset via code, you could check for zero records there.

Alternatively you could try checking for a different condition using your IIF statement...

Code:

=IIf(isNull([detExamFee]),0,Sum([Exam Fee Cost]))

I am not familiar with the NoData event so can't advise on that. I would recommend checking the help files for the details of how this works.

----------------------------
SnaveBelac - Adventurer
----------------------------

 

Upvote 0 Downvote

• May 17, 2004

• Thread starter
• #12

devGarfield

Programmer

Mar 23, 2004

31

GB

Well, I've managed to sort it out with all of your help.

My solution in the end was to create another textfield to store the result of the summation and hide it, then using the IIF(IsError.....) example from above displayed 0 in my report instead of #Error.

Thanks all for your help.

 

Upvote 0 Downvote