By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Filter Report on Fly with Part of a Date

100+
P: 147
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
Expand|Select|Wrap|Line Numbers
  1.             ElseIf TypeOf c Is Access.TextBox Then
  2.                 strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(35) & Format(c, "mm/dd/yyyy") & Chr(35) & " And "
Feb 11 '09 #1
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 1,287
I think you will have to use something like:
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & "([" & c.Tag & "] BETWEEN #1/1/" & Format(c, "yy") & "# And #1/1/" & (Format(c, "yy") + 1)  & "#) AND "
Feb 11 '09 #2

100+
P: 147
No that gives me results between Jan 1 of a random year and Jan 1 of the following year. Any ideas?
Feb 11 '09 #3

100+
P: 147
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?
Feb 11 '09 #4

Expert 100+
P: 1,287
Well that's much easier! You'd use:
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & "([" & c.Tag & "] BETWEEN #1/1/" & c & "# And #1/1/" & c + 1 & "#) AND "
Feb 11 '09 #5

100+
P: 147
Thats it!!! Your great! Thank you!
Feb 11 '09 #6

NeoPa
Expert Mod 15k+
P: 31,418
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 :
Expand|Select|Wrap|Line Numbers
  1. ElseIf TypeOf c Is Access.TextBox Then
  2.     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.
Feb 13 '09 #7

100+
P: 147
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?
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & "([" & c.Tag & "] BETWEEN #1/1/" & c & "# And #12/31/" & c & "#) AND "
  2.  
Feb 18 '09 #8

NeoPa
Expert Mod 15k+
P: 31,418
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).
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & "([" & c.Tag & "]=" & Me.c & ") AND "
With all these things, it's critical to know what it is you're dealing with.
Feb 18 '09 #9

100+
P: 147
Thank you I willl try with my next post coming very soon.
Feb 18 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.