Update Query Help

[Vel]

Hello,

I've recently made a query for billing where I work. The
query displays only those activities whose insuranceID
= "AAM", whose DateOfService is <= the maximum
authorization date ([LastDate] in a query qryCurrentAuth)
and whose PrintedOn value = Null. The query works just
fine. However, I want to then use the criteria from that
same query for an update query to update the PrintedOn
Value to Now().

So, to summarize:

one table and one query make up the new update query

tblActivity -- use fields
ProcID (to filter out missed appointments and phone calls)
DateOf (to filter out dates which are not authorized and
to update to =Now())
PrintedOn (to filter out already printed activities)

qryCurrentAuth (to pull the LastDate value to filter the
DateOf value in tblActivity)

That's it. I have the criteria set the exact same, only
in the PrintedOn field I have the UpdateTo value set to
Now(). When I run it I get the error
"Operation must use an updateable query"

Since I'm only using the other query to filter my record
set, I don't quite understand why I'm getting this
message. I posted the actual SQL for the query below as
well...

Vel.

UPDATE qryCurrentAuth INNER JOIN tblActivity ON
qryCurrentAuth.ClientID = tblActivity.ClientID SET
tblActivity.PrintedOn = Now()
WHERE (((tblActivity.PrintedOn) Is Null) AND
((tblActivity.ProcID) Not Like "M*" And
(tblActivity.ProcID) Not Like "P*") AND
((tblActivity.InsuranceID)="AAM") AND
((tblActivity.DateOf)<=[LastDate]));

 

[Michel Walsh]

Hi,

You can't get an updateable query if one of the implied "table" is a
non-updateable query. Push the non-updateable queries in the WHERE clause,
like:


UPDATE Activity
SET PrintedOn=Now()
WHERE PrintedOn Is Null
AND ProcID Not Like "[MP]*"
AND InsuranceID = "AAM"
AND ClientID IN(
SELECT CurrentAuth.ClientID
FROM CurrentAuth
WHERE CurrentAuth.LastDate = Activity.DateOf)


( I assumed [LastDate] was a field from your query. If not, push that
condition back in the outer WHERE clause with the other conditions implying
just the table Activity and some constants ).


Hoping it may help,
Vanderghast, Access MVP