How do I retrieve multiple records from a VBA Query - I only retrieve 1 record

[Michael G]

LSQL = "select NDC" & "," & "Quantity" & "," & "Date1 from Invoice where Date1 between #" & datefrom & "# and  #" & dateto & "#"
 
        Set Lrs = db.OpenRecordset(LSQL)
               'Retrieve value if data is found
        If Lrs.EOF = False Then
            getndc = Lrs("NDC")
                          getqty = Lrs("Quantity")
                       getdate1 = Lrs("Date1")
                         MsgBox (getndc & ",  " & getqty & ",  " & getdate1)
 
        Else
            getndc = "Not found"
        End If
                Lrs.Close
        db.Close
         Set Lrs = Nothing

I created a table with date ranges from 7/1/31 to 10/1/56
but the msgbox retrieves the 1st matching record and then stops

[Michael G]

what I did to retrieve more than one was re-write to this:

Dim datecount As Date
For datecount = datefrom To dateto
        'Create SQL statement to retrieve value from GST table
        LSQL = "select NDC" & "," & "Quantity" & "," & "Date1 from Invoice where Date1 =" & "#" & datecount & "#" & " AND NDC =" & ndc1
 
 
 
 
 
        Set Lrs = db.OpenRecordset(LSQL)
 
 
        'Retrieve value if data is found
        If Lrs.EOF = False Then
            getndc = Lrs("NDC")
 
            getqty = Lrs("Quantity")
 
            getdate1 = Lrs("Date1")
 
             MsgBox (getndc & ",  " & getqty & ",  " & getdate1)
 
        Else
            getndc = "Not found"
        End If
        Next datecount
 
        Lrs.Close
        db.Close
         Set Lrs = Nothing

However it take a while to run through all the dates

[Rabbit]

That's because you're creating a recordset for each date. Instead, use one recordset and loop through the recordset.

[TheSmileyCoder]

As rabbit said, loop through the records.
Example shown below:

Do while not LRS.EOF
   'Your Code
 
   LRS.MoveNext
Loop

[Michael G]

I thought my
if
end if loops
but I will definitely gives this a try
Thanks
I'll let you know how it works :D


-------------------
Awesome works great!!!!
Thanks you so much !!!!!!!!!!!!