Automatically Check a yes / no box depending on data in another field?

[Lisa Perry]

Hello,



I have a form (Access 2010) that we use to keep track of client data. When I enter a number equal to 6 or above in one field (Client Rating and this field is actually formatted as text because it could be 6 or 6a, b, c or 7 or 7a, b, c, etc…), I want anything =>6 to automatically check a box in another field which is the Watch Indicator box (a yes/no box) …how would I do this?

I tried to create an expression like the below on the Watch Indicator check box and it does not work - not sure why – it did not say it was incorrect, I think I may have it in the wrong place?

I used “After Update” on the Yes/No Box

=IIf([R Rating]<=6,Yes,No)

Any help would be appreciated. Thank you.

[rpeare]

Try using the ON EXIT property of your client rating field and set up something like

if(cint(left([Client_Rating],1)) >6 then
    me.TargetField = No
elseif (cint(left([Client_Rating],1)) <=6 then
    me.targetfield = Yes
else
    'do what you're going to do if client rating is missing
endif

where TARGETFIELD would be the field you want to be yes or no and this assumes that that field is a yes/no response field if you actually want a text value you'd have to enclose yes or no in "" marks.

[Lisa Perry]

Hello,

Thank you so much for your reply and please forgive me as I am not an advanced user - I tried using the code that you wrote and used my field names and it did not work ... something is missing - it says incorrect syntex. The first line if(cint...) was marked in red and the 3rd line was marked in red - I am assuming this is where the error is? I used On Exit on the R Rating Field .... any ideas? What does cint(left mean?

if(cint(left([R Rating],1)) >6 then
Me.[Watch Box Indicator] = No
elseif (cint(left([R Rating],1)) <=6 then
Me.[Watch Box Indicator] = Yes
Else
'do what you're going to do if client rating is missing
End If

End Sub

Again, thank you for any help, I truly appreciate it.

[rpeare]

ok can the first character of your [R Rating] be a character (anything non-numeric) or can it only start with a numeric value?

What I'm basically suggesting in the code (in pseudo code) is this:

if(cint(left([R Rating]),1) > 6 Then

If the left most character of the [R Rating] String is a numeric value higher than 6
(if you can have a non numeric character as your first character this formula would have to change but you didn't mention anything but numeric values in your post so I assumed it could ONLY be a numeric value in the first position of the field)

if(cint(left([R Rating]),1) <= 6 Then

If the left most character of the [R Rating] String is a numeric value less than or equal to 6

the left function lets you parse a string for a specific set of characters
the cint function changes a string into an integer (this part would fail if you attempt to change a text string to a numeric value that wasn't composed of numbers)

However, my original code was messed up it has too many opening brackets in it try this:

if cint(left([R Rating],1)) >6 then
         Me.[Watch Box Indicator] = No
elseif cint(left([R Rating],1)) <=6 then
         Me.[Watch Box Indicator] = Yes
Else
    'do what you're going to do if client rating is missing
End If

[ssanfu]

Just for fun, here is my version of the code.
It doesn't matter if the number is before or after the letter ("6A" or "A6")

Private Sub R_Rating_AfterUpdate()
   Dim i As Integer
   Dim tmp As String
   Dim sString As String

   'get value from control
   sString = Trim(Me.[R Rating] & "")

   ' get only numbers from string
   'Asc(48) = 0
   'Asc(57) = 9
   For i = 1 To Len(sString)
      If Asc(Mid(sString, i, 1)) >= 48 And Asc(Mid(sString, i, 1)) <= 57 Then
         tmp = tmp & Mid(sString, i, 1)
      End If
   Next

   'check for missing rating
   If Len(tmp) = 0 Then
      Me.Check1 = False
      MsgBox "warning!! Missing Rating"
   Else
      'set check box value
      Me.Check1 = (Val(tmp) >= 6)
   End If

End Sub

[Lisa Perry]

Hello again,

I tried the code below and it did not work. The field is text - not number as I have to be able to type 6 or 6a, 6b, etc. I put the code in just as you have it on the R Rating On Exit and it did not give me any error but when I enter an 8 or above it does not check the Watch List box. I am truly sorry if I am not explaining this correctly.

Thoughts?

This is what I put in...

Private Sub Risk_Rating_Exit(Cancel As Integer)
If CInt(Left([R Rating], 1)) > 6 Then
Me.[Watch Box Indicator] = No
ElseIf CInt(Left([R Rating], 1)) <= 6 Then
Me.[Watch Box Indicator] = Yes
Else
'do what you're going to do if client rating is missing End If



End Sub


Thank you.

Lp

ok can the first character of your [R Rating] be a character (anything non-numeric) or can it only start with a numeric value?

What I'm basically suggesting in the code (in pseudo code) is this:

if(cint(left([R Rating]),1) > 6 Then

If the left most character of the [R Rating] String is a numeric value higher than 6
(if you can have a non numeric character as your first character this formula would have to change but you didn't mention anything but numeric values in your post so I assumed it could ONLY be a numeric value in the first position of the field)

if(cint(left([R Rating]),1) <= 6 Then

If the left most character of the [R Rating] String is a numeric value less than or equal to 6

the left function lets you parse a string for a specific set of characters
the cint function changes a string into an integer (this part would fail if you attempt to change a text string to a numeric value that wasn't composed of numbers)

However, my original code was messed up it has too many opening brackets in it try this:

if cint(left([R Rating],1)) >6 then
         Me.[Watch Box Indicator] = No
elseif cint(left([R Rating],1)) <=6 then
         Me.[Watch Box Indicator] = Yes
Else
    'do what you're going to do if client rating is missing
End If

[rpeare]

You aren't answering the fundamental question I've been asked in a couple of posts now.

The field you are examining, does it ALWAYS start with a numerical figure? I do not care if the field itself is a text value, I'm talking about the very first character, is it ALWAYS a digit?

If it's not my code will not work. If it is it will.

[Lisa Perry]

Forgive me, yes it is always a digit first...the message I get is this...

Compile Error
Block If without End If

Does this help?

Lp

[rpeare]

You don't have an END IF on your statement

Private Sub Risk_Rating_Exit(Cancel As Integer)
If CInt(Left([R Rating], 1)) > 6 Then
Me.[Watch Box Indicator] = No
ElseIf CInt(Left([R Rating], 1)) <= 6 Then
Me.[Watch Box Indicator] = Yes
Else
'do what you're going to do if client rating is missing End If
END IF


End Sub

If you don't close your if statement you'll get that error every time (this is why I always indent code to make it easier to see the loops)

[Lisa Perry]

Well, I added exactly what you have below and it did not work it gave me another error message pointing to the 3rd line of the code. I really don't understand code so it is hard for me to determine the problem and I have wasted enough of your time. Thank you trying to help me, I appreciate it.

[rpeare]

make a copy of your database, put some junk data in it and upload it to this site, tell me what form you're working with as well and I'll have a look