Update Query not working 1

[psperry2]

I need to update a field in a temp table. When I run the Update Query, All I get is a view of the existing field data in the table I want to be updated. No update takes place.

UPDATE ztrptElapsed SET StartDate = (SELECT StepDateStart FROM tblPersons_Steps WHERE tblPersons_Steps.ISInvID = ZtrptElapsed.IsInvID AND tblPersons_Steps.StepID = 100);

 

[Golom]

Click the exclamation mark (run) icon rather than the view icon.

 

[psperry2]

Ok now it tells me "Operation must use an updateable query

 

[Golom]

Try this

UPDATE ztrptElapsed Z INNER JOIN tblPersons_Steps P
       ON Z.IsInvID = P.ISInvID 

SET Z.StartDate = P.StepDateStart 

WHERE P.StepID = 100

 

[psperry2]

Thank you Golom that worked! I tried to write the SQL in the same format I am used to in Transact SQL.

 

[Golom]

Access doesn't like coordinated sub-queries in UPDATE statements.

Transact-SQL (usually) doesn't have a problem with them as long as there's enough information to uniquely identify the records to be updated.

 

[psperry2]

I need to write another update query on the same table, I have a field called Elapsed_Days and it is a calculated field based on the difference in days between the 2 dates, StartDate and Enddate.

Can the DateDiff Function be used in a Query?

 

[psperry2]

this query works but if there is invalid dates it can produce a negative number. Is there a way to prevent the update if the DateDiff function produces a negative number?

UPDATE ztrptElapsed SET Elapsed_Days = DATEDIFF('d',StartDate,EndDate);

 

[psperry2]

ok I answered my own question.

this works ok:

UPDATE ztrptElapsed SET Elapsed_Days =iif(DATEDIFF('d',StartDate,EndDate)>0,DATEDIFF('d',StartDate,EndDate),0);

 

[PHV]

another way:
UPDATE ztrptElapsed
SET Elapsed_Days = DATEDIFF('d',StartDate,EndDate)
WHERE EndDate>StartDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886