looping through recordsets for calculated fields

[enigma19]

Ok. I will definitely read it.

SmileyCoder.. Thanks a lot. You are very helpful :-)

[TheSmileyCoder]

I imported the excel sheet into a mdb, and named the table the same as you indicated in your original post.

I added an extra field to the table RLastQty and then used the following code to calculate the field:

Private Sub CalculateLast()
   Dim i As Integer
   Dim varFieldValue As Variant
   Dim strFieldName As String
   
   'Open Recordset
      Dim rs As DAO.Recordset
      Set rs = CurrentDb.OpenRecordset("tblItemOrdQty", dbOpenDynaset)
      If rs.EOF Then GoTo exitSub
   
   'Loop through records
      Do While Not rs.EOF
         'Reset storage
            varFieldValue = Null
            
         'Loop through fields in reverse
            For i = 10 To 0 Step -1
               strFieldName = "r" & Format(i, "00") & " Qty"
               If Not IsNull(rs(strFieldName)) Then
                  varFieldValue = rs(strFieldName)
                  'Found a value, so exit loop
                  Exit For
               End If
            Next
         
         'Write calculated value to field
            rs.Edit
               rs("RLastQty") = varFieldValue
            rs.Update
            
         'Move to next record
         rs.MoveNext
      Loop
   
   
exitSub:
   'Cleanup objects
   rs.Close
   Set rs = Nothing
   

   
End Sub

I have included the database sample here.

This calculates it and stores it in the table itself. Again not a normalized approach but since the table itself is not normalized I don't see the harm.

[enigma19]

TheSmileyCoder,

My Access Guru....Brillia nt!!!
This is exactly what i wanted.

I have one more question. Can i have the same result in a query? In your example you had designed a form with calculate cmd button. I would like to have it as a query with a calculated field, so that i can use this field in another query to calculate the difference between Quantity required and Quantity ordered. That would help me to reach the final objective.

Thanks :-)

Suggest me some good learning materials for this level of Access VB Programming. I am really interested.

[TheSmileyCoder]

Yes, that is possible as well. I will look into it this evening (if time permits)

[Rabbit]

This is a function I use to replicate the functionality of coalesce in SQL Server.

Public Function coalesce(ParamArray inputs() As Variant) As Variant
    Dim item As Variant
    coalesce = Null
    For Each item In inputs
        If Not IsNull(item) Then
            coalesce = item
            Exit Function
        End If
    Next
End Function

It returns the first non-null parameter passed into the function.

[TheSmileyCoder]

I presume you could just call the function that rabbit has provided and provide the inputs in reverse order.

LastQty: coalesce([r10 Qty],[r09 Qty]......[r00 qty])

If you are unsure on the basics of using a custom function in a query, check out the link below:
How To Write and use a Custom Function

[TheSmileyCoder]

enigma19
Suggest me some good learning materials for this level of Access VB Programming. I am really interested.

Well a good starting resource if you are new to programming is actually the "Access VBA Programming For Dummies". That is where I started, and look where I am now :)

[enigma19]

@Rabbit

Thank you for the reply.

I am a newbie in Access programming. I really have no idea have to use this piece of code to get the desired outcome.

I will go through the Coalesce function (its new to me) and try to understand the code.

[enigma19]

@SmileyCoder

I will go through the video. Thanks.
:-)

[TheSmileyCoder]

The video should give you all the information you need in order to implement Rabbit's solution & code.

If not, you know where to ask :)

[enigma19]

@SmileyCoder

Thank you for the video on using a function in a query.

I have used Rabbits' Coalesce function in the query. It is working great. Very simple solution ( though it took some time to fill in the array input values)

Thank you Guys.

I will get back to you all again for more help in my Access learning :-)

Keep Rocking

[TheSmileyCoder]

You are welcome.