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