[RESOLVED w/ ADO .fields} Mistake somewhere in my loop???

Thank you everyone for the suggestions received... played with the code and ended up using .fields instead of a command (works faster too!!!)

With rs
.fields("name") = .fields("name") / .fields("name")
.update
etc.
etc.
end with

This works to calculate values in fields and put the answer into another field.



********************************
Good afternoon,

I am trying to calculate a value and put it into a field in an Access (2002) database via ADO and VB6. I had to create a loop to look at some fields first to determine if the calculation even needs to take place, then to figure out what field will be the basis for the calculation.

I got this same calculation to perform earlier but it was from one specific field and all the fields were numeric... these are not!

I cannot find my problem, however, I am new to this so goodness knows what silly thing I've done now. I have tried everything I can think of and am at my wits end, thus I'm begging (again!) for help from you wonderful gurus out here!!!!

I have attached a Word document that contains the specific code if anyone has a minute to look it over and offer any suggestions!!!

Thanks,
Mary

P.S. -- never mind, can't post Word....... converting to text......

I am sure that many people are willing to have a look into your code BUT please format the code correct. It is absolutly impossible to read the code because it is all in 1 line. Send just the project itself without table, that is enough.


Franky

You have to place all string values in between '' (single quotes)

try it out .

your code

VB Code:

'Open ADO connection
Dim SRT_DBcn As New ADODB.Connection
cnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\SRT_DB.mdb;Persist Security Info=False"
SRT_DBcn.Open cnString
 
Dim cmd As New ADODB.Command
Dim rsSurvey As New ADODB.Recordset
Dim SurveyRS As String
Let SurveyRS = NewSurvey & "_QstData"
'Open Survey Question data table
rsSurvey.Open SurveyRS, SRT_DBcn, adOpenKeyset, adLockOptimistic
DoEvents
With rsSurvey
    .MoveFirst
 
    Do Until rsSurvey.EOF
 
        'Look at Scale_Label (text field), if "--" then it does not require a bar, if has text other than -- it requires a bar
            If (rsSurvey!Scale_Label = "--") Then
                rsSurvey.MoveNext
                'If Scale_Label includes text other than -- it requires a bar
            ElseIf (rsSurvey!Scale_Label <> "--") Then
                'Look at Continuous_Recode, if "NO" then bar_value needs to be calculated, otherwise the calculation was done earlier
                If (rsSurvey!Continuous_Recode = "NO") Then
                    'if Response_1 is "--" and Response_2 has text then the calculation will be based on the value in "2" (text field)
                    If (rsSurvey!Response_1 = "--") And (rsSurvey!Response_2 <> "--") Then
                        cmd.CommandText = "UPDATE " & NewSurvey & "_QstData SET " & NewSurvey & "_QstData.Bar_Value = ROUND(val(" _
                        & NewSurvey & "_QstData.2) - " & NewSurvey & "_QstData.Min_Val) / (" & NewSurvey & "_QstData.Max_Val - " _
                        & NewSurvey & "_QstData.Min_Val)"
                        cmd.CommandType = adCmdText
                        SRT_DBcn.Execute cmd.CommandText
                        'if Response_2 is "--" and Response_1 has text then the calculation will be based on the value in "1" (text field)
                    ElseIf (rsSurvey!Response_2 = "--") And (rsSurvey!Response_1 <> "--") Then
                        cmd.CommandText = "UPDATE " & NewSurvey & "_QstData SET " & NewSurvey & "_QstData.Bar_Value = ROUND(val(" _
                        & NewSurvey & "_QstData.1) - " & NewSurvey & "_QstData.Min_Val) / (" & NewSurvey & "_QstData.Max_Val - " _
                        & NewSurvey & "_QstData.Min_Val)"
                        cmd.CommandType = adCmdText
                        SRT_DBcn.Execute cmd.CommandText
                        'if Response_2 is "--" and Response_1 is "--"  then the calculation will be based on the value in PR (text field)
                    ElseIf (rsSurvey!Response_1 = "--") And (rsSurvey!Response_2 = "--") Then
                        cmd.CommandText = "UPDATE " & NewSurvey & "_QstData SET " & NewSurvey & "_QstData.Bar_Value = ROUND(val(" & NewSurvey & "_QstData.PR) - " _
                        & NewSurvey & "_QstData.Min_Val) / (" & NewSurvey & "_QstData.Max_Val - " & NewSurvey & "_QstData.Min_Val)"
                        cmd.CommandType = adCmdText
                        SRT_DBcn.Execute cmd.CommandText
 
                    End If 'end looking at 1, 2, and PR
 
                End If  'end looking for all with Recode = "NO"
 
            End If ' end if for checking scale_label and BarValue for missing values - items without CR
 
        .MoveNext   'move to next record
 
    Loop
 
End With

I don't know which datatype is used for which field so can do no more for the moment

For calculations in your update statement you have to convert the non numeric fields.

Sorry Franky, I tried looking at the help to paste my code directly into the message so that it was readable, but didn't find the right info so I put it in a Word document and that wasn't accepted so I converted to text -- my apologies, I will make sure I do it right next time!! (The Word version looked great!!! LOL) Please forgive me.

Thanks Swatty for the suggestions, I will go try them now... what do you mean "convert" -- does "val" not work in SQL? Or convert the field type? I will go do a search on Convert...

Thanks both of you for taking the time to help -- I'll let you know how it turns out!