Filter Question

[jaad]

I am having an error when this filter is applied:

Private Sub ThisWeekFilter_Click()
Me.Filter = "(Year(ProjectScheduleT.DateStart)=Year(Date()) And DatePart("ww",ProjectScheduleT.DateStart,0)=DatePart("ww",Date(),0))"
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

[ADezii]

Try replacing the Double Quotes (") with Singles (') within the DatePart() Function:

Me.Filter = "(Year(ProjectScheduleT.DateStart)=Year(Date()) And " & _
            "DatePart('ww',ProjectScheduleT.DateStart,0) = DatePart('ww',Date(),0))"
Me.FilterOn = True

[NeoPa]

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.

[NeoPa]

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.

[NeoPa]

I suppose I should also highlight a problem the compiler won't find for you :

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.

[jaad]

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?

[NeoPa]

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.

[jaad]

I appreciate the feedback, thanks a million. You guys are by far the best bunch online.

[NeoPa]

We're always glad to help when we can :)