Using update query to populate tables based on a second query

[Mattbro]

Hello All
I have a table ‘Data’ in which I record articles that I have to send to users. I use it to monitor if they have returned said items and to generate reminder letters; The Table has fields for
Record No (PK),
User name,
user address,
user postcode,
date sent,
date reminder letter sent,

In order to monitor reminder letters, I have a query QrySendLetter that captures daydatediff between Now() and date sent,
where daydatediff>50 and ‘date reminder letter sent’ is null in addition to capturing the user details.


I have generated a report RptReminderLetter, fed by QrySendLetter which pulls the user details off Data and thus all my letters are printed, all from a Command button on a form which opens the report.
What I want to do is run an update query after I have printed off all my reports, that takes all the Record No’s from QrySendLetter, and populates the relative ‘date reminder letter’ sent Field in Data with a Now() value, thus the next time I run the RptReminderLetter, these items are excluded.
I have tried various combinations with update queries, such as

 UPDATE Data INNER JOIN [QrySendLetter] ON Data.[Record No] = [QrySendLetter].[Record No] SET Data.[1st Reminder Sent] = Now();

…but no luck so far. Does anyone know if this is possible, and if so, how?
Mattbro

[orange]

I think the issue may be because you are using the same table "data" 2 different places.
Do you get an error message? What exactly is the query for -suggest you show us the SQL.

Why not put a WHERE clause on an SQL statement such as:
UPDATE Data
SET [date Reminder Sent] = Now()
WHERE
DateDiff("d", [date sent], Now) > 50 AND
[date Reminder Sent] Is NULL

[Mattbro]

Aha!-Now that looks like it will work! I will try it at work and let you know. Many thanks,
Mattbro

[Mattbro]

Yep -works fine. Thanks very much.
Mattbro