Filter Report on Fly with Part of a Date

[DAHMB]

I have a report that I filter with a form,; everything works but I want to tweek it. I want to filter a date field on the year instead of the whole date. Could some one tell me what to change in this part of my code to make it work?
Thanks
Dan

            ElseIf TypeOf c Is Access.TextBox Then
                strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(35) & Format(c, "mm/dd/yyyy") & Chr(35) & " And "

[ChipR]

I think you will have to use something like:

strSQL = strSQL & "([" & c.Tag & "] BETWEEN #1/1/" & Format(c, "yy") & "# And #1/1/" & (Format(c, "yy") + 1)  & "#) AND "

[DAHMB]

No that gives me results between Jan 1 of a random year and Jan 1 of the following year. Any ideas?

[DAHMB]

Ok it does work but I have to enter the date as mmddyyyy . Can I set it up so I just enter the year as 2008 for example?

[ChipR]

Well that's much easier! You'd use:

strSQL = strSQL & "([" & c.Tag & "] BETWEEN #1/1/" & c & "# And #1/1/" & c + 1 & "#) AND "

[DAHMB]

Thats it!!! Your great! Thank you!

[NeoPa]

I presume you are trying to match all those records where the year-part of the field referred to in the .Tag property of the control, matches the year-part of the value in the control itself. In that case :

ElseIf TypeOf c Is Access.TextBox Then
    strSQL = strSQL & "(Year([" & c.Tag & "]=" & Year(c) & ") AND "

PS. Beware using between of two 1/1/... dates. This gives a year PLUS a day. 'Between' is always inclusive.

[DAHMB]

NeoPa your example did not work so to avoid getting Jan1 ot the following year included in my results I cjhanged the code to the following. What do you think?

strSQL = strSQL & "([" & c.Tag & "] BETWEEN #1/1/" & c & "# And #12/31/" & c & "#) AND "

[NeoPa]

It all depends on exactly what you have in c & c.Tag. I was going by your original code. According to that, c is a control containing a date value. Your latest code implies it only stores a year value. If that is true then, while your code may work, it's unnecessarily complicated. If c is alreay a year value, you can simply omit the call to the Year() function (which takes a date and returns its year).

strSQL = strSQL & "([" & c.Tag & "]=" & Me.c & ") AND "

With all these things, it's critical to know what it is you're dealing with.

[DAHMB]

Thank you I willl try with my next post coming very soon.