Default value for a check-box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Franklin - 07 Mar 2007 14:44 GMT

I am trying to set up a form that will calculate a percentage average based
on certain check-boxes. Each section in the form consists of a number of
check-boxes (Yes/No data types) which I want to assign default values to. I
have tried assigning the fields with a default value (i.e. 2.5, 5, 10, etc)
but it doesn’t seem to work when the average is calculated, or rather the
values I assigned to the check-boxes do not effect the average calculation I
have in a separate field. How do I give a default value for a check-box, so
that when the box is checked, that value will be applied to the control with
that is calculating the average? Help!

Reply to this Message

Carl Rapson - 07 Mar 2007 16:33 GMT

The default value for a checkbox is really meant to determine whether the
box is checked or unchecked by default, not as a value that is returned. Try
putting your values in the Tag property of the checkbox and use that value
if the checkbox is True.

Carl Rapson

>I am trying to set up a form that will calculate a percentage average based
> on certain check-boxes. Each section in the form consists of a number of
[quoted text clipped - 10 lines]
> with
> that is calculating the average? Help!

Reply to this Message

Franklin - 07 Mar 2007 19:44 GMT

Should I be using something other than a check-box (yes/no) data type then? I
would ultimately like whatever value is assigned to the check box to be
'true' when it is selected. I tried putting in the values in the Tag property
and it still did not work. What am I doing wrong?

> The default value for a checkbox is really meant to determine whether the
> box is checked or unchecked by default, not as a value that is returned. Try
[quoted text clipped - 17 lines]
> > with
> > that is calculating the average? Help!

Reply to this Message

Al Campagna - 08 Mar 2007 05:25 GMT

Franklin,
Please don't cross-post. Each post is generating different responses, and responders
in one group don't know what others have suggested in another group. It can only lead to
confusion, and is a duplication of effort.
I responded to you on 3/7 in the Forms group.

Signature

hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

> Should I be using something other than a check-box (yes/no) data type then? I
> would ultimately like whatever value is assigned to the check box to be
[quoted text clipped - 22 lines]
>> > with
>> > that is calculating the average? Help!

Reply to this Message

Al Campagna - 08 Mar 2007 14:01 GMT

Sorry... I responded on 3/7 in Getting Started, not Forms.

Signature

hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Reply to this Message

Franklin - 08 Mar 2007 19:11 GMT

[Are values of Field1 thru Field8 constant? But... sometimes you select some
and not
others?]
That's correct, the values in each field one are constant/they do not change
and select some and not others.

The odd thing is when even without entering any default value for any of the
fields, either in the table or on the form, I still get a value of -1.0 when
I check any one box.

> Sorry... I responded on 3/7 in Getting Started, not Forms.
> > Should I be using something other than a check-box (yes/no) data type then? I
[quoted text clipped - 23 lines]
> >> > with
> >> > that is calculating the average? Help!

Reply to this Message

Carl Rapson - 08 Mar 2007 17:27 GMT

I don't know what you mean by 'still did not work'. From your description,
it sounds like you want a checkbox, because it's a simple Yes/No. But you
then want to add some pre-determined value to your running sum (for
averaging) when the box is checked. To use a value in the Tag property, do
something like this:

If chkMyCheckBox = True Then
intSum = intSum + CInt(chkMyCheckBox.Tag)
End If

Carl Rapson

> Should I be using something other than a check-box (yes/no) data type
> then? I
[quoted text clipped - 32 lines]
>> > with
>> > that is calculating the average? Help!

Reply to this Message

Franklin - 08 Mar 2007 22:05 GMT

'Still did not work' meaning I was not getting a correct answer for the
average.

I am switching gears and trying something different. I changed the data
types of fields 1 - 8 to "Number", formatted them to 1 decimal place and
used the Lookup Wizard so that each field would only have 2 options; 1. A
value (i.e. 2.5, or 5, or 10) and 2. '0'.

However, when I am in Form view, although the drop down for fields 1 - 8
show two options, when I select a number with a decimal (2.5) it appears as
"2" in the control. If it would apply the ".5" it seems like the control
calculating the average would work, as it now reads 94.29%.

I have tried changing the Format to 1 decimal place in both the Table and in
property sheet in the Form. Help (again). Thanks!

> I don't know what you mean by 'still did not work'. From your description,
> it sounds like you want a checkbox, because it's a simple Yes/No. But you
[quoted text clipped - 44 lines]
> >> > with
> >> > that is calculating the average? Help!

Reply to this Message

Carl Rapson - 09 Mar 2007 16:59 GMT

You say you changed the field types to 'Number'; what did you select as the
Field Size? If it's Integer or Long Integer, you'll only get the integer
portion of your value. Setting the Field Size to Single or Double should
give you the complete value.

Carl Rapson

> 'Still did not work' meaning I was not getting a correct answer for the
> average.
[quoted text clipped - 13 lines]
> in
> property sheet in the Form. Help (again). Thanks!

<snip>

Reply to this Message

Franklin - 12 Mar 2007 20:11 GMT

I changed the field size and the expression/calculations work great.

However, I'm not sure how to compute for a null value. Each field has a
combo box control with 3 choices; a numerical value, a zero value, or a N/A
(does not apply) value. I cannot enter "N/A" as text, as it is set as a
number data type and would interfere with the numeric calculation. What value
can I set there instead?

And what would the expression be for this "if"/"then" situation? In essence,
if each numeric value is chosen, the expression adds them up and divides them
by the total, to get 100%. In some situations a "0" or "N/A" may be entered
for some of the fields.

I need an expression that would take into account any & all of these
situations & still come out with a viable percentage. Help (yet again)

> You say you changed the field types to 'Number'; what did you select as the
> Field Size? If it's Integer or Long Integer, you'll only get the integer
[quoted text clipped - 22 lines]
> >
> <snip>

Reply to this Message

Carl Rapson - 13 Mar 2007 16:22 GMT

There are a couple of things you could try. One is to equate the N/A case
with noselection in the combo box, and then check the combo box value like:

If cboMyControl.ListIndex = -1 Then
' Do whatever you want here for the Null case
Else
' Use the numeric value that is selected in the combo box
End If

Another thing you could try is to make your combo box unbound and add a
hidden textbox control that is bound to the numeric field in the table.
Then, in the AfterUpdate event of the combo box, you can put your numeric
value into the hidden control (or whatever value you wish if the N/A option
is selected). This way, you can have N/A as an option in the combo box with
no problem. Finally, use the hidden textbox control in your calculation.

Hopefully this will give you some ideas,

Carl Rapson

>I changed the field size and the expression/calculations work great.
>
[quoted text clipped - 50 lines]
>> >
>> <snip>

Reply to this Message

Franklin - 13 Mar 2007 17:22 GMT

It may be a bit more complicated. I will explain it in full.

I have eights fields in one section of the form. Each one has a combo box
with the following options:

Field1: 2.5, 0, "" (I am using a blank to designate a null value)
Field 2: 2.5, 0, ""
Field 3: 2.5, 0, ""
Field 4: 2.5, 0, ""
Field 5: 10, 0, ""
Field 6: 5, 0, ""
Field 7: 5, 0, ""
Field 8: 5, 0, ""

If a numerical value is chosen, that indicates the task was completed, 0
indicates that it was not, and "null" means that it was not applicable.

(Numerical values in this section sum up to 35)

In essence, each field is weighted differently. Now there are a number of
variations that can happen.

For example, let's say that fields 1 - 7 are applicable & field 8 is not,
but field 5 (a value of 10) was not completed, so it was given a "0" zero
value. This changes the total to 20 (adding fields 1,2,3,4,6,7) out of 30,
because field 8 was not applicable and thus given a null value. To get the
percentage, 20/30 = 66.6%.

Yet another example: only fields 7 & 8 were applicable and both were
completed for a total of 10. I would then need the expression to divide the
10 by 10 (the total value) which then gives me 100%.

Right now I have this in my control source:
=(Nz([Used appropriate greeting],0)+Nz([Tone of voice],0)+Nz([Responded to
inquiries],0)+Nz([Avoided interrupting caller],0)+Nz([Used Active Listening
skills],0)+Nz([Empathized appropriately],0)+Nz([Established
rapport],0)+Nz([Treated Caller with respect],0))/35

This obviously only works if they're all selected, as it divides by 35.

My question is how do I get the expression to account for any & all of these
variables?

Should I be looking into the "IIF" function?

> There are a couple of things you could try. One is to equate the N/A case
> with noselection in the combo box, and then check the combo box value like:
[quoted text clipped - 70 lines]
> >> >
> >> <snip>

Reply to this Message

Carl Rapson - 14 Mar 2007 16:24 GMT

I think what you need to do is create a function that does the calculation
and use that as your Control Source:

=MyFunction()

In that function do your calculation, referring to the combo box controls on
the form. You'll have much more flexibility in using or omitting your combo
boxes that you do by putting the calculation directly into the Control
Source.

Carl Rapson

> It may be a bit more complicated. I will explain it in full.
>
[quoted text clipped - 43 lines]
>
> Should I be looking into the "IIF" function?

<snip>

Reply to this Message