Getting Rid of #Error

[David Davis]

Hello,

I need help on getting rid of #Error in my fields on a form when no data exist. I was using the below formula:

="" & [System]

and when #Error showed I switched over to

=IIf(IsNull([System])," ",[System])

and that came up with the same results. If data exist it works fine but when data is not there I want it to be blank or say "No data exist".

What formula can I use in the control source to fix this. Any assistance would be most appreciative.

Thanks

[missinglinq]

What exactly is [System]? What Datatype?

Welcome to TheScripts!

Linq ;0)>

[janders468]

I'm trying to duplicate this error to see if I can fix it, what data type is your [System] Column?

[David Davis]

I'm trying to duplicate this error to see if I can fix it, what data type is your [System] Column?

Sorry about the slow response but two are of text and one is Date/Time.

It looks like this

="of " & [StudentCompany]

="" & [System]

=" on " & [GraduationDate]

Let me know what you come up with.

Thanks

[David Davis]

What exactly is [System]? What Datatype?

Welcome to TheScripts!

Linq ;0)>

I'm sorry Linq, but System is one of the fields from table1 used on the form and DataType is text.

Thanks for the fast response sorry couldnt say the same but step out a bit, didnt think anyone would respond back so quick.

[NeoPa]

David,

I've tried various approaches but was unable to duplicate your problem in any of them I'm afraid.

It appears there's some quite fundamental information that you haven't included in the question. I can't tell you what it may be, but this isn't standard behaviour in a form to report #Error simply because a field has no data.

[David Davis]

David,

I've tried various approaches but was unable to duplicate your problem in any of them I'm afraid.

It appears there's some quite fundamental information that you haven't included in the question. I can't tell you what it may be, but this isn't standard behaviour in a form to report #Error simply because a field has no data.

NeoPa,

I miss quoted before in saying it was a form, in fact it is a report. I am missing something but cant figure it out.

David

[NeoPa]

I think the matching answer is :

=IIf(IsError([System]),'',[System])

[NeoPa]

Should the question rather be though :
How do I stop a report from running if there are no matching records?

If so, the answer is that there is an On No Data event.

Private Sub Report_NoData(Cancel As Integer)
 
End Sub

This allows code to run informing the operator of the situation as well as allowing the report to be cancelled entirely (Set Cancel to True before exiting procedure).

[David Davis]

Should the question rather be though :
How do I stop a report from running if there are no matching records?

If so, the answer is that there is an On No Data event.

Private Sub Report_NoData(Cancel As Integer)
 
End Sub

This allows code to run informing the operator of the situation as well as allowing the report to be cancelled entirely (Set Cancel to True before exiting procedure).

Thanks NeoPa,

Good solution but how do you set it to true.

David

[NeoPa]

At its simplest :

Private Sub Report_NoData(Cancel As Integer)
  Cancel = True
End Sub

[Scott Price]

Have a look at Avoid #Error in Form/Report with no data on Allen Browne's website.

Regards,
Scott

[David Davis]

Have a look at Avoid #Error in Form/Report with no data on Allen Browne's website.

Regards,
Scott

Thanks for the information.

David

[David Davis]

At its simplest :

Private Sub Report_NoData(Cancel As Integer)
  Cancel = True
End Sub

NeoPa,

Thanks, that worked perfectly I have just one more question. How can you make Microsoft Access close the same time the database closes.

David

[NeoPa]

There's no really direct way, but if you cause an invisible form to open when you open the database (Tools / Startup...) then you can leave it running in the background while the database is open.

In the Form_Close() event procedure you can then call a close of Access itself :

Private Sub Form_Close()
  Call Application.Quit
End Sub

[David Davis]

NeoPa,

I can definitely say you are the expert. Everything work like you said and I appreciate your help. I am just about done. Can you throw me another bone on another stump I hit. ...
** Edit **
Question asked in new thread - see next post.

David.

[NeoPa]

Thank you for your comments David.

I have split off the extra question into its own thread (Select Report to Open by CheckBox) as explained in the thread.