using 'Max()' in an update query?

[JensenQT]

So I'm trying to use an update query to update a due date field from table A, using the latest event date from table B per AutoID (table A) + A frequency (days).

I've tried a few different ways but no closer, any suggestions?

(I'm quite new to access and rarely touched update query)

thanks.

[Welshgasman]

So give us a clue, what have you used?

Show your sql, and relationship window at least?

[JensenQT]

Here is the relationship table thing.
and the error.

[Welshgasman]

Firstly I think the order of your functions is incorrect.
You need to get the Max date first, then use DateAdd(). You have it the other way around?

Try using DMax() instead to get the date, then surround that with the DateAdd()

Do it in stages, so you can check the results. Make it a Select query and when correct, change it to an update query.

[JensenQT]

I have tried the method you provided. Im not quite sure what to think about this new error?

[Welshgasman]

Right.
Your Dmax() is not even attempting to use the correct syntax? God know what Access thinks that is.

As I mentioned previously, get the data into separate columns. get the Max date first, then apply the DateAdd() in yet another columns and compare.

You should be using the same criteria for the DMax(), that you are using for the update, which you said was you EquipmentInventoryID ?, else you will get any old date.

[JensenQT]

Im trying an alternative route, as I am not understanding why certain expressions and functions work with some things and not others.

I have create a Qry to Find the latest Date of a list of events and add months with a frequency.
This works fine.


But then when trying to use this in an update Qry it breaks and picks one date.


anyone have any idea why its doing this? and if you do maybe explain it to me?

thanks.

[Welshgasman]

Well, your field that you want to use appears to be called Due ?

Show the SQL code.

[JensenQT]

SELECT QryMaxEventDate.EquipmentInventoryID, DateAdd("m",[Frequency],[MaxOfDate_of_Event]) AS Due
FROM TblEquipment_Inventory INNER JOIN QryMaxEventDate ON TblEquipment_Inventory.EquipmentInventoryID = QryMaxEventDate.EquipmentInventoryID;

UPDATE QryDateAutoFill INNER JOIN TblEquipment_Inventory ON QryDateAutoFill.EquipmentInventoryID = TblEquipment_Inventory.EquipmentInventoryID SET TblEquipment_Inventory.Calibration_Due_Date = [MaxOfDate_of_Event];

[JensenQT]

Well, your field that you want to use appears to be called Due ?

Show the SQL code.

I did have it set to 'Due' but I had to reopen it and was set back to the old one, it still produces the same result with 2/12/2022

[Welshgasman]

OK, as I mentioned, I generally have to do this manually, but make it a select query and bring in the due date.

If you have it correct in the Select query, then the update should work.

Else upload the DB so someone can have a look. I only have 2007, so I might not be able to do that, but other members can.

You cannot possibly use MaxOfDate_of_Event as that is before you add your Frequency?

[Micron]

RE post 7. If the frequency value being used is 2 and it appears your max month value is 10, then without criteria you will add 2 months to 10 months and all dates will be the same. Not sure where the 2 and 2022 comes from. You might have to use a subquery to get the max of a value but I'm not seeing how that result would apply to so many different dates. IMO, your first error is because it's not a Totals query. The update error means you are trying to update a field to something that the field cannot accept, be that Null, or strings instead of numbers (or vice versa) - something along those lines.

You haven't shown the relationships window, you've shown a query design view. Ribbon>Database Tools>Relationships
I think that would help to figure things out.