filter based on criteria from different fields

[Guest]

I have 3 combo boxes: dayofmonth, month, year in addition to common fields
(name, lessontype, instructor, payment). I would like the user to be able to
select a day,month and year and the form to be filtered by date such that
only data recorded for that date is shown. However, I am having trouble. My
code is given below; any help would be appreciated

Private Sub Go_payment_Click()
'declare variables
Dim datefilter As String
Dim vardayofmonth, varmonth, varyear, vardate As String
Dim frm As Form

'lookup value for day of month in form
vardayofmonth = DLookup("[day of month]", "payments", "[day of month]='"
&
Forms!payments![day of month] & "'") payments and store as dayofmonth
'lookup value for month in form
varmonth = DLookup("[month]", "payments", "[month]='" &
Forms!payments!month & "'")
'lookup value for year in form payments and store as year
varyear = DLookup("[year]", "payments", "[year]='" & Forms!payments!year
& "'")
'concatenate day, month and year into a date: "vardate"
vardate = CDate(vardayofmonth & "-" & varmonth & "-" & varyear)
' set criteria for filter
datefilter = BuildCriteria("Date", dbDate, vardate)
' open form to be filtered
DoCmd.OpenForm "viewpaymentsmadetoday" 'open form
Set frm = Forms!viewpaymentsmadetoday 'Return Form object variable set
to "viewpaymentsmadetoday".
'filter form
frm.Filter = datefilter
FilterOn = True 'allow filters

End Sub

 

[MacDermott]

Why code this, when you have FilterByForm?

 

[Guest]

Filter by form doesn't quite give what I want. Filter by selection does, but
when I try and code for it (I want to make it user-riendly, that the user can
just press a button and get the answer rather than have to worry about
filters him/herself) it either doesn't filter, or else says that
filterbyselection is not available right now. I will put the code I was
using for THAT below.
Thanks for any help,
Tarnia

Private Sub Go_payment_Click()
DoCmd.OpenForm "viewpaymentsmadetoday" 'open form
DoCmd.RunCommand acCmdFilterBySelection
end sub

OR
sub go_payment_click()
do.cmd.funcommand accmdfilterbyselection
end sub

Why code this, when you have FilterByForm?

I have 3 combo boxes: dayofmonth, month, year in addition to common fields
(name, lessontype, instructor, payment). I would like the user to be able to
select a day,month and year and the form to be filtered by date such that
only data recorded for that date is shown. However, I am having trouble. My
code is given below; any help would be appreciated

Private Sub Go_payment_Click()
'declare variables
Dim datefilter As String
Dim vardayofmonth, varmonth, varyear, vardate As String
Dim frm As Form

'lookup value for day of month in form
vardayofmonth = DLookup("[day of month]", "payments", "[day of month]='"
&
Forms!payments![day of month] & "'") payments and store as dayofmonth
'lookup value for month in form
varmonth = DLookup("[month]", "payments", "[month]='" &
Forms!payments!month & "'")
'lookup value for year in form payments and store as year
varyear = DLookup("[year]", "payments", "[year]='" & Forms!payments!year
& "'")
'concatenate day, month and year into a date: "vardate"
vardate = CDate(vardayofmonth & "-" & varmonth & "-" & varyear)
' set criteria for filter
datefilter = BuildCriteria("Date", dbDate, vardate)
' open form to be filtered
DoCmd.OpenForm "viewpaymentsmadetoday" 'open form
Set frm = Forms!viewpaymentsmadetoday 'Return Form object variable set
to "viewpaymentsmadetoday".
'filter form
frm.Filter = datefilter
FilterOn = True 'allow filters

End Sub

 

[MacDermott]

It's my personal belief that you're doing your users a bigger favor by
teaching them to use Access's built-in functionality than you would be by
trying to make it "user-friendly" by providing them with functionality they
won't have, and shouldn't be led to expect, in other Access applications.
Spend a few minutes teaching them to use Access, and they can apply it to
any other Access application. Teach them to expect custom buttons, and
you're setting them up for disappointment and frustration in the future.

Filter by form doesn't quite give what I want. Filter by selection does, but
when I try and code for it (I want to make it user-riendly, that the user can
just press a button and get the answer rather than have to worry about
filters him/herself) it either doesn't filter, or else says that
filterbyselection is not available right now. I will put the code I was
using for THAT below.
Thanks for any help,
Tarnia

Private Sub Go_payment_Click()
DoCmd.OpenForm "viewpaymentsmadetoday" 'open form
DoCmd.RunCommand acCmdFilterBySelection
end sub

OR
sub go_payment_click()
do.cmd.funcommand accmdfilterbyselection
end sub

Why code this, when you have FilterByForm?

I have 3 combo boxes: dayofmonth, month, year in addition to common fields
(name, lessontype, instructor, payment). I would like the user to be

able
to

select a day,month and year and the form to be filtered by date such that
only data recorded for that date is shown. However, I am having

trouble.
My

code is given below; any help would be appreciated

Private Sub Go_payment_Click()
'declare variables
Dim datefilter As String
Dim vardayofmonth, varmonth, varyear, vardate As String
Dim frm As Form

'lookup value for day of month in form
vardayofmonth = DLookup("[day of month]", "payments", "[day of month]='"
&
Forms!payments![day of month] & "'") payments and store as dayofmonth
'lookup value for month in form
varmonth = DLookup("[month]", "payments", "[month]='" &
Forms!payments!month & "'")
'lookup value for year in form payments and store as year
varyear = DLookup("[year]", "payments", "[year]='" & Forms!payments!year
& "'")
'concatenate day, month and year into a date: "vardate"
vardate = CDate(vardayofmonth & "-" & varmonth & "-" & varyear)
' set criteria for filter
datefilter = BuildCriteria("Date", dbDate, vardate)
' open form to be filtered
DoCmd.OpenForm "viewpaymentsmadetoday" 'open form
Set frm = Forms!viewpaymentsmadetoday 'Return Form object variable set
to "viewpaymentsmadetoday".
'filter form
frm.Filter = datefilter
FilterOn = True 'allow filters

End Sub