Hi Victor, Lets assume that as well as Smith you have Jones, Obama as well as a stack of other names, and they all take a similar format eg Jones 23
The general idea would be to parse the two search range criteria into the two components and then compare those with the split up data in the records So for example with the searchtext assign them to two RVs RV11= subfield[searchtext1;" ";1] and the numeric component RV12= convert["GZ9";subfield[searchtext1;" ";2]] and similarly with the 2nd searchtext2 into RV 21 and RV22 Remember to scope the convert "GZ9" to fit the largest expected numeric, I would probably allow a factor of 100 so if you expected less than 99 Smiths, then I might still opt for GZZZ9. (If Bill Gates had scoped DOS for 100 times more than the 640k he thought was enough, today it still wouldn't be anywhere near enough) The searchtext1 and 2 (ie upper and lower range), can come from a Prompt for RV or it could be from a panel, it really is academic where it comes from On each record you are testing you need to parse the record into 2 fields say RV31 for the text and RV32 for the numeric, similar to how you parsed the searchtext ranges above To provide a match you need to a store the test result into an RV, and the formula would look something like: RV100= ((RV31=RV11 AND RV32>=RV12) OR RV31>RV11) AND (RV31<RV21 OR (RV31=RV21 AND RV32<=RV22)) /* gee I hope I got that formula right, give it a go */ This will return a true 1 or a false 0 depending on whether the record is inside or mathing either the lower or upper range And then use SkIP record if RV100 is false 0 This then allows disjointed search ranges, so that a search range of Jones 4 to Smith 3, would output : Jones 4 Jones 5 Jone ... Obama 1 Obama 2 Obama ... Smith 1 Smith 2 Smith 3 You might want to add some more sophistication of checking that the input ranges are in the right order and swapping them if necessary, or checking that they are in the correct format. If the database is large you might want to use an indexed sort, and skip to the first RV11, and then Stop the subreport if the main logic test for RV100 is false rather than looping through lots of records. I hope this helps. Oh yes one other point, if you only ever wanted to search through Smiths or to search through Jones but not have searches overlapping then you could make a much simpler search expression, by recombining the search expression into just string eg RV 11= Cat.t[ Subfield[Searchtext1;" ";1]; apply.format["GZZZ9";convert["GZZZ9";Subfield[Searchtext1;" ";2]]] ] This would reformat Smith 23 as Smith0023 and once similarly done for the upper searchtext2 and the data you are looping though then you could just search on the text. RV31>=RV11 and RV31<=RV21, but you would not be able to use the SKIP TO and STOP Report so easily Regards Brian -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Victor Warner Sent: Tuesday, 24 February 2009 11:14 PM To: Dataperfect Users Discussion Group Subject: [Dataperf] How to run report which selects range in alpha numericfield? I would like to run a report on a panel which selects a range of records where the selection is done on an alphanumeric field. The field in question contains text entries such as Smith 1 Smith 2 Smith 3 Smith 4 and so on until Smith 20 Smith 21 Smith 22 Smith 23 Smith 24 If I wished to select the range Smith 20 to Smith 22, using the Specify Range in Search Options for Reports does not work (because it is a text field). Using a formula such as min[P1F1="Smith 20"] will produce Smith 2 Smith 20 Smith 21 Smith 22 Smith 23 Smith 24 and I get the same result if I specify, say min[P1F1="Smith 21"]. I have read Ralph Alvy's Mastering DataPerfect, Formulas chapter on "obtaining perfect matches and identiy operator", but hat does not appear to make any difference. Help with this would be much appreciated. Victor Warner. _______________________________________________ Dataperf mailing list [email protected] http://lists.dataperfect.nl/mailman/listinfo/dataperf _______________________________________________ Dataperf mailing list [email protected] http://lists.dataperfect.nl/mailman/listinfo/dataperf
