Well spotted Paul, I just assumed that Kam wanted to position to a record as opposed to extracting a copy - should have read the qusstion really!
So there are now 4 choices. Dave Crozier -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Hill Sent: 13 October 2006 23:11 To: ProFox Email List Subject: Re: quick SQL question The title says SQL question. If this is the case I guess you need: SELECT Max(dDateServ) WHERE dDateServ <= '2006-06-18' On 10/13/06, Dave Crozier <[EMAIL PROTECTED]> wrote: > Kam, > > You have 3 choices: > > 1. > Set near on > Seek(<<Key>>,<<alias>>,<<tag>>) > > Or > > Set near on > Seek <<key>> order tag <<tag>> > > Just don't forget to set near off or back to the original setting or your > "exact seeks" will not work correctly > > If SET NEAR is on, the record pointer is positioned immediately after the > record with the closest index key > > 2. > If you don't want to use set near then the Seek() or seek <<key>>.... will > both return found() correctly but if you look at recno() it will return the > record number of the closest record. > > 3. > Use the locate just as you have in the example > > The choice is yours. > > In case (3) if you have a valid index available for the search based on > EXACTLY the expression in the "locate" command then it will become optimized > with Rushmore and be just as fast as the seek variants. > > Dave Crozier > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of kam > Sent: 13 October 2006 21:45 > To: ProFox Email List > Subject: quick SQL question > > I have records with a date field: dDateServ D > (I am displaying a string but the date field is a type 'date') > > 1. 02/15/2006 > 2. 03/15/2006 > 3. 04/15/2006 > 4. 05/15/2006 > 5. 06/15/2006 > 6. 07/15/2006 > 7. 08/15/2006 > 8. 09/15/2006 > > I want a LOCATE statement something like this: > > LOCATE FOR dDateServ <= date(2006,06,18) > > I want record #5 - the max date that satisfies the for clause. > > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
