Update Query - update to day before next date

Question

I am trying to create a query which will update a blank "To Date" field with the day prior to next updated date.

Example, an [Item Number] standard cost was updated ([From Date]) on 01/03/2019, then again on 01/07/2019 and then again on the 01/01/2020.

I would like an adjacent column which is updated with [To Date] of 30/06/2019, 31/12/2019.

I will run a subsequent query which updates blanks (i.e. current cost as there is no next [From Date]) to Today End of Month date (I assume I need a separate query for this rather than an IIF which can populate Blanks as part of this update query?)

Currently I have below, but it is updating the [To Date] with day prior to the newest date in all instances (i.e 31/12/2019 for first 2 rows), I understand that I need a SORT within the below query:

Many thanks in advance from this first time poster!

UPDATE
Standards
INNER JOIN
Standards AS Standards_1
ON
(Standards.[Item number] = Standards_1.[Item number]) AND (Standards.[From date] < Standards_1.[From date])
SET
Standards.[To Date 2] = Standards_1.[From date]-1;

you're trying to update all the previous [To Date] except the current/last one? — jbud
Ultimately, all [To Date] will be updated, with the [To Date] being the day prior to the next [From Date], with the final [To Date] being last day of current month (this will be resolved with a subsequent query) — Emmet Mc
You're on the right track. This query will return the following [From date] for each row. You can use those results along with the DateAdd("d", -1, …) function to update the [To Date 2] column. — Gord Thompson
If this can be calculated for purpose of UPDATE then it is not necessary to actually save calculated value. Calculate when needed. Saving calculated data is usually not necessary and can even be bad idea as saved value can get 'out of sync' with data. — June7
Thanks @GordThompson! I've had to first run your query as a Make Table query, access did not like when I tried to use within an update query ("ms access operation must use an updateable query") — Emmet Mc

jbud jbud · Accepted Answer · 2020-11-20T17:52:30

This might work, but the performance migth be a bit slow:

UPDATE Standards
    SET [To Date] = Nz(
                        DateAdd("d", -1,
                            DMin("[From Date]", "Standards", "[Item Number]=" & [Item Number] & " AND [From Date] > #" & [From Date] & "#")
                        )
                        , DateSerial(Year(Date()), Month(Date())+1, 0)
                    )

Start from the DMin function, which will lookup the next date greater than the current date for the item.

DateAdd function will subtract one day from this date.

Nz will use the value returned by the DateSerial function if the DateAdd function result is Null.

DateSerial function returns the end date of the current month.

# are added because of the date value of the [From Date] field in the criteria of the DMin.

Instead of using the [From Date] field in the DMin function criteria, you can consider using the Autonumber Primary Key field. You'll need to remove the # in the criteria.

Everything between Nz(...) will need to be on one line.

This solves both of your requirements, just test the performance.

Update Query - update to day before next date

1 Answers