Conditional Formatting based on Current Record [field] minus PreviousRecord [field]

[jhalstead85]

First time poster so please bare with me. I'm working on an Access
2003 database with a continuous form and I need some help with some
conditional formatting. Basically when the form loads I want to
compare a field from each record with the same field on the previous
record. If the Abs() difference of the two field is greater than or
equal to 1 I would like to make that control/field in the current
record yellow. I've searched high and low on Google for a solution and
spent the last 3 days trying to work on the kinks but cannot wrap my
head around this problem and how to go about it.

To clarify what I need see the following:

Assume we have a simple continuous form with three fields per row.

----Date----|-----Time-----|-randNum
--------------------------------------------
07-Jan-09 | 2:00 P.M. | -75.2
--------------------------------------------
07-Jan-09 | 3:00 P.M. | -75.5
--------------------------------------------
07-Jan-09 | 4:00 P.M. | -78.5
--------------------------------------------
07-Jan-09 | 5:00 P.M. | -74.5
--------------------------------------------

What I need:
Compare 3PM [randNum] to 2PM [randNum]. If absolute difference is >= 1
then turn 3PM randNum to yellow.
Compare 4PM [randNum] to 3PM [randNum]. If absolute difference is >= 1
then turn 4PM randNum to yellow.
....and so on and so forth for all records

The above needs to happen when the form is loaded/refreshed/requeried
and if/when a new record is added.

I'm up for any solution (robust, simple, tedious, etc).

Please let me know if you need any further explanation and thanks,
J

 

[Marshall Barton]

First time poster so please bare with me. I'm working on an Access
2003 database with a continuous form and I need some help with some
conditional formatting. Basically when the form loads I want to
compare a field from each record with the same field on the previous
record. If the Abs() difference of the two field is greater than or
equal to 1 I would like to make that control/field in the current
record yellow. I've searched high and low on Google for a solution and
spent the last 3 days trying to work on the kinks but cannot wrap my
head around this problem and how to go about it.

To clarify what I need see the following:

Assume we have a simple continuous form with three fields per row.

----Date----|-----Time-----|-randNum
--------------------------------------------
07-Jan-09 | 2:00 P.M. | -75.2
--------------------------------------------
07-Jan-09 | 3:00 P.M. | -75.5
--------------------------------------------
07-Jan-09 | 4:00 P.M. | -78.5
--------------------------------------------
07-Jan-09 | 5:00 P.M. | -74.5
--------------------------------------------

What I need:
Compare 3PM [randNum] to 2PM [randNum]. If absolute difference is >= 1
then turn 3PM randNum to yellow.
Compare 4PM [randNum] to 3PM [randNum]. If absolute difference is >= 1
then turn 4PM randNum to yellow.
...and so on and so forth for all records

The above needs to happen when the form is loaded/refreshed/requeried
and if/when a new record is added.

Unlike spreadsheets, databases have no definition of
"previous record". Actually, it's a little tougher in that
eaxh record in a table should stand on its own without being
dependent on other records in the table.

What that means is you need to use one or more queries to
determine the value in the "previous record". To do this
generally requires the use of subqueries (query within a
query) or a "self join" (linking the tale to itself).

If your definition of "previous record" is the record with
the latest time before the current record's time, then it
could be something like:

SELECT T.datetimefield, T.random,
(SELECT X.random
FROM table As X
WHERE X.datetimefield =
(SELECT Max(Y.datetimefield)
FROM table As Y
WHERE Y.datetimefield < T.datetimefield
)
) As Delta
FROM table As T

The second subquery determines the date/time of the
"previous record" and the first subquery then retrieves the
value of random from the identified "previous record".

If you can absolutely guarantee that there is *always* a
record that is *exactly* one hour before the current record,
then it can be done more simply and quickly with something
like:

SELECT T.datetimefield, T.random, X.random
FROM table As T LEFT JOIN table As X
ON T.datetimefield = DateAdd("h", 1, X.datetimefield)

Either way, the form's record source query now includes the
value of random from the "previous record" so it is easily
available in a conditional formatting expression.

 

[J]

First time poster so please bare with me. I'm working on an Access
2003 database with a continuous form and I need some help with some
conditional formatting. Basically when the form loads I want to
compare a field from each record with the same field on the previous
record. If the Abs() difference of the two field is greater than or
equal to 1 I would like to make that control/field in the current
record yellow. I've searched high and low on Google for a solution and
spent the last 3 days trying to work on the kinks but cannot wrap my
head around this problem and how to go about it.

To clarify what I need see the following:

Assume we have a simple continuous form with three fields per row.

----Date----|-----Time-----|-randNum

What I need:
Compare 3PM [randNum] to 2PM [randNum]. If absolute difference is >= 1
then turn 3PM randNum to yellow.
Compare 4PM [randNum] to 3PM [randNum]. If absolute difference is >= 1
then turn 4PM randNum to yellow.
...and so on and so forth for all records

The above needs to happen when the form is loaded/refreshed/requeried
and if/when a new record is added.

Unlike spreadsheets, databases have no definition of
"previous record".  Actually, it's a little tougher in that
eaxh record in a table should stand on its own without being
dependent on other records in the table.

What that means is you need to use one or more queries to
determine the value in the "previous record".  To do this
generally requires the use of subqueries (query within a
query) or a "self join" (linking the tale to itself).

If your definition of "previous record" is the record with
the latest time before the current record's time, then it
could be something like:

SELECT T.datetimefield, T.random,
                                (SELECT X..random
                                 FROM table As X
                                 WHERE X.datetimefield =
                                                        (SELECT Max(Y.datetimefield)
                                                         FROM table As Y
                                                         WHERE Y.datetimefield < T.datetimefield
                                                        )
                                ) As Delta
FROM table As T

The second subquery determines the date/time of the
"previous record" and the first subquery then retrieves the
value of random from the identified "previous record".

If you can absolutely guarantee that there is *always* a
record that is *exactly* one hour before the current record,
then it can be done more simply and quickly with something
like:

SELECT T.datetimefield, T.random, X.random
FROM table As T LEFT JOIN table As X
        ON T.datetimefield = DateAdd("h", 1, X.datetimefield)

Either way, the form's record source query now includes the
value of random from the "previous record" so it is easily
available in a conditional formatting expression.

Marsh,

Thanks for the suggestion I'll give it a try later today and see if I
can make it happen.

J