Results 1 to 4 of 4

Thread: Filter Subform

  1. #1
    Join Date
    Oct 2003

    Unanswered: Filter Subform

    I would like to filter a subform using a combo box. I currently have a form with an option box with three options and opens different subforms depending on what option they choose. I want to be able to filter the data by months. For example if they only want to see the records for the last three months, it would only show those records. I'd want five options in the combo box Last Three months, Last Two months, Last Month, Due This Month and Due Next Month.

    My ultimate goal is to group these records by month so if there is a better solution please let me know.

  2. #2
    Join Date
    Nov 2003
    Sounds simple enough...and if that's the look and feel you want then try it out. I would add another item to the mix though.

    Because you want to view the records grouped in Months, I would place a combo on each subform so that a specific query of several months can be viewed month by month. Only the months queried would be displayed within the combo (in the instance of Last Three Months for example).

    Self Taught In ALL Environments.....And It Shows!

  3. #3
    Join Date
    Feb 2004
    Chicago, IL
    I am not sure I completely understand what you want, but if you base your subforms on query(ies) and add a calculated field uisng the Month() function you could filter based on this field.

    Month(Date()) will return a number of the month (run today returns 8). So if you wanted the last three months you could use (assume your calculated field is called InvoiceMonth):

    InvoiceMonth > (Month(Date())-3) And InvoiceMonth < (Month(Date))

    Of course the Month() function will help with grouping as well.

  4. #4
    Join Date
    Oct 2003
    Thanks, I'm still learning how to use these types of expressions so please bare with me.

    Do both these expressions go on the query or on the subform?
    I tried both and I get the same results the Month(Date()) option works but I can't get the second one to work. This what I have on the subform I have two text boxes one is named txtTodaysMonth with the control source as =Month(Date()).
    The second txt box with the following in the control source =[txtTodaysMonth]>(Month(Date())-3) And [txtTodaysMonth]<(Month([6MonthCheck])). The results in the box is 0.

    Just for clarification on what I would like to do is:
    I have to do employment verification checks every 6 months, 1 year and 2 years. I have an option box with the above three choices and it pulls up the appropriate subform with all who need checks and because we are behind it shows everyone from the beginning of time and I only want to see up to the last three months. I'll just run a report for the others. I want the user to be able to choose from a combo box or more efficient method to filter the selection by month up to the last three months. I want the use to see the month name and not the number. I have a field already that shows the month name but not sure how to work it into this. Hopefully this makes a little bit more sense.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts