update check box fields in the table using array based on certain value

[mls via AccessMonster.com]

One of my table has _Ct values and I want to update other relevant fields in
the same table ( basically check box) with ‘yes’ or ‘no’ depending on the _ct
values. Can some one show me how I can do this in Access and correct the
following logic?

Public Const ct = "H1_Ct H3_Ct H5a_Ct H5b_Ct RP_Ct swInfA_Ct swH1_Ct"
Public Const test = "H1_Tested H3_Tested H5a_Tested H5b_Tested RP_Testedâ€

Public Const result = "H1_Result H3_Result H5a_Result H5b_Result
RP_Result"

Dim objDB As DAO.Database
Dim mytbl As DAO.Recordset

Set objDB = CurrentDb()
Set mytbl = objDB.OpenRecordset("ABI_db")

Dim array_ct() As String
Dim array_t() As String
Dim array_r() As String
Dim i As Integer

array_ct = Split(ct, " ")
array_t = Split(Test, " ")
array_r = Split(result, " ")

For i = 1 To UBound(array_ct)
If array_ct(i) > 5 And array_ct(i) < 37.44 Then Do
array_t(i) = "1"
array_r(i) = "1"
End

Else: If array_ct(i) = 0 Or array_ct(i) > 37.44 Then Do
array_t(i) = "1"
array_r(i) = "0"
End

End

Next

Thanks

 

[PieterLinden via AccessMonster.com]

Public Const ct = "H1_Ct H3_Ct H5a_Ct H5b_Ct RP_Ct swInfA_Ct swH1_Ct"
Public Const test = "H1_Tested H3_Tested H5a_Tested H5b_Tested RP_Testedâ€

Public Const result = "H1_Result H3_Result H5a_Result H5b_Result
RP_Result"

Dim objDB As DAO.Database
Dim mytbl As DAO.Recordset

Set objDB = CurrentDb()
Set mytbl = objDB.OpenRecordset("ABI_db")

Dim array_ct() As String
Dim array_t() As String
Dim array_r() As String
Dim i As Integer

array_ct = Split(ct, " ")
array_t = Split(Test, " ")
array_r = Split(result, " ")

For i = LBound(array_ct) To UBound(array_ct) ' LINE CHANGED
If array_ct(i) > 5 And array_ct(i) < 37.44 Then ' No "Do"... it's
not a Do/Loop
array_t(i) = "1" ' are
you really storing numbers as text?
array_r(i) = "1"
ElseIf array_ct(i) = 0 Or array_ct(i) > 37.44 Then 'No Do here either.
array_t(i) = "1"
array_r(i) = "0"
End
Next i

 

[mls via AccessMonster.com]

Good question. All my array variable field values are Integers but field
names are character so how can I split them? When I run the following code
it shows Type mis-match error message.. I tried to declare them as variant
but it says user defined type not defined.

Dim array_ct() As Integer
Dim array_t() As Integer
Dim array_r() As Integer
Dim i As Integer

array_ct = Split(ct, " ")
array_t = Split(test, " ")
array_r = Split(result, " ")

For i = LBound(array_ct) To UBound(array_ct)
If array_ct(i) > 0 And array_ct(i) < 37.44 Then
array_t(i) = -1
array_r(i) = -1
ElseIf array_ct(i) = 0 Or array_ct(i) > 37.44 Then
array_t(i) = -1
array_r(i) = 0
End If
End
Next i