By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Getting Rid of #Error

P: 41
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
Mar 15 '08 #1
Share this Question
Share on Google+
16 Replies


missinglinq
Expert 2.5K+
P: 3,532
What exactly is [System]? What Datatype?

Welcome to TheScripts!

Linq ;0)>
Mar 15 '08 #2

Expert 100+
P: 112
I'm trying to duplicate this error to see if I can fix it, what data type is your [System] Column?
Mar 15 '08 #3

P: 41
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
Mar 16 '08 #4

P: 41
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.
Mar 16 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 16 '08 #6

P: 41
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
Mar 16 '08 #7

NeoPa
Expert Mod 15k+
P: 31,186
I think the matching answer is :
Expand|Select|Wrap|Line Numbers
  1. =IIf(IsError([System]),'',[System])
Mar 16 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_NoData(Cancel As Integer)
  2.  
  3. 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).
Mar 16 '08 #9

P: 41
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_NoData(Cancel As Integer)
  2.  
  3. 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
Mar 16 '08 #10

NeoPa
Expert Mod 15k+
P: 31,186
At its simplest :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_NoData(Cancel As Integer)
  2.   Cancel = True
  3. End Sub
Mar 17 '08 #11

Scott Price
Expert 100+
P: 1,384
Have a look at Avoid #Error in Form/Report with no data on Allen Browne's website.

Regards,
Scott
Mar 17 '08 #12

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

Regards,
Scott

Thanks for the information.

David
Mar 17 '08 #13

P: 41
At its simplest :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_NoData(Cancel As Integer)
  2.   Cancel = True
  3. 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
Mar 17 '08 #14

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.   Call Application.Quit
  3. End Sub
Mar 17 '08 #15

P: 41
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.
Mar 18 '08 #16

NeoPa
Expert Mod 15k+
P: 31,186
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.
Mar 18 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.