How can I define IIf parameters across different records in a table?

Question

I've defined a query that filters out records that are null in a specific field. I'd like to also calculate a query field that returns the type of record that follows the record that was filtered out, if it matches the parameters. The way I thought to do this was with an IIf statement with multiple parameters:

Preparing: IIf([tblCustomers!OrderId]=([tblCustomers!OrderId]+1)
    AND [tblCustomers!OrderStatus]="Preparing","Preparing","")

This didn't work as I hoped, but I wasn't too surprised, as it would have to return data from the field initially tested. So, the argument that adds 1 is actually doing nothing.

Is there a way to target the next record in the table, test if it matches one of two or three strings, then return which one it is?

---

Edit: Following @mazoula's solution, it seems a correlated subquery is indeed the answer here. Following the guide on allenbrowne.com (linked by June7), I seemed to be on the right track. Here is my code for retrieving the status of a previous record:

SELECT tblCustomers.AccountId, 
tblCustomers.OrderId, 
tblCustomers.OrderStatus,
tblCustomers.OrderShipped,
tblCustomers.OrderNotes,
    (SELECT TOP 1 Dupe.OrderStatus                 
    FROM tblCustomers AS Dupe                     
    WHERE Dupe.AccountId = tblCustomers.AccountId 
        AND Dupe.OrderId > tblCustomers.OrderId   
    ORDER BY Dupe.AccountId DESC, Dupe.OrderId) AS NextStatus
FROM tblCustomers
WHERE (((tblCustomers.OrderShipped)="N") AND 
((tblCustomers.OrderNotes) Is Null))
ORDER BY tblCustomers.AccountId DESC;

Unfortunately, I am met with the following error:

At most one record can be returned by this subquery

Doing a little more research, I found that incorporating an INNER JOIN expression should solve this.

...
FROM tblCustomers
INNER JOIN OrderStatus Dupe ON Dupe.AccountId = tblCustomers.AccountId
WHERE ...

This is where I've hit another roadblock and, when the syntax is at least correct, I receive the error:

Join expression not supported.

Is this a simple syntax issue, or have misunderstood the role of a Join expression?

Answer 1

in Access 2016 I do this in two parts because access throws the error: must use an updateable query when I try to update based on a subquery. For instance, if I want to replace the Null Values in TableA.Field3 with 'a' if the next record's Field3 is 'a'

tableA:
-------------------------------------------------------------------------------------
|         ID         |       Field1       |       Field2       |       Field3       |
-------------------------------------------------------------------------------------
|                  1 | a                  |                  1 |                    |
-------------------------------------------------------------------------------------
|                  2 | b                  |                  2 |                    |
-------------------------------------------------------------------------------------
|                  3 | c                  |                  3 | a                  |
-------------------------------------------------------------------------------------
|                  4 | d                  |                  4 | b                  |
-------------------------------------------------------------------------------------
|                  5 | e                  |                  5 |                    |
-------------------------------------------------------------------------------------
|                  6 | f                  |                  6 | b                  |
-------------------------------------------------------------------------------------

I make a table on which to base the update query:

Replacement: (SELECT TOP 1 Dupe.Field3 FROM [TableA] as Dupe WHERE Dupe.ID > [TableA].[ID])

'SQL PANE'

SELECT TableA.ID, TableA.Field1, TableA.Field2, TableA.Field3, (SELECT TOP 1 Dupe.Field3 FROM [TableA] as Dupe WHERE Dupe.ID > [TableA].[ID]) AS Replacement INTO TempTable
FROM TableA;

TempTable:

----------------------------------------------------------------------------------------------------------
|         ID         |       Field1       |       Field2       |       Field3       |    Replacement     |
----------------------------------------------------------------------------------------------------------
|                  1 | a                  |                  1 |                    |                    |
----------------------------------------------------------------------------------------------------------
|                  2 | b                  |                  2 |                    | a                  |
----------------------------------------------------------------------------------------------------------
|                  3 | c                  |                  3 | a                  | b                  |
----------------------------------------------------------------------------------------------------------
|                  4 | d                  |                  4 | b                  |                    |
----------------------------------------------------------------------------------------------------------
|                  5 | e                  |                  5 |                    | b                  |
----------------------------------------------------------------------------------------------------------
|                  6 | f                  |                  6 | b                  |                    |
----------------------------------------------------------------------------------------------------------

Finally do the Update

UPDATE TempTable INNER JOIN TableA ON TempTable.ID = TableA.ID SET TableA.Field3 = [TempTable].[Replacement]
WHERE (((TempTable.Replacement)='a'));

TableA after update

-------------------------------------------------------------------------------------
|         ID         |       Field1       |       Field2       |       Field3       |
-------------------------------------------------------------------------------------
|                  1 | a                  |                  1 |                    |
-------------------------------------------------------------------------------------
|                  2 | b                  |                  2 | a                  |
-------------------------------------------------------------------------------------
|                  3 | c                  |                  3 | a                  |
-------------------------------------------------------------------------------------
|                  4 | d                  |                  4 | b                  |
-------------------------------------------------------------------------------------
|                  5 | e                  |                  5 |                    |
-------------------------------------------------------------------------------------
|                  6 | f                  |                  6 | b                  |

notes: In the Make Table query remember to sort TableA and Dupe in the same way. Here we use the default sort of increasing ID for TableA then grab the first record with a higher ID using the default sort again. the only reason I did the filtering to 'a' in the update query is it made the Make Table query simpler.