Date parameters

[FredGB]

Hi
I am using CR v9 and accessing a foxpro database via an ODBC connection.

I am trying to show how many months are due til next training requirement and have been successful in showing up to three months using the following formular :-

If sum ({@PAS11A1/Aout},{people.companyref}) >=1 then "T" else
if Maximum ({@PAS11A1/Arenewaldate},{people.companyref}) in Next30Days then "1" else
if Maximum ({@PAS11A1/Arenewaldate},{people.companyref}) in Next31to60Days then "2" else
if Maximum ({@PAS11A1/Arenewaldate},{people.companyref}) in Next61to90Days then "3" else
if Maximum ({@PAS11A1/Arenewaldate},{people.companyref}) in Alldatesfromtoday then "*" else
"X"

How can I add to this to show "4" "5" etc to "12" months countdown, i.e. 91 to 120 days then "4" etc.


Thanks

Fred

 

[Madawc]

Do one formula to find the number of days between the date and now. Something like @Futuredays

Datediff("d", Currentdate, Maximum ({@PAS11A1/Arenewaldate},{people.companyref}))

Then test

if @Futuredays =< 30 then "1"
else if @Futuredays =< 60 then "2"

and so on

Madawc Williams (East Anglia)

 

[FredGB]

Hi Madawc
You guys never cease to amaze me.
Worked a treat might get some R & R in now.

Sincerely

Fred

 

[tmozer]

Running a daily report which tracks intake, production and backlog (for the current or previous day, depending on when the report is run), I also want to report cumulitive totals for the work week (starting Mondays), the month, the quarter and the calendar year. That means I have to determine start dates for each total (from this date, what is the date of the start of the week, month, quarter and year??).

Can someone suggest a formula (or a set of same) for doing the above??

 

[Madawc]

tmozer, that really should be a new thread. There is no strong connection with this one.

Look up DateAdd to find out how to subtract whole months or quarters. There is also DayOfWeek, which gives you 1 for Sunday, 2 for Monday etc.

Also check the FAQs, and then use the Search function. I think you'll find fully worked out solutions for what is quite a common need.

Madawc Williams (East Anglia)