Solved Filter Dates by 1 month from today, 2 months from today etc (1 Viewer)

Lochwood

Registered User.
Local time
Yesterday, 17:00
Joined
Jun 7, 2017
Messages
105
I am looking to filter a date field by month based on a form. I have a combo box with 1 Month, 2 Months, 3 Months, 6 Months, 12 Months.

If i choose 1 month on the form i would like all records from todays date to next month.
If i choose 2 months on the form i would like all records from today upto 2 months and so on.

Not sure if im on the right track but heres what ive done so far. Created a query that labels each month based on the programmed_end

Calculated Query
Date_Period: IIf([Programmed_End]<Date(),"Expired") & IIf([Programmed_End] Between Date() And Date()+30,"1 Month") & IIf([Programmed_End] Between Date()+31 And Date()+61,"2 Months") & IIf([Programmed_End] Between Date()+62 And Date()+92,"3 Months") & IIf([Programmed_End] Between Date()+93 And Date()+182,"6 Months") & IIf([Programmed_End] Between Date()+183 And Date()+365,"12 Months")

so each date has a calculated field showing if its within a month or 2 months or 3 etc. (GREAT). Now if my form shows "2 months", how can i get the records to show me all of 1 month and all of 2 months together. at the moment i can only get the form to show me that single month selected.

Query Criteria to Combobox
Expr6: IIf([Forms]![Mainform]![Combo50]="*",True,[Date_Period] Like [Forms]![Mainform]![Combo50] & "*")

Any help would be a godsend
 

Ranman256

Registered User.
Local time
Yesterday, 20:00
Joined
Apr 9, 2015
Messages
3,643
Make a form,with 2 date boxes: txtStartDate, txtEndDate.
When you change the combo (or txtBox of days) ,then reset the end date box.

The query need only use: between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:00
Joined
Sep 21, 2011
Messages
7,172
I would have a combo with
Code:
1    1 Month
2    2 Month
3    3 Month
etc, and then use DateAdd() to add the first column for the variable date.?
You could even strip off the digit from the text showing and use that.?
 

Lochwood

Registered User.
Local time
Yesterday, 17:00
Joined
Jun 7, 2017
Messages
105
In the end ive gone with RanMans Pointer. Why i never thought of this ??? worked a treat. Thanks again and thanks to Gasman for you input.
 

Users who are viewing this thread

Top Bottom