Problem with an Updating query

  • Thread starter Thread starter Dennis Snelgrove
  • Start date Start date
D

Dennis Snelgrove

Below is an SQL update query. Access keeps telling me that "Operation
must use an updateable query". I've looked at the Help file, but I
can't see where any of the listed causes applies to my query. Any
insights would be more than welcome.

I've appended a bunch of info for 2008 into a database from Excel, and
now I'm running (or attempting to) queries to match the Foreign keys
to the proper Primary keys. The criteria below is to limit the update
to the newly appended data by limiting the TimesheetsID.

None of the tables are read-only, and this is a simplle mdb in which
I'm the only user. If I'm not supplying enough info, please let me
know...

Thanks...


UPDATE tblTimesheets SET tblTimesheets.PayPeriodID = (Select
tblPayPeriodInfo.PayPeriodID FROM tblPayPeriodInfo WHERE
((tblTimesheets.WeekEnding) between (tblPayPeriodInfo.WeekStarting)
AND (tblPayPeriodInfo.WeekEnding)))
WHERE (((tblTimesheets.TimesheetsID)>=1144));
 
As far as Access is concerned, your subquery COULD return multiple
values and therefore Access will not update the field.


Select tblPayPeriodInfo.PayPeriodID FROM tblPayPeriodInfo WHERE
((tblTimesheets.WeekEnding) between (tblPayPeriodInfo.WeekStarting)
AND (tblPayPeriodInfo.WeekEnding)))


You can use DLookup (VBA function) to get the value

DLookUp("PayPeriodID","tblPayPeriodInfo","WeekStarting<=#" & WeekEnding
& "# AND WeekEnding >=#" & Weekending & "#")


I may have gotten the <= and >= tests reversed, TEST THIS and BACKUP
you data first.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top