• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Qury between 2 dates

I have a fiedl on a form, I wan to return all the records 21 days before that day

I gave tried <=Date() -21

and load others but can't get it working

Any ideas
1 Solution
if FutureDate - getdate () <= 21 ...
BrogrimAuthor Commented:
I don't understand

I want all the records starting at the form field date and 21 days before that
Patrick MatthewsCommented:
Assuming this is Access, then you will need something like this in the WHERE clause of your query:

WHERE DateInTheTable <= (CDate(Forms![NameOfForm]![NameOfDateField]) - 21)

Open in new window

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

BrogrimAuthor Commented:
tried that with this SQL not working

SELECT tblddai_renewalpostdate.daterenewalposted
FROM tblddai_renewalpostdate
WHERE (((tblddai_renewalpostdate.daterenewalposted)=([Forms]![frmDDAI_SB]![txtStartDate])-21));
Kevin CrossChief Technology OfficerCommented:
Note you did not wrap the field in CDate() as shown by Patrick. Additionally, to be more explicitly in your intention, you may want to consider using DateAdd() (ref: http://www.techonthenet.com/access/functions/date/dateadd.php). It is better to explicitly convert to date using CDate().

DateAdd("d", -21, [Forms]![frmDDAI_SB]![txtStartDate])

That may implicitly convert the date field to a date anyway. Not sure you want = also. You mention <= in question.
BrogrimAuthor Commented:
Kevin CrossChief Technology OfficerCommented:
You are most welcome! Note my comment was in support of Patrick's regarding CDate(). If that is what solved the issue, then please know you won't offend me any by requesting a Moderator change this to a split or credit to Patrick. If the DateAdd() is what did it, then glad that helped!

Best regards and happy coding,


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now