I need to get all value before the last items in field, how to?
Example:
.....
AAAAA BBBBBAA VVVVVV < I need to get all from this row
FFFFFFF OOOO SSSSS
get the value in field before the last value
-
- PlatinumLounger
- Posts: 4376
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: get the value in field before the last value
Is there a field on which the records are sorted?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4376
- Joined: 26 Apr 2010, 17:36
Re: get the value in field before the last value
no....HansV wrote:Is there a field on which the records are sorted?
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: get the value in field before the last value
I'm not disputing your reply, but can't it be extracted based on record count? (Just querying out of interest)
If there are 20 records, count 20-1 and extract?
Or offset one up from last record in record set?
If there are 20 records, count 20-1 and extract?
Or offset one up from last record in record set?
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: get the value in field before the last value
The order of records in a recordset is unpredictable if the sort order is not specified. If the underlying table has a primary key, the records will probably be sorted on that, but you can't depend on it. In particular, if records have repeatedly been inserted and deleted, 'new' records could be listed before 'old' records.
So you really need to specify how records should be sorted if you want to retrieve the 'last' record or the 'next to last' record.
Similarly, if you use First or Last as Total option in a totals query, or if you use the DFirst / DLast function, the result is basically random - you get the value from the first or last record that Access happens to encounter, but that may not be what you expect/want.
So you really need to specify how records should be sorted if you want to retrieve the 'last' record or the 'next to last' record.
Similarly, if you use First or Last as Total option in a totals query, or if you use the DFirst / DLast function, the result is basically random - you get the value from the first or last record that Access happens to encounter, but that may not be what you expect/want.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: get the value in field before the last value
TX for the explanation.
My mind was set on the scenario that one might want to extract the second last record irrespective of that record content... a positional extract as opposed to a record content extract.
Cheers
My mind was set on the scenario that one might want to extract the second last record irrespective of that record content... a positional extract as opposed to a record content extract.
Cheers
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: get the value in field before the last value
My point was that a "positional extract" returns a meaningless result if you don't specify how the records are sorted.
Best wishes,
Hans
Hans