ACCESS FEED: RE: [MS_AccessPros] Why are these query results different

Selasa, 12 November 2013

RE: [MS_AccessPros] Why are these query results different

Lee-

I suspect it's working properly, but you have the either the Format of the Decimal Places of the FTE field set so that it doesn't display the full value. When you open the tmpReportCurrentPosInfo table and click in FTE, do you still not see decimal places?

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of leevt99@yahoo.com
Sent: Tuesday, November 12, 2013 10:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Why are these query results different

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