[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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

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?

Evan Cutler
Evan Cutler
  • 2
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
Evan CutlerAuthor Commented:
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?
Jeffrey CoachmanCommented:
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.
Evan CutlerAuthor Commented:
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. :)


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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