Formatting report fields

[benjy5]

Hey access gurus,
I'm working with access 97, and in my report I have to highlight the numbers in a particular field in my records in which there has been a drop of 50% from the previous record in that same field. Is there an easy way to do this thru the report or do I have to create another query to handle this?

Thanks for the help in advance!!

 

[Paul_Bricker]

b.
you might be able to use this custom function that returns values for the previous record. You would call it from your query.

Function PrevRecValue(KeyName As String, KeyValue, FieldNameToGet As String)
Dim rs As DAO.Recordset

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecValue = 0

' Get the form recordset.
Set rs = CurrentDb.OpenRecordset("YourQueryName", dbOpenDynaset)

' Find the current record.
Select Case rs.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select
Debug.Print rs!Total
' Move to the previous record.
rs.MovePrevious

' Return the result.
PrevRecValue = rs!FieldNameWhereValueIsStored
Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
Set rs = Nothing
End Function

Then in a new column you could have something like
ChangeHighlight:IIf(CurrentValue/PreviousValue >= .50,"Yes", "No")

Then in your Report check to see what the value of ChangeHighlight is and set the appearance of your textbox based on whether it's Yes or No.
HTH
Paul