Results 1 to 7 of 7

Thread: VBA Loop Help

  1. #1
    Join Date
    Mar 2009
    Posts
    4

    Unanswered: VBA Loop Help

    I have enougk understanding of VBA to be dangerous and thankfully I only use it help make my tasks easier and not dependent on any type of real development. With that said I would like to get some input from you fine people on something I would like to accomplish.

    I want to loop through a table of records until a condition is met.
    Once the condition is met I want to capture the value and use it as part of my update query. I am thiniking it will look something like this.... Please do not laugh at me too hard .

    ' Dim dbs As Database
    ' Dim rst As Recordset
    ' Dim correct_date As Date ' How do I make this as mm/yy?

    ' Set dbs = CurrentDb
    ' Set rst = dbs.OpenRecordset("Select * FROM [Table];")

    ' Loop Until condition is met
    ' value from record set is > then Now()(by month) in mm/yy format

    ' If Value is Yes capture what that field is

    ' Then use that value in my Update SQL
    'Update [table].[field] to correct_date where [other conditions];

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Off the top of my head (untested):

    Code:
    Dim dbs As Database
    Dim rst As Recordset
    Dim correct_date As Date
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Select * FROM [Table];")
    
    rst.movefirst
    
    do while not rst.eof
        'assuming date is first field in recordset (0 based)
        if (year(rst.fields(0)) + (month(rst.fields(0)) - 1)*12) > (year(date) + (month(date) - 1)*12) then
            correct_date = rst.fields(0)
            exit do
        end if
        
        rst.movenext
    loop
    
    'Update [table].[field] to correct_date where [other conditions];
    Also, it's a good idea to explicitly specify ADO or DAO recordset type.
    Me.Geek = True

  3. #3
    Join Date
    Mar 2009
    Posts
    4
    Thanks nckdryr I will make sure I do that.

    Anyone else have any insight on how to capture the correct value in a variable and use it in my update statement.

    Thanks again the input.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why not use a single bit of SQL to find the first value which is greater than or equal to the specified value

    select top 1 my,column,list from mytable where mydatecolumn >= mytargetdate

    or id you must use mm/yy (presuming that means you are storing dates as strings... yuck

    select top 1 my,column,list from mytable where format(mydatecolumn,"mm/yy") >= format(mytargetdate,"mm/yy")
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Healdem is right, you won't need a loop to achieve this, just soem cunning SQL

    Explain your scenario in a bit more depth providing the table definitions and some sample data along with desired output and we'll see what's what!
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2009
    Posts
    4
    Thanks a million for the help everyone!

    Specifics on what I am trying to capture.

    Table 1 - Has a blank date field that I want populated based on the following condition (if date is < First date in Table 2.Date then insert that date, else compare the 2nd date, 3rd etc until the condition is true.

  7. #7
    Join Date
    Mar 2009
    Posts
    4
    Wanted to do a self bump to see if anyone had any insights.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •