Script to calculate month

[Matt Leach]

Im having trouble with a script that involves calculating a month.

I have a field called "Assigned" that contains a date.

What i am trying to do is create a script of all records that were assigned 1 month ago, 2 months ago, 3 months ago.

So far, i have been able to figure out how to get the current date and take the month to subtracy by one like so:

Month (Get (CurrentDate)) - 1

Now if i were to set that calculation as a variable of lets say $1_Month, how would i tell the program that i want to see all records whose assigned dates meet the criteria of the current month - 1, which would be July?

Thanks

Edited August 29, 2009 by Guest

[comment]

Try something like:

Enter Find Mode []

Set Field [YourTable::Assigned ; Let ( t = Get (CurrentDate) ; Date ( Month (t) - 1 ; 1 ; Year (t) ) & ".." & Date ( Month (t) ; 0 ; Year (t) ) ) ]

Perform Find []

Or, if you want to use a variable for how many months ago:

Enter Find Mode []

Set Field [YourTable::Assigned ; Let ( t = Get (CurrentDate) ; Date ( Month (t) - $monthsAgo ; 1 ; Year (t) ) & ".." & Date ( Month (t) - $monthsAgo + 1 ; 0 ; Year (t) ) )

Perform Find []

Edited August 29, 2009 by Guest

[Matt Leach]

I am able to get that to work correctly for 1 month back but anything over that does not appear to work correctly unless i am doing something wrong. I used your first example whoch worked for 1 month ago so i tried it for 2 months ago by changing the script to -2:

Let ( t = Get (CurrentDate) ; Date ( Month (t) - 2 ; 1 ; Year (t) ) & ".." & Date ( Month (t) ; 0 ; Year (t) ) )

That should give me all records with an assigned date of 7/1/09 - 7/31/09, but i am also getting records from June showing up. Am i using the script incorrectly?

[comment]

For 2 months ago, it needs to be:

Let ( t = Get (CurrentDate) ; Date ( Month (t) - 2 ; 1 ; Year (t) ) & ".." & Date ( Month (t) - 1 ; 0 ; Year (t) ) ) 

BTW, it's still August around here, so two months ago would be June.

[Matt Leach]

Math is a little rusty, thanks. I'll give it a try