Others

How to Copy the Previous Row Value in Microsoft Access

Emma BrownEmma Brown Nov 3, 2026 869 views

Question details

The user needs to calculate a departure date in a database by evaluating a specific condition (Stop=1), and if not met, copying the departure date from the preceding record.

How to Copy the Previous Row Value in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Calculating a conditional departure date in a dataset where values often need to be inherited from the preceding record unless a stop condition is triggered.
Observed behavior
Because Access tables do not have an inherent row order like spreadsheets do, referencing a 'previous' row requires explicitly defining the sequence using a key or date field combined with relational SQL logic.
Before you start

Ensure your table includes a unique sequential identifier (such as an AutoNumber field or a timestamp) so you can explicitly define the order of the records.

Solution 1Recommended

Use a Correlated Query and IIf Function

Retrieve the previous record's value using a self-join or subquery, then apply conditional logic with the IIf function to calculate the date.

Since Microsoft Access does not store records in a guaranteed order, you cannot simply look 'up' one row. You must use a unique sequential field to determine what constitutes the preceding row.

By utilizing a subquery to find the highest ID less than the current ID, you can fetch the previous row's data and apply the required condition.

1
Establish an Order Field

Open your table in Design View and ensure there is an AutoNumber field (e.g., 'RecordID') or a strictly chronological Date/Time field to define the sequence.

2
Construct the Subquery

In Query Design, create a new query. Use a correlated subquery (or DLookup) to find the maximum RecordID that is less than the current row's RecordID to locate the previous record's Departure Date.

3
Apply the IIf Expression

Add a new calculated field in your query using the expression: IIf([Stop]=1, DateAdd("d", 1, [ArrivalDate]), [PreviousDepartureDate]). This fulfills the logic to calculate the date if Stop is 1, or copy the previous date otherwise.

4
Handle the First Record

Wrap your reference to the previous date in the Nz() function to prevent errors on the very first record, where no previous row exists.

Use a Correlated Query and IIf Function
Datasheet View Warning: Never rely on the visual order of a Datasheet view to determine the 'previous' row. Always explicitly define the sequence using SQL logic and a unique key.
Free Microsoft Office alternative

Looking for a Lightweight Alternative for Your Office Tasks?

While Microsoft Access is designed for complex relational databases, WPS Office provides an all-in-one suite for your daily document, spreadsheet, and presentation needs. It is completely free, lightweight, and features high compatibility with Microsoft Office formats, allowing you to manage flat-file data and spreadsheet calculations with ease.

  1. 1. Download WPS Office: Visit the official WPS website to download and install the free office suite on your preferred operating system.
  2. 2. Import Your Data: Export your Access data as a CSV or Excel file, then open it seamlessly in WPS Spreadsheet.
  3. 3. Analyze and Calculate: Use WPS Spreadsheet's built-in referencing tools and formulas to easily calculate preceding row values without complex SQL queries.
Seamlessly open, edit, and save Microsoft Excel, Word, and PowerPoint formats.Process complex data, formulas, and references effortlessly with WPS Spreadsheet.Free, lightweight, and fast installation on Windows, Mac, and Linux.Familiar user interface ensuring a zero-learning-curve migration.
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't Microsoft Access copy the previous record automatically?

Unlike Excel spreadsheets, Access is a relational database where records have no inherent order. You must explicitly instruct Access on how to sort and identify the 'previous' record using a unique identifier or date.

Can I use the DLookup function to get the previous row value?

Yes, you can use DLookup with criteria searching for the maximum ID less than the current row's ID. However, using a correlated subquery or self-join is often faster and more efficient for larger datasets.

How do I deal with the first record having a null previous value?

Wrap your previous row calculation in the Nz() function. This allows you to specify a default value or an alternative calculation when the preceding row's date is null.

Can this conditional date calculation be done in a table directly?

No, calculations that reference other rows must be performed in a Query, Form, or Report. Access tables do not support referencing preceding records directly in Calculated fields.