Breaking Date Field by 3 textfields for "filter by form"

Posted on 2012-08-15
Last Modified: 2012-08-19
I have a form in Access, where I do "Filter By Form".
No issues there...but I'm being asked to enhance it.

One of the fields is a date field.  To search a "Month", I put in

Between #1/1/2011# and #1/31/2011#

To get January 2011.

Can I split the date field textboxes up to 3 fields, and still keep control with a single date filed, while searching by the 3 fields?  This way, if I do a "Filter by Form" I can use a textbox or combo box on it?

Question by:Evan Cutler
    LVL 65

    Expert Comment

    by:Jim Horn
    Perhaps a better idea would be to have a table of months, say tbl_months, with columns of txt_formal_name (i.e. January 2011), dt_start (i.e. 1/1/2011 12:00 AM), and dt_end (i.e. 1/31/2011 11:59:59 PM).

    Then, create a combo box with the txt_formal_name column visible, and everything else.

    Then, when the user selects a value, pass along the dt_start and dt_end columns in your 'Between' expression.
    LVL 9

    Author Comment

    by:Evan Cutler
    Another thought is to repeat the field three times, then just filter out the value partial I want for each one.

    Can I do that?
    LVL 74

    Accepted Solution

    You can create a query that pulls out the 3 date components.
    Something like this:

    SELECT YourTable.OrderID, YourTable.CustID, YourTable.OrderDate, YourTable.Price, DatePart("yyyy",[OrderDate]) AS OrderYear, DatePart("m",[OrderDate]) AS OrderMonth, DatePart("w",[OrderDate]) AS OrderDay
    FROM YourTable;

    Then create your form from that.
    Then you can use Filter by form to filter on any combination on the 3 date component fields.
    LVL 9

    Author Closing Comment

    by:Evan Cutler
    This is a good solution...I will work towards my goal with this solution.

    My issue is that if I adjust the DatePart, the date field does not get updated....
    but it's a good start. :)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 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

    13 Experts available now in Live!

    Get 1:1 Help Now