If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#Error on when ther is no data for Calculation
I have an Access 2003 form where the on some records the calculation
works fine when all elements of the calculation have values. When there are no values in one or more of the variables it results in a #Error. Is there a way to prevent that at the Form level? Any help with this appreciated! Rbollinger |
#2
|
|||
|
|||
No. You cannot prevent this at the form level. It is a matter of
understanding why the error occurs, and dealing with it on a case-by-case basis. On cause is mal-formed arguments. If you have: =DLookup("MyField", "MyTable", "ID = " & [ID]) and you are at a new record where ID has not been assigned, the 3rd argument resolves to just: ID = Clearly that is going to produce an error. To avoid that, use Nz() to supply some value, e.g.: =DLookup("MyField", "MyTable", "ID = " & Nz([ID],0)) If there are no records in the form, and no new records can be added, the detail section of the form goes completely blank. The form header and footer still show, and if you have a calculation that refers to the non-existent controls in the detail section, they will error. There are convoluted ways of trying to get around that; for example, you could try referring to MyControl like this: =IIf([Form].[RecordsetClone].[RecordCount]=0, Null, [MyControl]) However, this scenario is likely to thoroughly confuse Access anyway, so you are still likely to run into problems. Details in: Incorrect display of data at: http://allenbrowne.com/bug-06.html So the simpler workaround might be to set the form's AllowAdditions property back to Yes (so the detail section does not go blank), and cancel the BeforeInsert event to prevent new records. There are several other causes of #Error, such as division by zero, circular dependencies, and so on. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "robboll" wrote in message oups.com... I have an Access 2003 form where the on some records the calculation works fine when all elements of the calculation have values. When there are no values in one or more of the variables it results in a #Error. Is there a way to prevent that at the Form level? Any help with this appreciated! Rbollinger |
#3
|
|||
|
|||
Divide by Zero is probably one of the biggest offenders in this kind of
situation. The easy fix for that is to test for zero before doing the divide: Me.txtBoxOne = Me.txtBoxTwo / IIf(IsNull(Me.txtBoxThree),1,Me.txtBoxThree) "Allen Browne" wrote: No. You cannot prevent this at the form level. It is a matter of understanding why the error occurs, and dealing with it on a case-by-case basis. On cause is mal-formed arguments. If you have: =DLookup("MyField", "MyTable", "ID = " & [ID]) and you are at a new record where ID has not been assigned, the 3rd argument resolves to just: ID = Clearly that is going to produce an error. To avoid that, use Nz() to supply some value, e.g.: =DLookup("MyField", "MyTable", "ID = " & Nz([ID],0)) If there are no records in the form, and no new records can be added, the detail section of the form goes completely blank. The form header and footer still show, and if you have a calculation that refers to the non-existent controls in the detail section, they will error. There are convoluted ways of trying to get around that; for example, you could try referring to MyControl like this: =IIf([Form].[RecordsetClone].[RecordCount]=0, Null, [MyControl]) However, this scenario is likely to thoroughly confuse Access anyway, so you are still likely to run into problems. Details in: Incorrect display of data at: http://allenbrowne.com/bug-06.html So the simpler workaround might be to set the form's AllowAdditions property back to Yes (so the detail section does not go blank), and cancel the BeforeInsert event to prevent new records. There are several other causes of #Error, such as division by zero, circular dependencies, and so on. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "robboll" wrote in message oups.com... I have an Access 2003 form where the on some records the calculation works fine when all elements of the calculation have values. When there are no values in one or more of the variables it results in a #Error. Is there a way to prevent that at the Form level? Any help with this appreciated! Rbollinger |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I save an access document in word document? | cmartin | General Discussion | 2 | September 13th, 2005 11:26 PM |
Unable to have multiple queries feeding a single report | PZ Straube | Setting Up & Running Reports | 15 | June 15th, 2005 08:16 AM |
Data Source issues. ??data.access.pages | Phil | Database Design | 2 | October 11th, 2004 02:42 AM |
Data Source issues | Philippe | Database Design | 1 | October 10th, 2004 09:45 PM |
How to create graphs in a monthly report where the base data can change | John Clarke | Charts and Charting | 3 | June 25th, 2004 02:22 AM |