Need help with something from MVP website

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have encountered a little problem which I found some information about on
the MVP website http://www.mvps.org/access/forms/frm0022.htm
But unfortunately, I am not strong enough to crack the code and implement
this on my DB. Can anyone explain this to me in layman terms... where, what
to change, how...

On subform... record field I have this
DS1 =IIF([specialisme]=[DoctorSpeciality1];1;0)
to count the records in case the condition is true. (CountIf didn't work so
good)
Subform also has a total of this in the footer.
=Count([DS1])
This works fine...
Now I try to put over the total DS1 to the main form and I get #Error when
no records in the subform...

Thanks for the assistance
Your humble apprentice,

Ben
 
You need to create a standard code module.

click on the "modules" tab in the main access window.

If you already have a module, then you can simply use that exist code module
(each module can have "MANY" code functions that your write).

If you don't have a module, then simply click on "new", and you will jump
right into the code editor. Simply "paste" the following code (from that web
site).

Here is the code you paste:

'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
'
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


Now once you put the above code in the module...click on the save button.
You can use the "default" module name (ie: module1, module2..etc..these
number just like forms, or queries when you don't give them a name).

after you save, then close the code editor (click on the 'x' in the upright
right hand corner).

At this point in time, you are now able to use the nnz function anywhere.

So, in your case, change you expression in the "main" code for from

=nzz( you existing expression in the main form goes here )

All that the nzz function does is check if nothing is return..and thus
changes it to a zero...
 
Wonderful.

This was extremely simplified and I understand it now. I believe that you
just multiplied my understanding of Access by 50.
It works perfectly !

Greetings,

Ben
 
Back
Top