Value of field in previous record of query

  • Thread starter Thread starter Max Moor
  • Start date Start date
M

Max Moor

Hi All,

Is there a way, in an expression, to test the value of a field (same
one the expression is in) in the previous record?

What I really want to do is "group" records. I have a query sorted
on this field with group names in it. I want to run the query and have
this field display it's value only for the first record in a group with it.
The others would be blank. In the subform displaying off this query, it
would look like the groups were headed.

I hoe this makes sense. I'd love to make it work.

Thanks, Max
 
You can do this by adding a new column to the query that looks up the value
of the previous row, and then uses Conditional Formatting in your continuous
form to affect the display. However, it is very inefficient, and the results
are wrong if the user alters the filtering or sorting of the form.

If you are happy with read-only results, you could use a subquery for the
new column, e.g.:
PriorValue: (SELECT TOP 1 SomeField FROM MyTable AS Dupe
WHERE Dupe.ID < MyTable.ID ORDER BY ID DESC )

If you need editable results, DLookup() is not adequate as you cannot
specify a sort order. Paste the function below into a general module, and
use something like this for the calculated query field:
PriorValue: ELookup("SomeField","MyTable","ID < " & Nz([ID],0),"ID DESC")

Once you have the PriorValue field in your query:
1. Create a continuous form.
2. In form design view, select the field to be formatted ("SomeField" in the
example above).
3. Choose Conditional Formatting from the Format menu.
4. Set Condition 1 to:
Expression Is ... [SomeField] = [PriorValue]
and choose a suitable format (e.g. white font to hide the value).


Function ELookup(Expr As String, Domain As String, Optional Criteria,
Optional OrderClause)
On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Author: Allen Browne. (e-mail address removed)
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

'Build the SQL string.
strSql = "SELECT TOP 1 " & Expr & " FROM " & Domain
If Not IsMissing(Criteria) Then
strSql = strSql & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
strSql = strSql & " ORDER BY " & OrderClause
End If
strSql = strSql & ";"

'Lookup the value.
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set db = Nothing
Exit Function

Err_ELookup:
MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.Number
Resume Exit_ELookup
End Function
 
Not a good answer. Here is a much more efficient approach, that continues to
work regardless of how the form is sorted or filtered.

1. Paste the function below into a standard module, and save.

2. Add a text box to your from. Set its Name property to
txtPriorValue
Set its Visible property to No if you don't want to see it.

3 Set its Control Source to:
=GetPreviousValue([Form],"SomeField")
replacing "SomeField" with the name of your field.

4. Set the Format property of this text box to the appropriate type. For
example, use:
- General Number for numbers,
- Currency for currency,
- General Date for Dates.
Leave blank if the field is of type Text.

5. Select the text box to be formatted - SomeField in this example.
From the Format menu, choose Conditional Formatting.
Set Condition 1 to:
Expression Is ... [SomeField] = [txtPriorValue]
and apply the formatting you desire.


Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing

Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function


--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Allen Browne said:
You can do this by adding a new column to the query that looks up the value
of the previous row, and then uses Conditional Formatting in your continuous
form to affect the display. However, it is very inefficient, and the results
are wrong if the user alters the filtering or sorting of the form.

If you are happy with read-only results, you could use a subquery for the
new column, e.g.:
PriorValue: (SELECT TOP 1 SomeField FROM MyTable AS Dupe
WHERE Dupe.ID < MyTable.ID ORDER BY ID DESC )

If you need editable results, DLookup() is not adequate as you cannot
specify a sort order. Paste the function below into a general module, and
use something like this for the calculated query field:
PriorValue: ELookup("SomeField","MyTable","ID < " & Nz([ID],0),"ID DESC")

Once you have the PriorValue field in your query:
1. Create a continuous form.
2. In form design view, select the field to be formatted ("SomeField" in the
example above).
3. Choose Conditional Formatting from the Format menu.
4. Set Condition 1 to:
Expression Is ... [SomeField] = [PriorValue]
and choose a suitable format (e.g. white font to hide the value).


Function ELookup(Expr As String, Domain As String, Optional Criteria,
Optional OrderClause)
On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Author: Allen Browne. (e-mail address removed)
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

'Build the SQL string.
strSql = "SELECT TOP 1 " & Expr & " FROM " & Domain
If Not IsMissing(Criteria) Then
strSql = strSql & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
strSql = strSql & " ORDER BY " & OrderClause
End If
strSql = strSql & ";"

'Lookup the value.
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set db = Nothing
Exit Function

Err_ELookup:
MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.Number
Resume Exit_ELookup
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.


Max Moor said:
Hi All,

Is there a way, in an expression, to test the value of a field (same
one the expression is in) in the previous record?

What I really want to do is "group" records. I have a query sorted
on this field with group names in it. I want to run the query and have
this field display it's value only for the first record in a group with it.
The others would be blank. In the subform displaying off this query, it
would look like the groups were headed.

I hoe this makes sense. I'd love to make it work.

Thanks, Max
Click to expand...
Click to expand...
 
Back
Top