Hi Lee
Yes, that is a very annoying "feature" of update queries. If you want to preview the effect of the query, you need to convert it to a SELECT query, then convert it back again.
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of leevt99@yahoo.com
Sent: Wednesday, 13 November 2013 10:46
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] RE: Why are these query results different
Ugggh, I've been working on another platform too long, the query results show what's currently there in an update statement, not what WILL be there :/
---In ms_access_professionals@yahoogroups.com, <leevt99@...> wrote:
Hi, I am trying to update a table with a simple formula, the data type of the field i'm trying to update is double with 2 decimal places, and I'm updating it from another double field with 2 decimal places.
However in the update query, it tries to update all of the rows to 1, even though simple mathematics would show that should not be the case. When i do a select query with the exact same criteria, the results include.8, .6, .9, etc. like it's supposed to. I've tried the following in the update query but none of it works: [StdHours], Cdbl(stdHours), cdbl([StdHours]), Round(StdHours/40,2), etc. What's going on?
Does NOT add decimals:
UPDATE tmpReportCurrentPosInfo INNER JOIN tblCoBuDept
ON tmpReportCurrentPosInfo.DeptID = tblCoBuDept.DeptID
SET tmpReportCurrentPosInfo.FTE = StdHours/40
WHERE tblCoBuDept.DivisionCode='T2'
DOES add decimals (desired result)
SELECT StdHours/40 AS Expr1
FROM tmpReportCurrentPosInfo INNER JOIN tblCoBuDept
ON tmpReportCurrentPosInfo.DeptID = tblCoBuDept.DeptID
WHERE tblCoBuDept.DivisionCode='T2'
Thanks
-Lee
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar