FilterEdit and Project 2003 not liking test criteria

[Jonathan]

Hi
I am having a problem with some code (see below) in Project VBA. This code
compiles fine and on some machines Win 2000 and Win XP works fine. On others
we get the following error messages:-

The entry is not valid.
The test value cannot be used with the field for the data you want to find
or filter for.

Then get

An unexpected error occurred with the method.

As the "test" parameter is direct from help file and works on some machines
at a loss as why this doesn't work.

Tried reinstalling Project and didn't fix
Tried making sure user has full admin rights to desktop and that didn't help.

Any ideas?

Thanks

Jonathan.

Private Sub cmdOK_Click()
Dim dtFilterDate As Date

dtFilterDate = DateAdd("h", 5, CDate(Right$(dtpDate, 11)))

On Error GoTo Err_cmdOK_Click
FilterEdit Name:="FilterByDay", Create:=True, TaskFilter:=True,
OverwriteExisting:=True, FieldName:="Start", _
test:="is greater than or equal to", Value:=dtFilterDate,
Operation:="and", ShowSummaryTasks:=True
FilterEdit Name:="FilterByDay", Create:=False, TaskFilter:=True,
OverwriteExisting:=False, newfieldname:="Start", _
test:="is less than or equal to", Value:=DateAdd("d", 1,
dtFilterDate), ShowSummaryTasks:=True
FilterApply Name:="FilterByDay"

Err_cmdOK_Click:
End
End Sub

 

[John]

Hi
I am having a problem with some code (see below) in Project VBA. This code
compiles fine and on some machines Win 2000 and Win XP works fine. On others
we get the following error messages:-

The entry is not valid.
The test value cannot be used with the field for the data you want to find
or filter for.

Then get

An unexpected error occurred with the method.

As the "test" parameter is direct from help file and works on some machines
at a loss as why this doesn't work.

Tried reinstalling Project and didn't fix
Tried making sure user has full admin rights to desktop and that didn't help.

Any ideas?

Thanks

Jonathan.

Private Sub cmdOK_Click()
Dim dtFilterDate As Date

dtFilterDate = DateAdd("h", 5, CDate(Right$(dtpDate, 11)))

On Error GoTo Err_cmdOK_Click
FilterEdit Name:="FilterByDay", Create:=True, TaskFilter:=True,
OverwriteExisting:=True, FieldName:="Start", _
test:="is greater than or equal to", Value:=dtFilterDate,
Operation:="and", ShowSummaryTasks:=True
FilterEdit Name:="FilterByDay", Create:=False, TaskFilter:=True,
OverwriteExisting:=False, newfieldname:="Start", _
test:="is less than or equal to", Value:=DateAdd("d", 1,
dtFilterDate), ShowSummaryTasks:=True
FilterApply Name:="FilterByDay"

Err_cmdOK_Click:
End
End Sub

Jonathan,
I only work with Windows XP so I don't know if Windows 2000 has an
issue. Off hand your basic code looks ok (I didn't try it) but I do have
some observations.
1. Why are you using "CDate"? Wouldn't dtpDate already be a date type?
2. In a standard date format of "XX/XX/XXXX" there are only 10
characters from the right but your Right$ function calls for 11
characters.
3. If some cases of dtpDate are not actually a date (i.e "NA"), then as
I recall, the conversion may not always work.
4. When it chokes, which line does it highlight?
5. But perhaps the most prominent observation is, why not simply use a
test of "equals" the subject date itself? Your code seems to approach a
single day filter in an overly-complex way.

John
Project MVP

 

[Jan De Messemaeker]

John, about your final remark, it's not as simple as that
7/7/8 14:00 is not equal to 7/7/8 14:01
dtpdates are generally completed by 00:00 and thus aren't equal to a start
date which is completed by 08:00 or whatever.
What the poster needs is the function vba.DateValue which eliminates the
time of day (or puts it equal o 00:00)

HTH


Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[John]

John, about your final remark, it's not as simple as that
7/7/8 14:00 is not equal to 7/7/8 14:01
dtpdates are generally completed by 00:00 and thus aren't equal to a start
date which is completed by 08:00 or whatever.
What the poster needs is the function vba.DateValue which eliminates the
time of day (or puts it equal o 00:00)

HTH

Jan,
Yes I thought about the time element after I posted so I went back and
did a little testing. Depending on how the dtpDate is expressed my
simple solution may or may not work. For example, if the dtpDate is a
string (i.e. "dd/mm/yyyy"), then a simple "equals" test in the filter
will cover all time values for that date.

Nonetheless, your approach covers more cases and is therefore better
overall.

John

 

[Rod Gill]

The method I use to eliminate the time part is cLng(Date). This deletes the
fractional part of the date number returning only the day number.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Jonathan]

The date format was dd-mmm-yyyy. I need use the longer month make it clear
depending on EU or US users. Reason for 11 characters dates.

Datevalue worked a treat thanks.

I can't use the "equals" as the test for the filter is for 24hrs from 5am to
5am the next day.

Unfortunately the error still occurs on the first FilterEdit line. It
doesn't seem to like any of the tests specified in the help file "equals",
"does not equal", "is greater than", "is greater than or equal to" etc.

Jonathan.

 

[John]

The date format was dd-mmm-yyyy. I need use the longer month make it clear
depending on EU or US users. Reason for 11 characters dates.

Datevalue worked a treat thanks.

I can't use the "equals" as the test for the filter is for 24hrs from 5am to
5am the next day.

Unfortunately the error still occurs on the first FilterEdit line. It
doesn't seem to like any of the tests specified in the help file "equals",
"does not equal", "is greater than", "is greater than or equal to" etc.

Jonathan.

Jonathan,
Thanks for the added info. Going back to your original post, you
indicate that the code works on some Win 2000 and XP installations but
not on others. That makes me wonder if there is a problem with object
library references.

Try the following. Use the macro below to check object library
references on both Win 2000 and Win XP machines. The values for each
reference will be printed to the Immediate Window in the VB Editor. You
can copy and paste the results into a text file or even Excel. Collect
the data for PCs where the code compiles and where it doesn't. Then
compare the various references. Ideally, everything should match but my
guess is that something will be missing on those machines where the code
does not compile.

Sub Chk_ObjLib_Refs()
Dim oRef As Object
For Each oRef In ThisProject.VBProject.References
Debug.Print oRef.Description
Debug.Print oRef.fullpath
Next
End Sub

John
Project MVP

 

[Jonathan]

The code compiles on all machines, the errors occur when the code is run.
I'll still check the object libraries and see if that is the problem.
Thanks
Jonathan.

 

[Jonathan]

I compared the object library references and they are the same.

 

[John]

I compared the object library references and they are the same.

Jonathan,
OK, let's see if we can break it down into something more easily
analyzed. You said the first FilterEdit line is highlighted when it
fails.
1. Where exactly did you use the DateValue function suggested by Jan?
2. When the macro fails, hit "debug" so the failed line is highlighted.
Now hover your mouse over the dtFilterDate variable. What value is
displayed?
3. If you manually create the first part of the filter, (i.e. using
Project/Filtered For/More Filters), does that filter work on all PCs?
4. Where does the dtFilterDate come from (i.e. is it entered by the user
via a message box, is it in a spare field, etc.)?

This is not impossible, it's just more difficult to troubleshoot since I
can't replicate the problem on my installation.

John
Project MVP

 

[Jonathan]

Your question about if the filters work via the interface got me thinking, as
they do work on all the workstations. They do work slightly differently in
that there is no time element specified and the date is displayed in a
different format. dd/mm/yy

So I converted the code to use strings instead of date variables (which
converts any date to our short date format dd-mmm-yyyy) and converted them to
dd/mm/yy and the filters worked. Played around further and it doesn't seem to
like the month format "Jul", should be "07". Odd.

Thanks for your help
Jonathan.

 

[John]

Your question about if the filters work via the interface got me thinking, as
they do work on all the workstations. They do work slightly differently in
that there is no time element specified and the date is displayed in a
different format. dd/mm/yy

So I converted the code to use strings instead of date variables (which
converts any date to our short date format dd-mmm-yyyy) and converted them to
dd/mm/yy and the filters worked. Played around further and it doesn't seem to
like the month format "Jul", should be "07". Odd.

Thanks for your help
Jonathan.

Jonathan,
Great, it sounds like you figured it out. Sometimes VBA can be very
cantankerous and all you can do is to try different things to "make it
happy".

You're welcome and thanks for the feedback.

John
Project MVP