KevinS said:
When I put the two fields sid by side in a query two things happen.
1- I get a message that I cannot group on memo or OLE object
2 - When I get rid of the 'Total' row then I get all the repeat
records.
Normally, i would only get one record.
I evidently don't know enough about SQL to have recognized the fact that
you are Grouping. By definition, grouping results in a non-updateable
recordset. To be able to update, you need to be processing individual
records.
When developing an update query, I *always* begin by defining a SELECT
query and verifying that I am getting the data I expect .... only after
carefully confirming that my select query is doing what I intend do I
unleash an update query.
Step one: Back up your database in case soemthing doesn't work quite
right.
Step two: Build a Select query that gives you the *exact* results you
want.
Step three: Attempt to manually modify the fields in question in the
datasheet view of your Select Query. If that fails, re-work your query
until you have an updateable recordset. If you have trouble with this
step post back -- or search the archives for making an updatable
recordset.
Step four: Make an update query using your select query as the
recordsource and verify that it works properly.
After you have tested and verified everything you could consolidate
everything into one query if that's the way you want it --- I tend to
use nested querys a lot because it's easier for me to grasp the concepts
of "do one thing at a time".
I don't know if this applies to this situation or not, but using "poison
characters" in field names makes me nervous -- [Comments/Plan of Action]
may or may not be causing trouble.
--
Clif
When I put IntakeMain.[Comments/Plan of Action] in the update query
Comments/Plan of Action --- Field
IntakeMain -- Table
[ComingDue2]![Comments/ Plan ofAction] -- Update to
[Comments/ Plan ofAction] -- Criteria
When I run this update query I get the message: "Operation must use an
update query".
What does this mean? Any questions?
KevinS
--
Is it the times or the Zeitgiest?
Clif McIrvin said:
I don't see anything obviously wrong with the SQL.
If you change the query to a select query and show both fields in
question do you see what you expect?
What are the properties of the two fields in question in the
respective
table design views?
I tried an update query but it is only good for replacing a name
with
a name
- not everything in the field.
What exactly do you mean? Unless this is different between A97 and
A2003
(I have no experience with any version other than A2003) replacing
"everything in the field" is precisely what an update query does.
--
Clif
KevinS said:
UPDATE ComingDue2 INNER JOIN (Staff INNER JOIN ((Staff AS Staff_1
INNER JOIN
(((C_Checklist RIGHT JOIN (PCCases INNER JOIN OpenCases ON
PCCases.CaseID =
OpenCases.IntakeChild.CaseID) ON C_Checklist.CaseID =
OpenCases.IntakeChild.CaseID) INNER JOIN IntakeChild ON
OpenCases.IntakeChild.CaseID = IntakeChild.CaseID) INNER JOIN
IntakeMain ON
PCCases.CaseID = IntakeMain.CaseID) ON Staff_1.StaffID =
IntakeMain.Supervisor) INNER JOIN CompltDB2 ON IntakeMain.CaseID =
CompltDB2.CaseID) ON (Staff.StaffID = IntakeMain.PCW) AND
(Staff.StaffID =
CompltDB2.StaffID)) ON ComingDue2.ASCI = CompltDB2.ASCI >>>>SET
IntakeMain.[Comments/Plan of Action] = [ComingDue2]![Comments/ Plan
of
Action]<<<< These are the two that are driving me crazy!
WHERE (((IntakeMain.ReferDate)<=Date()) AND ((C_Checklist.Certype)
Is
Null));