Access VBA Loop through a table and get data for certain records

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I want to loop through an Access table automatically to check if a field called "Registration Date" is 7 or fewer days away.
What VBA code would I use to do this?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NoahHardware Tester and Debugger

Commented:
Hi there! :)

Do you have an example file you can attach?
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Hi. See example attached
Software & Systems Engineer
Commented:
Dim rst as DAO.Recordset
set rst = currentDB.OpenRecordset(NameOfYourTable)
With rst
While Not .EOD
if .Fields("Registration Date") <=7 then
' Some Custom Action
End if

.MoveNext
Wend
End With

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Use DLookup  to check for the existence of an ID within the last 7 days:

Dim RecentRegistration As Boolean

RecentRegistration = Not IsNull(DLookup("[ID]", "[YourTable]", "DateDiff('d', [Registration Date], Date()) <= 7"))

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks that worked in combination
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
What is the purpose of your loop?

Gustav recommended using DLOOKUP(), with will return the first record that matches your critieria.

But a DCOUNT would actually identify the number of records which match the criteria.  If that value is greater than zero and you want to do something with that data, you could either build a recordset with that criteria in a SELECT statement and use that in a report or to accomplish whatever task you are looking for.

HTH
Dale
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
The purpose of the loop is to look at the expiry date field and compare it to today's date. So I used the DateDiff function in Gustav's post inside the loop from John
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Murray,

What I was getting at is your purpose (what are you doing inside the loop, after you identify whether the record meets your 7 day criteria)?

You may not need a loop (generally slow) to do what you are looking for.   And rather than looping through an entire recordset to identify records that meet your criteria, it  might be significantly more efficient to create a recordset that is based on that criteria.

SELECT * FROM yourTable WHERE DateDiff("d", [SomeDateField], Date()) < 7

HTH
Dale
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks Dale

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial