Updating a single column with multiple columns via VBA

[kwvh]

Lost in the trees again. This should be relatively simple, but I can't get there. I have a table with several columns. This table layout changes periodically based on needs. A spreadsheet is created out of another software package. That spreadsheet may have new columns in it. The spreadsheet is imported into a new table in Access, let's call it "tmpHoursOut". there is a lookup table, let's call it tlkpA_PayCode. The tlkpA_PayCode contains a list of the columns in the tmpHoursOut that would be included in an update to section of a query. The user can go into the lookup table and add, edit or delete records. Those records are used to determine which columns to include in summing the data to place in the single column, Total_Hour.

Below is the code. The strUpdateTo variable does get populated with the correct string, but I get an error stating "Data type mismatch in criteria expression."

Code: Select all

    

    strSQL = "SELECT tlkpA_PayCode.Code " & _
            "FROM tlkpA_PayCode " & _
            "WHERE (((tlkpA_PayCode.[Include in OA?])='Y'));"
    strUpdateTo = ""
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
        testor = 0
        Do While rst.EOF = False
            testor = testor + 1
            If testor > 1 Then
                strUpdateTo = strUpdateTo & " +" & "[" & rst!code & "]"
            Else
                strUpdateTo = "[" & rst!code & "]"
            End If
            rst.MoveNext
        Loop
    rst.Close
    Set rst = Nothing
    strUpdateTo = Chr(34) & strUpdateTo & Chr(34)
    
            ' Loop through the lookup table to populate the UpdateTo section of the query
        DoCmd.RunSQL "UPDATE tmpHoursOut SET tmpHoursOut.Total_Dist_Hour = IIf((" & strUpdateTo & ") >0," & strUpdateTo & ",0) ;"

There is no criteria in the query, so I assume it is in the populating of the strUpdateto variable that is the problem. But I am stumped.

Any ideas?

[HansV]

Temporarily change the last part of the code to

Code: Select all

    ' Loop through the lookup table to populate the UpdateTo section of the query
    strSQL = "UPDATE tmpHoursOut SET tmpHoursOut.Total_Dist_Hour = IIf((" & strUpdateTo & ") >0," & strUpdateTo & ",0) ;"
    Debug.Print strSQL

When you run the code, the SQL string will be displayed in the Immediate window in the Visual Basic Editor. Does it give you a clue? If not, copy it and paste it into a reply.

[kwvh]

Hans,

You are a brilliant teacher. Displaying it in the Immediate window made it very obvious what was wrong. Putting the quotes around the strUpdateTo variable was the problem. I don't know why I did that.

THANKS again as usual.

Respectfully,

Ken