I want to copy a date field values into 5 fields. How?

[stalk]

I want to copy the date_tested field value into all the array_dt fields? mytbl.Fields(array_dt(i)).Value = mytbl.Fields(Date_Tested).Value



Is the logic below correct. I 'm getting error message.

Public Const ct = "InA_Ct,InB_Ct,H1_Ct,Hx_Ct, RP_Ct"
Public Const Dt = "InA_Date,InB_Date,H1_Date,Hx_Date,RP_Date"
Public Const result = "InA_Result,InB_Result,H1_Result,Hx_Result,RP_Result"
 Sub arr_update()
    Dim objDB As DAO.Database
    Dim mytbl As DAO.Recordset
    Set objDB = CurrentDb()
    Set mytbl = objDB.OpenRecordset("Temp_RESULTS")
     Dim array_ct, array_dt, array_r, i
    Dim result_reported As String
    array_ct = Split(ct, ",")
    array_dt = Split(Dt, ",")
    array_r = Split(result, ",")
    For i = 0 To 4
    While Not mytbl.EOF
    mytbl.Edit
     mytbl.Fields(array_dt(i)).Value = mytbl.Fields(Date_Tested).Value
    If (mytbl.Fields(array_ct(i)).Value > 0 And mytbl.Fields(array_ct(i)).Value <= 32.99) Then
       mytbl.Fields(array_r(i)).Value = 1 'Positive
    ElseIf (mytbl.Fields(array_ct(i)).Value = 0) Then
       mytbl.Fields(array_r(i)).Value = 0 'Negative
    ElseIf mytbl.Fields(array_ct(i)).Value = Null Then
       mytbl.Fields(array_r(i)).Value = 9 'Not tested
        End If
    mytbl.Update
    mytbl.MoveNext
Wend
mytbl.MoveFirst 
Next I
 End Sub

[RuralGuy]

Two issues: Dimming an Array and multiple dim's on a line.
1) You Dim an Array with Dim array_ct() etc.
2) From the second element on is Dimmed as a Variant unless specified.
My guess is you really wanted:
Dim array_ct()
Dim array_dt()
Dim array_r()
Dim i as Integer

[stalk]

Dim is not the problem here. array_dt should be text field and date_tested is date field. all the Date fields I want should be in text data type. That is the error I'm getting .

Can some one check the why I could not read Dat_tested value into my other ~_Date fields( InA_date, InB_date...)

Two issues: Dimming an Array and multiple dim's on a line.
1) You Dim an Array with Dim array_ct() etc.
2) From the second element on is Dimmed as a Variant unless specified.
My guess is you really wanted:
Dim array_ct()
Dim array_dt()
Dim array_r()
Dim i as Integer

[Micron]

Always provide the error number/message and the line causing the error to minimize our guessing. I've never used .Value to set a field value, so I had to look it up. Found out it's the default property (that's why I've never used it) but I did come across this statement

Trying to set or return the Value property in an inappropriate context (for example, the Value property of a Field object in the Fields collection of a TableDef object) will cause a trappable error.

here: https://msdn.microsoft.com/EN-US/lib.../ff195493.aspx
Your recordset seems to be based on a table, so I thought the statement there might be relevant.

I don't see a problem with your Dim statement except for i - which you have created as a variant. The fact that you're using it as an integer or long may not be the problem. "array_dt" may be a text field somewhere (as you state), but in your code, it is an objectd variable of the variant type.
Also, I have seen table recordset loops start in the middle of a table when MoveFirst was not executed, which you are not. Thus you might be trying to MoveNext (just before the Wend) when you are at EOF because your moves are being done inside a counter loop. You would be trying to move next regardless of the EOF. I would test for no records first (there's more than one way) as in

If Not (rs.BOF And rs.EOF) Then
  rs.MoveFirst
  **you can set or Dim or whatever, anything else. The counter goes in here. The beginning the inner loop:
Do While Not rs.EOF
...more code
Loop
End If

You could also entertain the idea of For i = 0 to Ubound(array_ct) as opposed to declaring constants. Should you add a field to this later, it's more work to modify your constants. Again, no idea of the error you're getting, so I'm left to guess.

[ssanfu]

Your naming convention (or lack of one) makes it hard to decipher what is what.

It looks like you have a table (or query) named "Temp_RESULTS" with at least 16 fields.
For the fields "InA_Date", "InB_Date", "H1_Date", "Hx_Date", "RP_Date", are the field types "Date" or "Text" types?
The field "Date_Tested" is a Date type field?
Does the field "Date_Tested" have values (dates) in every record?


If would really help if you would post your dB for analysis...



There is another problem. You CANNOT write a test like this:

            ElseIf mytbl.Fields(array_ct(i)).Value = Null Then
                mytbl.Fields(array_r(i)).Value = 9    'Not tested
            End If

The reason is this: Nothing is equal to NULL, not even Null.
In the immediate window enter

? 1=1

then press enter. The result is TRUE
Enter

? Null = Null

The result is FALSE.


You have to use the function IsNull() or the function NZ():

            ElseIf IsNull(mytbl.Fields(array_ct(i)).Value) Then
                mytbl.Fields(array_r(i)).Value = 9    'Not tested
            End If

            ElseIf NZ(mytbl.Fields(array_ct(i)).Value, 0) = 0 Then
                mytbl.Fields(array_r(i)).Value = 9    'Not tested
            End If