When a calculated field is zero on a subform, it returns "#Erreur"

Question

Hi everyone,

I have a form named “Form1” on which I have a tab “Tab1” with a subform named “subform1” on Tab1

On the subform, I have a calculated field name “CalcField1”

Now, when the recordset attach to the “Calcfield1 has record, no problem, the sum is correct and everything works fine when at least

1 record is in it.

However, when it has no records, the “Calcfield1” returns “#Error” even if my default value is 0.

When a recordset does not have any records, my calculated field returns a string “#Error”, I did put the default value to 0

but it is always the same.

  

I read that we can do something about it, but even if I tried, still the same, what I found is that a module can be created as follows:

formHasDataModule

Option Compare Database

Public Function HasData(frm As Form) As Boolean
  HasData = (frm.Recordset.RecordCount <> 0&)
End Function

Can someone please show me how to use this module to have the field “Calcfield1” return 0 when it has no record.

Thanks

---

Claude Larocque

Answer 1

You did not post the calculation.

Answer 2

Hi Karl

It was a little complicated to post the calculation, however because it is important, I did the time to explain in details where the calculated field that don't shows the zero comes from.

I put the file on skydrive in my public folder, here is the link:

https://skydrive.live.com/view.aspx?cid=F7190044A52FAE85&resid=F7190044A52FAE85%21193

Thank you to take time to help

Have a good day!

 

Claude from Quebec

 

 

 

---

Claude Larocque

Answer 3

You can use Keri Hardwick's nnz() function to solve this problem"

    http://access.mvps.org/access/forms/frm0022.htm
    Forms: #Error when the Subform has no records

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 4

Hi Karl,

Wow, I did learn in the last year, but I am not sure what to do, I know that to learn it's trial and error time, so I did this:

Thanks for your quick response; I did create a module named “Module nnz” like below, I put Public Function instead of just Function.

I have added a subform on the main form for testing purpose:

Name of main form: « Détails de fermeture de caisse »

Name of subform : « testvalue »

Name of field in the subform : « SommeDeMontantReçu2 »

The source of the subform is: “Synthèse des commandes payées TOTAUX par type2” query

The source of the field is: “SommeDeMontantReçu2”

Module nzz:

Option Compare Database

'***************** Code Start ***************
'This code was originally written by Keri Hardwick.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Keri Hardwick
'
Public Function nnz(testvalue As Variant) As Variant
'Not Numeric return zero
  If Not (IsNumeric(testvalue)) Then
    nnz = 0
  Else
    nnz = testvalue
  End If
End Function
'***************** Code End ****************
Public Function IsAnError(testvalue As Variant) As Boolean
      IsAnError = IsError(testvalue)
End Function

 

On afterupdate of the field I put that:

Private Sub SommeDeMontantReçu2_AfterUpdate()
If Forms![Détails de fermeture de caisse].[testvalue].[SommeDeMontantReçu2].IsAnError Then
nzz = 0
End If
End Sub

And nothing works, but I was expecting that, I thought may be with this start it will be faster for you to correct me.

Thanks

---

Claude Larocque

Answer 5

Hi Karl,

Wow, I did learn in the last year, but I am not sure what to do, I know that to learn it's trial and error time, so I did this:

 Thanks for your quick response; I did create a module named “Module nnz” like below, I put Public Function instead of just Function.

 I have added a subform on the main form for testing purpose:

Name of main form: « Détails de fermeture de caisse »

Name of subform : « testvalue »

Name of field in the subform : « SommeDeMontantReçu2 »

The source of the subform is: “Synthèse des commandes payées TOTAUX par type2” query

The source of the field is: “SommeDeMontantReçu2”

Module nzz:

Option Compare Database



'***************** Code Start ***************

'This code was originally written by Keri Hardwick.

'It is not to be altered or distributed,

'except as part of an application.

'You are free to use it in any application,

'provided the copyright notice is left unchanged.

'

'Code Courtesy of

'Keri Hardwick

'

Public Function nnz(testvalue As Variant) As Variant

'Not Numeric return zero

 If Not (IsNumeric(testvalue)) Then

  nnz = 0

 Else

  nnz = testvalue

 End If

End Function

'***************** Code End ****************

Public Function IsAnError(testvalue As Variant) As Boolean

   IsAnError = IsError(testvalue)

End Function

 

On afterupdate of the field I put that:

 

Private Sub SommeDeMontantReçu2_AfterUpdate()

If Forms![Détails de fermeture de caisse].[testvalue].[SommeDeMontantReçu2].IsAnError Then

nzz = 0

End If

End Sub

And nothing works, but I was expecting that, I thought may be with this start it will be faster for you to correct me.

First, I note that it looks as though you intended to name your module ("nzz") the same as the function ("nnz").  If the code you posted is accurate, you didn't actually do that, but you shouldn't even try -- a module must not have the same name as any function it contains.  Call the module "modNnz", or "modUtilities" (if you intend to put more than one function in there), or some other unique name.

Second, forgive me but what you are trying to do in the AfterUpdate code you posted seems completely wrong.  The purpose of the nnz() function is to transform a control that contains an #Error value (or any non-numeric value) into a 0.  So if you have a control on the subform that returns an error value (e.g., #Error), then you can have another control that uses nnz() to transform that value into 0.  For example, if your control [SommeDeMontantReçu2] returns #Error, you can have another control that has this as its controlsource:

    =nnz([SommeDeMontantReçu2])

That control will display 0 if SommeDeMontantReçu2 is Null or #Error or any non-numeric value.

Understand, it might be simpler to rewrite your original calculation so that it doesn't return an error when the subform has no records.  But I don't have time to download your file from Skydrive and examine it.  If you post the actual controlsource of SommeDeMontantReçu2, I may have time to look at that and consider how it might be fixed.

 

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 6

I think you are taking the long road to a solution.  I think all your need is to change the text box expression from:

   =your calculation

to:

   =IIf(Form.Recordset.RecordCount = 0, 0, your calculation)

Answer 7

Hi Marshall or Andrey

I did try your suggestion without success, however, instead of having an #Error I have a blank field, go figure, however it does not works for my other calculations that needs a value to work,

It seems so easy but I can't figure it out.

I did put another worksheet and I show the tables, queries and forms.... may be I can't see straight...hopefully you can help

Here is the link:

https://skydrive.live.com/view.aspx?cid=F7190044A52FAE85&resid=F7190044A52FAE85%21195

Thanks for your time

---

Claude Larocque

Answer 8

Try this instead - a simple formula in the field with the error.

MainForm [Field on Form]=IIf(NZ([subform].[Form]![Field in Subform],0)=0,)

---

Chris Ward

Answer 9

Hi Chris and other helpers,

It seems so easy when I read the answers, but I can't have this #Error go away, I tried and it went away but it was blank instead so I couldn't use it anyway to perform calculation on these control, no zeros in sight...

Thank you

 

---

Claude Larocque

Answer 10

If you still have the above code it may be necessary to place a ' in front of each line to temporarily stop the code then test again. if it still doesn't work you can always remove the ' infront of each line of code

'Sub....

'

'

'End Sub

Another approach I've used

=NZ([APPRO subform].[Form]![SumOfPaymentAmount],0)

---

Chris Ward

Answer 11

You may have to move up the chain, into the query to get a handle on what is being reported. For instance if you are trying to get a count on records that have data, in some situations you have to do something like the following in order to make it count right;

SELECT Count(*) AS CountOfPEBNumber, Sum(IIf(Products.Closed,1,0)) AS CountOfAdministrativelyClosed, [Begin Date] AS SubmittalBegin, [End Date] AS SubmittalEnd
FROM Products
WHERE (((Products.SubmittalDate) Between [Begin Date] And [End Date]))
GROUP BY [Begin Date], [End Date];

---

Chris Ward

Answer 12

Hi Chris,

I'll try that in a few hours, I'll get back to you with more details

Have a nice evening

Claude

 

---

Claude Larocque

Answer 13

Thanks Chris

You put me in the right direction, now I have zeros instead of #Erreur

Have a great day

Claude

 

---

Claude Larocque

Answer 14

Hi 

I have just been struggling with the same problem.

I could not adjust the input query as the logic of the form and underlying query did not allow for that.

I tried the nnz function but could not get it to work.

The simple answer is to use the built in "iserror" function in the field in the main form as follows:

=IIf(IsError([NameofYourSubform].[Form]![NameOfFieldOnSubform]),0,[NameofYourSubform].[Form]![NameOfFieldOnSubform])

This returns a 0 (or whatever value you want) if there is no record in the subform 

and the value of the NameOfFieldOnSubform if a record is present.

Hope that might help someone

phil

---

So many bugs....so little time to swat them :)