filtering only on year and not date

[tanzania]

Hi, I am using the following bit of code to filter a subform according to the filed Date_game, however its returning records according to the year and not actual dates.Any ideas why this may be? Thanks!

Forms!frmMain!sfCurrent.Form.Filter = "Date_game" & " Between #" & Me.txtDateFrom & "# And #" & Me.txtDateTo & "#"
Forms!frmMain!sfCurrent.Form.FilterOn = True

 

[Pat Hartman]

If the Date_game field contains time, you need to exclude that when filtering.

Forms!frmMain!sfCurrent.Form.Filter = "DateValue(Date_game) Between #" & Me.txtDateFrom & "# And #" & Me.txtDateTo & "#"
Forms!frmMain!sfCurrent.Form.FilterOn = True

If this code is running in the main form, you can use:
Me.sfCurrent.Form.Filter and Me.sfCurrent.Form.FilterOn

 

[tanzania]

is that necassary if ive set the format to short date when setting up the field? either way ive tried what you have suggested and its still behaving the same way. Ive done some testing and it is returning all dates for the year prior to the EndDate. For example if I select Jun 07 it shows all dates from january to the end of june. I am baffled!

 

[Pat Hartman]

How a date is formatted has nothing to do with how it is stored and it is the stored value that will be used by the filter - NOT the displayed value.

Are you only entering month and year into the controls used for filtering? To make this work properly, you need to use real dates in the filter. If you want the user to enter only month and year, you will need to supply the day values in the code module so that the dates are complete.