IsError()

[Rockape]

Hi all,

My problem is the following and somehow someone has previously suggested the IsError() function

I have a report which produces information based on the date parameters that I apply. If however data is not availablefor that date range I get the #Error appearing.

Is there a way of using this function to avoid having the #Error messae appearing and substituting it with say a statement eg. "no data available for that date range"

Any advice much appreciated.

 

[vbaInet]

I'm pretty sure there was a question from me which remained unanswered regarding your problem.

 

[Rockape]

I'm pretty sure there was a question from me which remained unanswered regarding your problem.

Hi,

How did you get around it?

Thanks

 

[vbaInet]

My question was what calculation are you doing?

 

[Rockape]

My question was what calculation are you doing?

Ok sorry,, misunderstood.

Its basically a summing up of entries.

i.e. the standard report sums up the amounts associated to a particular code which has a date assigned to it.

If the report finds no amounts for that code then #Error appears.

Similarly if If the report finds no amounts for that code for a specified date range then #Error appears.

I hope that makes sense!

Thanks again

 

[vbaInet]

Let's see the expression you've used in the textbox.

 

[Rockape]

Hi,

Alternatively I'm looking for a VBA script which recognises the fact that the report does not have data to produce and will therefore produce a statement which says eg "No data available"

Thanks

 

[vbaInet]

So the msgbox will show if there's no data and the report will not be opened right?

 

[Rockape]

So the msgbox will show if there's no data and the report will not be opened right?

Hi,

Well not quite.

I would want the report to open but somewhere on the report there would be a statement saying that there is no data available.

It seems there is a function HasData which perhaps is useful??

Many thanks for your patience

 

[vbaInet]

You can use a textbox for that purpose:

=IIF([HasData] = 0, "No data available", "")

However, you still haven't dealt with the #Error that will still be displayed.

 

[Rockape]

You can use a textbox for that purpose:

=IIF([HasData] = 0, "No data available", "")

However, you still haven't dealt with the #Error that will still be displayed.

Hi,

Where exactly would i place this? I tried pasting it on the report but nothing?

So near and yet so far

Help

 

[vbaInet]

You paste it in the Control Source property (i.e. under the DATA tab in the property sheet) of a textbox.

 

[Rockape]

Hi again,

My other idea was to prepare a blank report with the statement "no data available"

If the record set produced by the query/form contained no data then the blank report would be produced.

How would that work? Hmmmm

Thanks

 

[vbaInet]

Show me the exact code you use to open the report. And also tell me the name of this blank report.

 

[Rockape]

You paste it in the Control Source property (i.e. under the DATA tab in the property sheet) of a textbox.

Many thanks,

It worked but still have #Error problem

Many thanks

 

[vbaInet]

Yes I know, that was what I mentioned in my post #10.

This still brings us back to my question in post #4. What calculation are you performing in that textbox? Show the calculation.

 

[Rockape]

Show me the exact code you use to open the report. And also tell me the name of this blank report.

Hi the blank report has just occurred to me.

Howver there is no code as such. It is simply a form and query working in tandem.

the form has a combo from which to select the code. The query has the reference to that part of the form. In other words the query picks up what is selected in the form then carries out the calculation. The standard report is created from the query results.

I was thinking that if the recordset produces no records then a another report would be produced stating that there is no data for the code selected... somethin on those lines.

This might need some VBA code ??

thanks

 

[vbaInet]

I think you should stick to one thing. Either you try to fix the #Error problem or you show a blank report.

How does the report get opened, isn't it via a button? Behind that button there's code.

 

[gemma-the-husky]

the report has a property (event)

onnodata

which you can use to prevent the report opening. (set cancel to true) and give a sensible message.

you will then get a 2501 error in the form trying to launch the report, which is easily handled.

is that what you want?




just use this

 

[vbaInet]

the report has a property (event)

onnodata

which you can use to prevent the report opening. (set cancel to true) and give a sensible message.

you will then get a 2501 error in the form trying to launch the report, which is easily handled.

is that what you want?




just use this

That had been suggested. Doesn't seem to be the case Dave.