(UTC)
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Normal Topic Date range (Read 751 times)
tcgeo
Full Member
***
Offline



Posts: 278
Location: Traverse City, Michigan
Joined: May 13th, 2008
Date range
May 31st, 2010 at 10:11pm
Print Post Print Post  
I want to be able to show a date range in an LE for records in a search result.  I'd also like this available for reports.
In other words if my search result contains records that have the following dates in the date LE; 01/01/2010, 02/01/0210, 03/31/2010  I want to be able to show something similar to; Report range equals  01/01/2010  through 03/31/2010 in an LE.

I’ve tried using @XResultSetForm and @XResultSetValue to capture the dates in a date field so that I could use StringArray commands to sort them from earliest to latest date then select the first and last dates and write them similar to;  First date + “ “ + “Through” + “ “ + Last date.  When I attempt this tho, the @XResultSetValue returns only the year of the date and not the day, month and year.  So, I tried @ToNumber(string) and it returned the dates as numbers and I’m able to convert back to dates but I haven't been to select the earliest and latest dates.

I would think that this would be a common request for reports but I can’t seem to find a solution anywhere, any help would be appreciated.
  
Back to top
WWW WWW  
IP Logged
 
Carl Underwood
Senior Member
Members
*****
Offline



Posts: 1351
Location: New Hampshire
Joined: Mar 11th, 2003
Re: Date range
Reply #1 - Jun 1st, 2010 at 2:30am
Print Post Print Post  
I think this is what you are looking for...
Code
Select All
stat vRS as Int
stat vCount as Int
stat vMin as String
stat vMax as String

// Open the result set of the active form
vRS = @XResultSetForm(@Layout)

// Count the result set
vCount = @XResultSetTotal(vRS)

// Sort by date, ascending
XResultSetSort(vRS, "DateEntered:-1")

// Get oldest date
XResultSetCurrentPosition(vRS, 1)
vMin = @XResultSetValue(vRS, "DateEntered")

// Get latest date
XResultSetCurrentPosition(vRS, vCount)
vMax = @XResultSetValue(vRS, "DateEntered")

// Close the result set (but not the form)
XResultSetClose(vRS)

// Restructure the date format
vMin = @Right(vMin, 5) + "/" + @Left(vMin, 4)
vMax = @Right(vMax, 5) + "/" + @Left(vMax, 4)

// Work with final values
WriteLn(vMin + " through " + vMax)

Change "DateEntered" to whatever name you have for your date field.


But, you should also take a look at the following command. It will return what was entered into the retrieve specs for a particular layout element.
@Attribute([LE_Name], ATTR_ID_TEMPORARY_VALUE)
  

Carl Underwood
CDU Computer Consulting LLC
Epsom, New Hampshire
Back to top
WWW WWW  
IP Logged
 
tcgeo
Full Member
***
Offline



Posts: 278
Location: Traverse City, Michigan
Joined: May 13th, 2008
Re: Date range
Reply #2 - Jun 1st, 2010 at 11:29am
Print Post Print Post  
Thank you so much Carl, that worked instantly and exactly as wanted.  I would not have come up with that on my own.

As to "you should also take a look at..@Attribute([LE_Name], ATTR_ID_TEMPORARY_VALUE)"  This is what I have been using:

vDateselect = @Attribute(TodaysDate, ATTR_ID_TEMPORARY_VALUE)
vDateselect2 = @Left(vDateselect, @Instr(vDateselect, ".")-1)
vDateselect3 = @mid(vDateselect, @Instr(vDateselect, ".")+2,15)
Period Selected = "From" + "  " + vDateselect2 + "  " + "Thru" + "  " + vDateselect3

It worked great but I though that it would be more accurate to use the actual search result data? Not sure that I've made up my mind about that tho.

Thanks again for sharing the code, I sincerely appreciate that. Although I do enjoy trying to figure it out myself, sometimes I'm just at a dead end and need new insight. For this issue it definitely has relieved the stress to get the full code rather than getting bits and pieces that I have to wrestle with more.

And thanks for being there on the holiday too!

Have a great day,

Brandon
  
Back to top
WWW WWW  
IP Logged
 
Carl Underwood
Senior Member
Members
*****
Offline



Posts: 1351
Location: New Hampshire
Joined: Mar 11th, 2003
Re: Date range
Reply #3 - Jun 2nd, 2010 at 1:27am
Print Post Print Post  
You're welcome.
  

Carl Underwood
CDU Computer Consulting LLC
Epsom, New Hampshire
Back to top
WWW WWW  
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge