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

 

[Chris O'C via AccessMonster.com]

In your calculated field determining if the other field is null or not, use
absolute value, like this:

abs(isnull(fieldname))

The results will be 1 if there's data in the field or 0 if there's not.

Chris

 

[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.

 

[Chris O'C via AccessMonster.com]

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

 

[Chris O'C via AccessMonster.com]

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]

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.

 

[Chris O'C via AccessMonster.com]

Use this formula in the field's control source:

=sum(abs(isnull(D1PSI1)))

Chris

 

[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.

 

[Chris O'C via AccessMonster.com]

Access won't let you add any more code until you fix the bad vba code.

Go to the code window. On the menu, click on debug > compile db name.

Please post the highlighted code.

Chris

 

[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