Qury between 2 dates

Posted on 2011-10-25
Last Modified: 2012-05-12
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
Question by:Brogrim
    LVL 6

    Expert Comment

    if FutureDate - getdate () <= 21 ...

    Author Comment

    I don't understand

    I want all the records starting at the form field date and 21 days before that
    LVL 92

    Expert Comment

    by:Patrick Matthews
    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


    Author Comment

    tried that with this SQL not working

    SELECT tblddai_renewalpostdate.daterenewalposted
    FROM tblddai_renewalpostdate
    WHERE (((tblddai_renewalpostdate.daterenewalposted)=([Forms]![frmDDAI_SB]![txtStartDate])-21));
    LVL 59

    Accepted Solution

    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: 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.

    Author Closing Comment

    LVL 59

    Expert Comment

    by:Kevin Cross
    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,


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now