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

[A441OTA]

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......

[Franky]

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

[swatty]

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

try it out .

your 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.

[A441OTA]

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!