Lookup a query field with the latest date

[antioch]

Hi,



I'm struggling with the bold statements (Access VBA) below. I'm trying to store the most recent query data from the field name [HM Test] based on the latest date. For Step 2, I want to write the result "testSQL" in a table named [FundList] and in the field named [ID]. Does anyone know how I can fix the bold statement and add the Step 2? I would appreciate any help on this...thanks in advance!

Do Until rs.EOF
i = 1
DoCmd.OpenTable ("FundList")
strSQL = DLookup("[QueryID]", "FundList", "[num] = 1")

DoCmd.OpenQuery (strSQL)
testSQL = DLookup("[HM Test]", "strSQL", "[date] = latest date")

[STEP 2 GOES HERE]

i = i + 1

rs.MoveNext
Loop

[CJ_London]

date is a reserved word

and you should be getting a very clear error message when the code fails to run.

where does 'latest date' come from?

[antioch]

latest date should just be the row with the latest date. Since my data is quarterly, latest date should be the row with 6/30/17. i tried Dmax, but that didn't work either. Thanks.

[ssanfu]

I kinda understand what you are trying to do, but there are lots of errors in this code snippet...

    Do Until rs.EOF
        i = 1          '<<<--- i will ALWAYS = 1 because the variable i is inside the Do Loop. If rs loops through 1000 records, i will still be 1.

        DoCmd.OpenTable ("FundList")   '<<<-- If rs loops through 1000 records, table "FundList" will be opened 1000 times. (never closed??)
        
        strSQL = DLookup("[QueryID]", "FundList", "[num] = 1")  '<<-- strSQL will always be the same value, because 
                                                                      the DLookup criteria will always find the record where num = 1. 

        DoCmd.OpenQuery (strSQL)   '<<-- the variable strSQL is a value. Openquery requires an SQL string, not a value.  
                                         And if rs loops through 1000 records, the Openquery command would be executed 1000 times
        
        testSQL = DLookup("[HM Test]", "strSQL", "[date] = latest date")  '<<-- as stated by Ajax, "Date" is a reserved word and shouldn't be used as an
                                                                                object name. Somehow you need to get the value for latest date". 
                                                                                And the syntax would be wrong: if there is a variable for "LatestDate",
                                                                                syntax would be DLookup("[HM Test]", "strSQL", "[date] = #" & LatestDate & "#")

        [STEP 2 GOES HERE]

        i = i + 1

        rs.MoveNext
    Loop

STEP 2 depends on how you add a new record to a table. If there is a record set opened, would use "rst.AddNew".
Or could use an append query.