Solved Filter in the subform based on a date (1 Viewer)

smtazulislam

Member
Local time
Today, 15:36
Joined
Mar 27, 2020
Messages
806
Any help will appreciate
I have a subform Like :
IDClinIDJoinDateLeftDate
012501/02/202003/03/2020
022602/05/202015/09/2021
032715/01/202101/03/2021

And I create a Main form that is data RecordSource to tblclient table.
I want to create two combo box (Year) and (Month) for filtering record in the subform on based Joindate ;

If I choose from combo box year : 2020 then need display record Only 2020 year date from JOINDATE :
IDClinIDJoinDateLeftDate
012501/02/202003/03/2020
022602/05/202015/09/2021
If I choose from combo box year : 2020 and Month : May then need display record Only 2020 from JOINDATE
IDClinIDJoinDateLeftDate
022602/05/202015/09/2021

if I reset (Year = "" and Month = "") then show all data
can possible to filtering.
 

isladogs

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
You just need to concatenate the two combo values in your filter. The only 'tricky' bit is that you need month numbers based on month name e.g. 5 or 05 for May
However, a simpler approach for year and month may be to select all dates between the start and end of that month/year based on the two combo choices. The filter result would then resolve to
Code:
Between "#5/1/2020#" And "#5/31/2020#"

Suggest you lave a look at the Incident Analysis example in this article Multiple Group & Filter - Mendip Data Systems.
You should be able to use the same ideas for your situation
 

smtazulislam

Member
Local time
Today, 15:36
Joined
Mar 27, 2020
Messages
806
Much appeciate for your comment & suggest. I check your suggested db, here you used date in the main form and my date iin the subform even you have between two dates to filter where I want to the work on one date. But your db is nice to learn filter. Thank you sir @isladogs

I have a soluation now.
I try like :
Code:
Option Compare Database
Option Explicit

Private Sub month_AfterUpdate()
    Me.subformClient.Form.Filter = "[MonthMeet] = " & Me.month
    Me.subformClientattd.Form.Filter = "[MonthAttend] = " & Me.month
    Me.subformClient.Form.FilterOn = True
    Me.subformClientattd.Form.FilterOn = True
End Sub

Private Sub Year_AfterUpdate()
If Me.Year = "All Years" Then
    Me.Year = Null
    Me.month = Null
    Forms.MainForm.Form.subformClient.Requery
    Forms.MainForm.Form.subformClientattd.Requery
Else
    Me.subformClient.Form.Filter = "[YearMeet] = " & Me.Year
    Me.subformClientattd.Form.Filter = "[YearAttend] = " & Me.Year
    Me.subformClient.Form.FilterOn = True
    Me.subformClientattd.Form.FilterOn = True
End If
End Sub

I thinking in the future have any problem when I create split db for create FrontEnd and BackEnd for many users.
I guess that, without sql run in the VBA code can work at same time all users.

Please suggest me...

NOTE : here I showed example of a demo from my main db, But I have 6 subforms same thing. all work on a date.
 

isladogs

MVP / VIP
Local time
Today, 12:36
Joined
Jan 14, 2017
Messages
18,186
The whole point of a split database is to allow multiple users to view/edit data simultaneously.
There should be no issues if more than one users wishes to filter data at the same time.

It also doesn't matter whether the form data is being filtered or data in the sub form.
The only difference is that you need to reference sub form controls rather than form controls
 
Last edited:

smtazulislam

Member
Local time
Today, 15:36
Joined
Mar 27, 2020
Messages
806
The whole point of a split database is to allow multiple users simultaneously.
There should be no issues if more than one users wishes to filter data at the same time.

It also doesn't matter whether the form data is being filtered or data in the sub form.
The only difference is that you need to reference sub form controls rather than form controls
Thank you much your valuable advice. I appreciated for your quick response.
 

Users who are viewing this thread

Top Bottom