Custom Filter 1

[kevindmorgan]

I have a macro that creates a custom filter then executes the filter. It works fine if I put specific dates in the Value:="HELP" field.

I want to use variables instead of specific dates and base them from "today". I can use "today" in the first field, but I want the second date to be a date in the future. I have tried "today (+30)" but it treats that as the 30th of the current month.

Here is the part of the macro that creates the filter. I need to know how I can use a variable where HELP is used here.

Sub SixtyDay()

FilterEdit Name:="60 Day", TaskFilter:=True, _
create:=True, overwriteexisting:=True, _
FieldName:="Finish", test:="gtr or equal", _
Value:="HELP", Operation:="and", _
ShowInMenu:=True

FilterEdit Name:="60 Day", TaskFilter:=True, _
create:=False, overwriteexisting:=False, _
newfieldname:="id", test:="less or equal", _
Value:="HELP"

----truncated-----
End Sub

 

[PDQBach]

Try replacing "HELP" with "Enter a date in mm/dd/yyyy format "?

You will need to keep the quotes.

You may (but I can't remember) need to put a semicolon ";" after the final quote and before the "?". Try it without the semicolon first.

 

[kevindmorgan]

PDQBach:

I don't think I made the question clear. I am trying to do it with NO user input...which is why I need the variable.

I want to have a 30 day filter (for example) that will always filter from TODAY to 30 days. I want it to be done with a macro (or just a filter).

Like I said before, if I put "today" in the box it works for the first date, but how can I use that to reference a future date. I hope this isn't too confusing a question!

Also, I only used "HELP" in my macro to show where I would need to put a variable. I don't actually have "HELP" in there!

Thanks

 

[PDQBach]

Here are several possibilities:

Now +30d (d means days).

If that gets rejected, try
DateAdd( interval, number, date )

where interval is replaced by "d", number is 30 and date is, well, the original date.

 

[kevindmorgan]

PDQBach:

Thanks!

DateAdd was what I was looking for. It worked great. In case anyone else reads this and needs the whole deal, here it is!
======================================================
This will create a filter (and overwrite the existing filter with the name *60 Day) of tasks from 30-60 days from today that are less than 100% complete. It will then display a report of these tasks. (You will have to build the custom report though!)

Sub SixtyDaySort()

Dim FirstDate As Date
Dim SecondDate As Date
Dim TodaysDate As Variant


TodaysDate = Date
FirstDate = DateAdd("d", 30, TodaysDate)
SecondDate = DateAdd("d", 60, TodaysDate)


FilterEdit Name:="*60 Day", TaskFilter:=True, Create:=True, _
overwriteexisting:=True, FieldName:="Finish", _
test:="gtr or equal", Value:=FirstDate, Operation:="and", _
ShowInMenu:=True

FilterEdit Name:="*60 Day", TaskFilter:=True, Create:=False, _
overwriteexisting:=False, newfieldname:="Finish", _
test:="less or equal", Value:=SecondDate

FilterEdit Name:="*60 Day", TaskFilter:=True, Create:=False, _
overwriteexisting:=False, newfieldname:="% Complete", _
test:="less", Value:="100"

FilterApply Name:="*60 Day", Highlight:=False

ReportPrintPreview Name:="*60 Day"
End Sub

 

[PDQBach]

Thanks for posting the final code.