using two criteria in one case statement

[chessico]

I have a series of case statements that open my form in different views. The VBA code for the form
uses both SQL and VB statements as below
Private Sub FilterMe_Click()
On Error GoTo Err_FilterMe_Click
Dim dtmStart As Date, dtmEnd As Date

Select Case Me.optFilterBy
Case 1 'Current memberships or unexpired
Me.Filter = "[Expired] = False"
Me.OrderBy = "MemberID"
Me.OrderByOn = True
Me.FilterOn = True
Case 2
'Shows records for the LAST YEAR based on each CURRENT year
'but needs new code every time date is changed!
dtmStart = IIf(Month(date) < 8, DateSerial(Year(date) - 2, 8, 1), DateSerial(Year(date) - 1, 8, 1))
dtmEnd = IIf(Month(date) < 8, DateSerial(Year(date) - 1, 7, 31), DateSerial(Year(date), 7, 31))
Me.Filter = "MemberID IN (SELECT MemberID FROM S_Payments_Table " & _
"WHERE PaymentDate Between " & _
"#" & Format(dtmStart, "mm/dd/yyyy") & "# And " & _
"#" & Format(dtmEnd, "mm/dd/yyyy") & "#" & _
"ORDER BY MemberID)"
Me.OrderBy = "MemberID"
Me.OrderByOn = True
Me.FilterOn = True



However I wanted to show the form with members who are 'Players'. My form takes all membership details and if a member is a rugby player then this is indicated by a 'tick' box. Trouble is some players remain 'ticked' but may not play in the current year for whatever reason but remain a player for next season. Without resetting the 'tick' box each season its easier to leave it but I want to filter members who are players but only current for this year.
So for
Case 6 ' Players
Me.Filter = "[Player] = True" 'And "[Expired] = False" This doesn't work data type mismatch
In ther words I want to do two things instead of 1 in the same statement

[pbaldy]

Your quotes are goofed up; try:

Me.Filter = "[Player] = True And [Expired] = False"

[chessico]

Thanks havent had chance to reply - felt rough- had flue! Your help appreciated of course it works great. Now to make things harder I want to filter records by two different data types in my case statement.
The previous code was based on two 'tick boxes' indicating true or false but this statement needs to combine this code below but also -
And [Expired] = False
So I presume I have to use SQL statement (as code posted previously in thread) instead? Sorry never done this type before.

Case 9 'Full Members type is 3
Me.Filter = "[MemberTypeID] = 3"
Me.OrderBy = "MemberID"
Me.OrderByOn = True
Me.FilterOn = True

[pbaldy]

What are the different types? Basically text values need to be surrounded by single quotes, dates by #, numbers by nothing, and the whole thing with double quotes:

"DateField = #3/14/12# AND TextField = 'ABC' AND NumericField = 123"

[chessico]

Thanks again. Basically I have had this right before except I either put double quotes in the middles as well as ends or added single quotes after numbers.
The rule you gave was very helpful and simple for future reference. I gave up trying to solve this problem ages ago and only recently thought Id retry, so although it didnt work before by removing the quotes has solved it. I gave up using forums a good while ago because I found replies were often quite sarcastic to people like myself who had no programming knowledge other than bits of code snippets picked up from the internet. Sometimes the knowledgable few who replied were scathing because of my stupidty. This did not happen with you - you answered clearly and quickly and saved me going round in circles thinking it couldnt be done Again thank you.

[pbaldy]

Happy to help!