Convert True (-1) or False (0) to a calculable number (1) or (0)

[Aso]

I have a field that outputs a -1 or 0 depending on whether the field is not
null or is null. I am having troubles finding the right statement to the four
fields that have either -1 or 0 and adding up the responses, ex:

D1 499 = -1
D2 500 = -1
D3 450 = -1
D4 = 0

so now that I have the True/False conversion to "-1" or "0", I cannot get
the field i wish to add the four fields up to work. I've tried Abs, sum,
count. How do i get the form to recognize this output as an actual number so
i can merely add up the absolutes of each to come up with a divisor for the
next step in the calculation?

Argh, help, please v.v
Aso

 

[Aso]

I get an error message:
"There was an error compiling this function. The Visual Basic module
contains a syntax error. Check the code, recompile it."

I have been using an Iif statement =IIf([D1PSI1] Is Null,"0","-1") to
convert the not null response to -1 and the null response to 0. I just can't
seem to get the field set up for the calculation to recognize and calculate.
I've tried the abs and the nz. I am really not sure what step i am missing
here.

 

[Aso]

I am attempting to use the formula in the control source, not in the event
procedure etc. All the particular formulas that I am currently working with
in regards to this form are in the control source, their purpose is to just
give me something and some way of adding up non null fields to get a single
digit that will be used as a divisor ultimately for the average of a few
numbers. Im trying to get the form to add say we have 4 fields and three have
a response and one does not. I need the divisor to be 3 not 4. I mean I am
sure you understand where i am trying to get here, I appreciate your help
with this. =)

Let me make that more clear. Open the form's property sheet while in design
mode. Use the expression I gave you in the field's control source property,
not in an event procedure in the form's vba module.

Chris

Are you in the field's control source property? Use this expression for that
property, not in an event procedure:

=abs(isnull(D1PSI1))

Chris

I get an error message:
"There was an error compiling this function. The Visual Basic module

[quoted text clipped - 5 lines]

I've tried the abs and the nz. I am really not sure what step i am missing
here.

 

[Aso]

Yeah Chris, I just dont get it. I am still getting the same error message. I
am becoming completely frustrated by something that i feel should be so
completely simple. ugh.

 

[Steve Sanford]

In an unbound text box on the form, enter the following in the "Control
Source":

=Abs(Not IsNull([D1]))+Abs(Not IsNull([D2]))+Abs(Not IsNull([D3]))+Abs(Not
IsNull([D4]))

Change D1, D2, D3, D4 to the actual names of the text boxes (controls) on
the form.


Note: tables have fields, forms have controls. When you create a control by
dragging a field from the field list box, Access names the control the same
as the field. It makes things easier if you rename the control to something
different than the field name (ie use a naming convention).

HTH