filter results according to month chosen

[SShafeeq]

Hi,

I have an EventCalendar form, which has a continuous sub form.

Basically, i have one combo box with different months listed and another which has years (2011 til 2030), and a command button.

Now the subform has all the records about all the events users have recorded, including date, location, time start and time end.

What I want is users to choose the month from the combo and the year from the second combobox, click the command button, and only the records whose date match the month and the year should display.

What code do I use?

I'm thinking a me.filter would be sufficient in the onclick event of the btn.

 

[Galaxiom]

For a small numbers of records you could use a query or filter with

Month(datefield) = Forms!formname.combo1 AND Year(datefield) = Forms!formname.combo2

However this is comparatively slow.

Better to use the combos to generate a dynamic where clause or filter in the style:

datefield BETWEEN #mm/1/yyyy# AND #mm/31/yyyy#

However you need to get the day to suit the month.

 

[DCrake]

Another altenative would be:

WHERE Format([DateField],"mmmyyyy") = Me.CboMonth & Me.CboYear

 

[Galaxiom]

There is an important difference between David's solution and mine. David is using the month name as an abbreviated text term so it is searching for values in the style of Jan2011.

My solution requires the bound column of the month combo to be the numeric month.

Similarly David's solution could also be done as:
WHERE Format([DateField],"mmyyyy") = Me.CboMonth & Me.CboYear

Since it is mmyyyy it will match on values like 012011. The combo need to be using the same style.

Another alternative:
WHERE Format([DateField],"mmmmyyyy") = Me.CboMonth & Me.CboYear

This will use the whole month name in the search and of course requires the combo to use the whole name too. However this longer string will be slower to match.

The whole month name (January) can be displayed but search on the number (01) or abbreviation (Jan) by using two columns and setting the ColumnWidths property to show what you want while being bound to the search value. All depends on how you set up the bound column of the month combo.

Hope this makes sense.

 

[SShafeeq]

Thanks guys, and Im sorry to sound ignorant but I don't know much about queries.

Is there a way to implement this in vba? I feel i'm more comfortable with that, even though i'm still finding my feet!

Thanks!

 

[SShafeeq]

Ok, i've got this so far but its not working, its giving me a syntax error:

Dim filtM As String
filtM = "Month(EvntDate) =" & Me.cmbMonth

Dim filtY As String
filtY = "Year(EvntDate) =" & Me.cmbYear


Me.Filter = "[EvntDate] =#" & filterM & "And [EvntDate] =#" & filtY
Me.FilterOn = True

 

[DCrake]

You dont need the #'s in your filter string.

 

[SShafeeq]

ok, I removed the #'s but its still giving me a

Run time error '3075'
syntax error (missing operator) in query expression "[EvntDate]=And [EvntDate] =Year(EvntDate)=2011'.

 

[SShafeeq]

Anyone have any ideas, as I have been smashing my head against this and nothing works! I'm sure theres an easy way around it but my novice experience in Access is slowing me down. Help very much appreciated!

 

[Galaxiom]

It is really just a matter of working logically so you end up with a string that makes sense. What you posted is duplicating the field name.

Part of your problem woud also be using FilterM when the variable is called FiltM. You should use Option Explicit in your code declarations so errors like that will be detected.

Me.Filter = filtM & " And " & filtY

 

[SShafeeq]

Solved!

Me.Filter = "Month(EvntDate)=" & Me.cmbMonth & "AND Year(EvntDate)=" & Me.cmbYear

The above filter works! Thanks everyone for helping me figure it out, couldn't have done it without your help.