"Yes" "No" "N/A" conditional response

[Jen_RT]

Hi.

I'm relatively new to access and thought I might try here for some help.

I have created a form with 21 questions, all of which have a drop down menu where the user picks either "Yes" "No" or "N/A".

I have one final question at the end of the form and I think I need a sort of IF statement. I would like this field on the form to display NSS if any of the answers to the 21 questions was "Yes", if not, it should display "NNSS".

Any help would be greatly appreciated. Cheers.

 

[Galaxiom]

An enormous IIF is probably the first to mind but it is very clumsy.

Another is to Record Yes, No or N/A as numeric codes and sum the field values.
There is a pattern to the assigning of values that will reveal the count of the responses.

The simple case is the one used in the checkbox. True is -1 and False is zero. The number of true responses can be read as the absolute value of the sum.

For more complex situations set the value of each new response as at least:
(number of questions X the value of the previous response) + 1


For example with three responses and twenty questions:

N/A = 0, Yes = 1, No = 21

No matter how many Yes responses their total will not reach that of a single No response. The total for all responses as No will be 420 and every other combination of answers will lie in between zero and 420.

The expression for the number of No responses in this case is:
Int([TotalScore]/21)

For the number of Yes responses:
[TotalScore] MOD 21

(The MOD operator returns the remainder of the division)

You could use 0, 1 and 100. Then the number of responses can be read directly from the pairs of digits using the Mid function, or just Right() and Left() as appropriate, to parse the sum as a string.

Mid(Format([TotalScore], "0000"),3,2) will return the count of Yes

The total is simply:
TotalScore: Field1 + Field2 + Field3 etc

 

[Steve R.]

Create a public Boolean variable. Set it to "false". If anyone answers "yes" to a question, set the Boolean variable to "true". At the end of your process use the Boolean variable to determine whether to display "NNSS" on your form.

 

[Jen_RT]

Hi!

Thanks very much for your speedy responses. Got to leave work shortly to visit the dentist, but I'll be sure to post tomorrow to let you know how I get on.

Cheers

 

[CEH]

I think you could simplify Steve R's answer even further and skip the Boolean and just assign the value to your textbox......

Public Function FieldValidate()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
If ctl.Value = “1” Then ‘This assumes the value for answer of YES is 1 in your dropdown
Me.Textbox21 = “NSS”
Else
ME.Textbox21= “NNSS”
End If
End If
Next
End Function
'air code.... you might have to play with it........

 

[Galaxiom]

Public Function FieldValidate()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
If ctl.Value = “1” Then ‘This assumes the value for answer of YES is 1 in your dropdown
Me.Textbox21 = “NSS”
Else
ME.Textbox21= “NNSS”
End If
End If
Next
End Function

CEH's code will set the value of textbox21 based on the value of the last control tested. The If condition would need to account for the previous answers.

Indeed, once any value has been determined to be yes textbox21 could be set and the procedure exited since the values of the remaining boxes are irrelevant.

BTW. If the value of textbox21 is stored in a table it would be a breach of normalisation by setting a field based on the value of other fields.

 

[MSAccessRookie]

I think you could simplify Steve R's answer even further and skip the Boolean and just assign the value to your textbox......

Public Function FieldValidate()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
If ctl.Value = “1” Then ‘This assumes the value for answer of YES is 1 in your dropdown
Me.Textbox21 = “NSS”
Else
ME.Textbox21= “NNSS”
End If
End If
Next
End Function
'air code.... you might have to play with it........

Combining this code with what Steve was saying is the way to go. Although this code (as written) is close, galaxiom pointed out its shortcoming. Try the following rewrite.

Public Function FieldValidate()
    Dim ctl As Control
    Dim YesFound as Integer
 
    YesFound = 0
 
    For Each ctl In Me.Controls
        If ctl.ControlType = acComboBox Then
        'This assumes the value for YES is 1 in your dropdown
            If ctl.Value = "1" Then 
                YesFound = 1
            End If
        End If
    Next
 
    If YesFound = 1 Then 
        Me.Textbox21 = “NSS”
    Else
        ME.Textbox21= “NNSS” 
    End If
End Function

 

[CEH]

If called from the before Update of the form, why is it necessary to have the "YesFound" assigned a value?

 

[MSAccessRookie]

If called from the before Update of the form, why is it necessary to have the "YesFound" assigned a value?

It is necessary to assigned a value to "YesFound" regardless of what event the code is posted behind. The For Each...Next loop will set the value when required to do so. If you do not assign a value to "YesFound", then the value could be set and then reset back to no.

 

[CEH]

I noticed I left off one thing from my code.... The last line...

Public Function FieldValidate()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Then
If ctl.Value = “1” Then ‘This assumes the value for answer of YES is 1 in your dropdown
Me.Textbox21 = “NSS”
Else
ME.Textbox21= “NNSS”
End If
End If
Next
Set ctl = Nothing
End Function

Because I am still not following WHY the "YesFound" is even there.... Doesn't make sense to me... Your assigning a value, simply to assign another value based on the first value.. seems repetitive and unnecessary.
So tell me WHY it is necessary to have this "YesFound" assigned a value.
I use this code on field validation and noticed in the last DB I used it on I also did it with the value assigned to "Answer" and then populated a textbox from that. I removed it and went straight to the textbox...works fine. So again, WHY do it assigning the extra value? Is it doing something I am not seeing?
Hope I'm making sense

 

[Galaxiom]

I
Because I am still not following WHY the "YesFound" is even there.... Doesn't make sense to me...

The YesFound is there to make the function respond to all boxes, not just the last one. As I pointed out earlier, your version will only report the value of the last box tested.

I also pointed out that the function could set the value of text21 and exit as soon as one Yes response had been found. YesFound would not be necessary then.

 

[CEH]

Maybe I'm dense here but I don't follow....... Lets say I execute this code from a button.... Would it not with "For Each ctl In Me.Controls" start with the first control and then with the "Next" move to the second and so on? And it would set the value of Textbox21 = “NSS” only if it hits a "1"....Which it should. So I'm not following what you mean by "make the function respond to all boxes, not just the last one"... It's a loop right?

 

[Galaxiom]

Its a loop but if the last control tested is No then ctrl21 will be set NNSS regardless of the others.