Nice code orange.
I was on hold for a long while (seemed like forever)..... and, well, I modified your code a little. Apologies...
Code:
Option Compare Database
Option Explicit
Public Const ct = "A1_Ct,A2_Ct,A3_Ct,Up1_ct,Up2_ct,PI1_Ct,PI2_Ct"
Public Const st = "A1,A2,A3,Up1,Up2,PI1,PI2" '<<- same as array ct, but without the "_ct"
Sub arr_update()
10 On Error GoTo arr_update_Error
Dim strHold As String
Dim ObjDB As DAO.Database
Dim mytbl As DAO.Recordset
Dim Array_Ct
Dim Results As String
Dim i As Integer
Dim Array_St
Dim MinVal As Double '<<- variable for min value
Dim MaxVal As Double '<<- variable for max value
Dim AllZero As String '<<- variable for all zero values
20 Set ObjDB = CurrentDb()
30 Set mytbl = ObjDB.OpenRecordset("TP_Test")
40 Array_Ct = Split(ct, ",")
50 Array_St = Split(st, ",")
'-----------------------------------------
60 MinVal = 0
70 MaxVal = 35.99
80 AllZero = "- All Zeros -" '<<- text to display if all values are NULL/Empty or 0
'-----------------------------------------
90 Do While Not mytbl.EOF
100 For i = 0 To UBound(Array_Ct)
110 Debug.Print Array_Ct(i) & " " & mytbl.Fields(Array_Ct(i))
120 If (mytbl.Fields(Array_Ct(i)) > MinVal And mytbl.Fields(Array_Ct(i)) <= MaxVal) Then
130 strHold = strHold & Array_St(i) & ", " '<<- added a training space to separate value after comma
140 Debug.Print i & " " & Array_St(i) & " " & strHold
150 End If
160 Next i
170 Debug.Print "Len strhold " & Len(strHold)
'---------- Update Result field ---------------------------
180 mytbl.Edit
190 If Len(strHold) > 0 Then
200 strHold = Left(strHold, Len(strHold) - 2) '<<- remove trailing comma & space
210 Else
220 strHold = AllZero
230 End If
240 mytbl!result = strHold
250 Debug.Print "New record result---> " & strHold
260 mytbl.Update
'---------- Update Result field ---------------------------
270 strHold = ""
280 mytbl.MoveNext
290 Loop
300 On Error GoTo 0
' ---- Clean up ----
310 mytbl.Close
320 Set mytbl = Nothing
330 Set ObjDB = Nothing
340 MsgBox "Done"
350 Exit Sub
arr_update_Error:
360 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure arr_update, line " & Erl & "."
End Sub