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

Filter Question

100+
P: 158
I am having an error when this filter is applied:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ThisWeekFilter_Click()
  2. Me.Filter = "(Year(ProjectScheduleT.DateStart)=Year(Date()) And DatePart("ww",ProjectScheduleT.DateStart,0)=DatePart("ww",Date(),0))"
  3. Me.FilterOn = True
For some reason it works fine when I apply it directly from the filter tool on the Ribbon but when I try to use it in VBA it highlight "ww" as an error. Does anyone know why?

Thanks
Feb 5 '10 #1

✓ answered by ADezii

Try replacing the Double Quotes (") with Singles (') within the DatePart() Function:
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "(Year(ProjectScheduleT.DateStart)=Year(Date()) And " & _
  2.             "DatePart('ww',ProjectScheduleT.DateStart,0) = DatePart('ww',Date(),0))"
  3. Me.FilterOn = True

Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,597
Try replacing the Double Quotes (") with Singles (') within the DatePart() Function:
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "(Year(ProjectScheduleT.DateStart)=Year(Date()) And " & _
  2.             "DatePart('ww',ProjectScheduleT.DateStart,0) = DatePart('ww',Date(),0))"
  3. Me.FilterOn = True
Feb 5 '10 #2

NeoPa
Expert Mod 15k+
P: 31,186
See Quotes (') and Double-Quotes (") - Where and When to use them.

ADezii's advice will help as the first " at the start of "ww" is closing the string in your code, not opening a string within your string. The interpreter then finds itself dealing with ww, as something it has no knowledge of.
Feb 5 '10 #3

NeoPa
Expert Mod 15k+
P: 31,186
Try following this advice always. It is worth doing even before you get to thinking about asking any questions. In this case the compiler would have found and reported your problem without the need for a question.
When posting any code on here please :
  1. Ensure you have Option Explicit set (See Require Variable Declaration).
  2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
  3. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
  4. Ensure that the code in your post is enveloped within CODE tags. The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.
If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question.
Feb 5 '10 #4

NeoPa
Expert Mod 15k+
P: 31,186
I suppose I should also highlight a problem the compiler won't find for you :
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = "(Year(ProjectScheduleT.DateStart)=Year(Date()) And DatePart("ww",ProjectScheduleT.DateStart,0)=DatePart("ww",Date(),0))"
This line makes little sense as I see it, as the second must always be true if the first is. If two dates are equal, then the week values of those dates cannot help but be equal also.
Feb 5 '10 #5

100+
P: 158
Thanks Adezii, the changing of the quotes " to ' did the trick. Much appreciated.

NEOPA: I always wondered about this "Option explicit" and "Option Compare Database" line at the top. I understand what "Option Explicit" means, but what does"Compare database" line about?
Feb 5 '10 #6

NeoPa
Expert Mod 15k+
P: 31,186
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
specifies how comparisons are made in the code. Check the Help system for full details, but basically it determines how strings should be compared. Is the case of the characters to be considered or ignored. The Database setting indicates to use the setting defined in the databse options, but you can choose to take control and set it explicitly if you prefer.
Feb 6 '10 #7

100+
P: 158
I appreciate the feedback, thanks a million. You guys are by far the best bunch online.
Feb 6 '10 #8

NeoPa
Expert Mod 15k+
P: 31,186
We're always glad to help when we can :)
Feb 7 '10 #9

Post your reply

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