• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Help with Ageing formulae

I have created a formulae which I know does not work

if isnull ({WO.SCHEDSTARTDATE}) then 'No Shed Date' else
if ({WO.SCHEDSTARTDATE}) < currentMonth then 'Future Months Requirement' else
if ({WO.SCHEDSTARTDATE}) in currentmonth then 'Due this Month' else
if ({WO.SCHEDSTARTDATE}) in LastFullMonth then 'Due Last Month' else

Can someone correct the above for me
Gordon
0
GiaHughes
Asked:
GiaHughes
  • 4
  • 3
  • 2
2 Solutions
 
mlmccCommented:
For a future requirement the Scheduled Start Date would be GREATER THAN today.

if isnull ({WO.SCHEDSTARTDATE}) then 'No Shed Date' else
if ({WO.SCHEDSTARTDATE}) in currentmonth then 'Due this Month' else
if ({WO.SCHEDSTARTDATE}) in LastFullMonth then 'Due Last Month' else
if ({WO.SCHEDSTARTDATE}) > currentMonth then 'Future Months Requirement' else

What isn't working?

mlmcc
0
 
peter57rCommented:
if isnull ({WO.SCHEDSTARTDATE}) then 'No Shed Date'
else
if {WO.SCHEDSTARTDATE} > dateserial(year(currentdate), month(currentdate)+1,0)then 'Future Months Requirement'
else
if {WO.SCHEDSTARTDATE} >= dateserial(year(currentdate), month(currentdate),1) then 'Due this Month'
 else
if {WO.SCHEDSTARTDATE} in LastFullMonth then 'Due Last Month'
else
"Before Last Month"
0
 
GiaHughesAuthor Commented:
Hi mimcc/peter67r

Good to hear from you
I think the solution peter has given me works
How can i make the report order the heading
No Shed Date
Future Months Requirement
Due This Month
Older than last month

Rather than in alpha order
Gordon
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
peter57rCommented:
If you are grouping on this field then you can use the 'Specific Order' option in the Group dialog to set the group order.

Or you could just prefix the text with a letter to force a sequence..

A. No Shed Date
B. Future Months Requirement
C. Due This Month
D. Due Last Month
E. Older than last month
0
 
GiaHughesAuthor Commented:
Hi Peter57r
Where do I find the Group dialog so that i can specify the order?

Gordon
0
 
mlmccCommented:
Right click on the group header in the left margin
Click CHANGE GROUP
Click the dropdown on order
Choose SPECIFIED ORDER
A new tan will appear and you can specify the order

mlmcc
0
 
GiaHughesAuthor Commented:
Hi mimcc
The information is presented in a cross tab which is in a sub-report
so the cross tab is currently shown in the report header of the sub report
hope this makes sense, so I currently do have a Change group option
Any suggestions

Gordon
0
 
mlmccCommented:
You can do the same thing in the cross tab

Edit the subreport
Right click the cross tab in the upper left corner
Click CROSS TAB Expert
Select the field in the row or column block
Click CHANGE GROUP
Set the order to SPECIFIED ORDER

mlmcc
0
 
GiaHughesAuthor Commented:
Always excellent from mimcc and peter57r
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now