如何在Access VBA中简化查询创建日期超10天且不超20天记录的代码

First, let's clarify what your original code is doing: it's fetching all records where the created_date is between 11 and 20 days old (since DateDiffValue >10 means more than 10 days have passed, and <21 means fewer than 21 days).

Your current SQL attempt has a logical error—you're comparing created_date (a date value) to the result of DateDiff (a numeric value), which doesn't make sense. Let's fix that with two clean, efficient approaches:

Option 1: Use DateDiff Directly in the WHERE Clause (Clear and Readable) #

This approach mirrors your original logic but moves the date check into the SQL query itself, eliminating the need for a VBA loop:

Set rs = db.OpenRecordset("SELECT * FROM COMPUTER_LIST WHERE DateDiff('d', created_date, Date()) > 10 AND DateDiff('d', created_date, Date()) < 21")

This tells Access to calculate the day difference for each record and return only those where the difference falls between 11 and 20 days.

Option 2: Use Date Ranges (Better for Performance) #

If you have an index on the created_date field, using date ranges will let Access leverage that index to speed up the query (instead of calculating DateDiff for every record). Here's how:

Set rs = db.OpenRecordset("SELECT * FROM COMPUTER_LIST WHERE created_date > DateAdd('d', -21, Date()) AND created_date < DateAdd('d', -10, Date())")

Let's break this down:

• DateAdd('d', -21, Date()) gives you the date exactly 21 days ago
• DateAdd('d', -10, Date()) gives you the date exactly 10 days ago
• We're selecting records where created_date is after 21 days ago but before 10 days ago—exactly the 11-20 day window you want.

Why Your Original Attempt Didn't Work #

Your query was comparing created_date to Datediff('d', created_date,Date()), which is a number (like 15 for a record 15 days old). Comparing a date to a number will always give unexpected results, which is why that approach failed.

Either of the two options above will replace your entire loop with a single, efficient SQL query—no need to iterate through every record in VBA!

内容的提问来源于stack exchange，提问作者user2328935