Last Record Field
- Thread starter Joshann
- Start date
- Local time
- Today, 08:43
- Joined
- Feb 28, 2001
- Messages
- 30,551
This question has no constant meaning. Therefore you cannot implement it reasonably without doing something that probably violates every rule in the book. If this is what you think you must do, it is time to rethink. Seriously. Here is why that is so.
The "last" record depends on the sort order. The last record for an ascending sort for field A is different than the last record for a descending sort for field B. "Last record" is PURELY a relative term. You can never use this query field meaningfully in a query. Note also that opening a table and opening a query do not guarantee appearance of the data in the same order, so you can't store this in a table.
Not only that - so here is your recordset in your little query. Now you go delete or insert a record at the end of the table. You might well have changed the designator for "last record." If you hard-coded the size of the table, you just screwed yourself because of the size change. If you stored a real marker in the table, it's probably wrong.
In VBA code, the recordset's EOF property is set to TRUE when you reach the end of the recordset. Forms and reports test this property "behind the scenes" to determine that the current record is, indeed, the end of the set. You can use the EOF property in VBA yourself if you opened the query via a recordset. And if you opened it implicitly through a form or report, you want to use a "footer" section to take some action at the end of the recordset implied by the query.
In any query-based operation, the end of the set is what stops Access from continuing. You could not have told Access to stop earlier without putting a criterion in the query. You cannot tell Access to NOT stop at that place, either. It has nowhere to go if you did. So what value is there to knowing this information? Nothing practical.
OK... now I've told you why you really don't want to do this. So here is how you MIGHT do it if despite my lecture you decide this is the ONLY way you can live another day...
IF the query has a unique key for sorting purposes, add a field that contains an IIF that tests for whether the unique key equals the DMax() of that key using the same filtration rules for the DMax as were used for the query. (I.e. same WHERE clause.) Look up DMax. UNLESS OF COURSE you used descending order, in which case use DMin instead.
If the query has no unique key for sorting, you cannot do this anyway. Because without something unique, you have nothing testable.
The "last" record depends on the sort order. The last record for an ascending sort for field A is different than the last record for a descending sort for field B. "Last record" is PURELY a relative term. You can never use this query field meaningfully in a query. Note also that opening a table and opening a query do not guarantee appearance of the data in the same order, so you can't store this in a table.
Not only that - so here is your recordset in your little query. Now you go delete or insert a record at the end of the table. You might well have changed the designator for "last record." If you hard-coded the size of the table, you just screwed yourself because of the size change. If you stored a real marker in the table, it's probably wrong.
In VBA code, the recordset's EOF property is set to TRUE when you reach the end of the recordset. Forms and reports test this property "behind the scenes" to determine that the current record is, indeed, the end of the set. You can use the EOF property in VBA yourself if you opened the query via a recordset. And if you opened it implicitly through a form or report, you want to use a "footer" section to take some action at the end of the recordset implied by the query.
In any query-based operation, the end of the set is what stops Access from continuing. You could not have told Access to stop earlier without putting a criterion in the query. You cannot tell Access to NOT stop at that place, either. It has nowhere to go if you did. So what value is there to knowing this information? Nothing practical.
OK... now I've told you why you really don't want to do this. So here is how you MIGHT do it if despite my lecture you decide this is the ONLY way you can live another day...
IF the query has a unique key for sorting purposes, add a field that contains an IIF that tests for whether the unique key equals the DMax() of that key using the same filtration rules for the DMax as were used for the query. (I.e. same WHERE clause.) Look up DMax. UNLESS OF COURSE you used descending order, in which case use DMin instead.
If the query has no unique key for sorting, you cannot do this anyway. Because without something unique, you have nothing testable.
Yes, I do realize all of those things you said, and normally, I wouldn't even consider trying to do this. I didn't explain why I need to do this because I was trying to spare everyone a long story. But here it is... If you can tell me a different way to do this, I would be happy to hear it.
The query I'm using is the result of two tables with a one to many relationship. The resulting query has no unique identifier. I am using the resulting query as the data source for a merge in Word. I need for a couple of specific paragraphs to appear in the merged document after the last record. If I simply put the paragraphs at the end of the form in Word, those paragraphs will appear after every record. As far as I've been able to ascertain, there is no function in Word that will determine which record is the last record in the data source. However, I can use a function that will test for a specific value in each of the records (e.g. { IF {MERGERECORD "LastRecord"} = "True" "INSERT TWO PARAGRAPHS" ""}). The query I'm using is grouped and sorted, and Word inserts the records into the document in the order in which they are sorted. Therefore, if I could create a field in the query that has true for its value if the record is the last record in the query or false if the record is not the last record in the query, I could accomplish what I'm trying to do.
The query I'm using is the result of two tables with a one to many relationship. The resulting query has no unique identifier. I am using the resulting query as the data source for a merge in Word. I need for a couple of specific paragraphs to appear in the merged document after the last record. If I simply put the paragraphs at the end of the form in Word, those paragraphs will appear after every record. As far as I've been able to ascertain, there is no function in Word that will determine which record is the last record in the data source. However, I can use a function that will test for a specific value in each of the records (e.g. { IF {MERGERECORD "LastRecord"} = "True" "INSERT TWO PARAGRAPHS" ""}). The query I'm using is grouped and sorted, and Word inserts the records into the document in the order in which they are sorted. Therefore, if I could create a field in the query that has true for its value if the record is the last record in the query or false if the record is not the last record in the query, I could accomplish what I'm trying to do.
