decimal places in queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created an update query to sum a value created in a select query to a value stored in a table. I need the value to be stored as a decimal number with 2 decimal places in the table. The select query creates the correct value (in two decimal places) however when the update query updates the field in the table the value is rounded to the nearest whole number. Whatever I try i cannot stop it doing this. I have modifed all the decimal place properties for the table, the select query and the update query I can find to no avail. The SQL for both the qeuries is attached at the end of this message - hope somebody can help

Select Query
SELECT Employees.EmployeeID, Employees.EmployeeName, tblWeekDates.WeekNo, tblWeekDates.WeekEndingDate, tblWeekDates.WeekCommencingDate, Employees.EmploymentStatus, DateDiff("d",Date()-28,Date()) AS Expr1, Employees.AverageWorkedDays, IIf([AverageWorkedDays]=5,([Expr1]/7)*0.38,(IIf([AverageWorkedDays]=4,([Expr1]/7)*0.31,(IIf([AverageWorkedDays]=3,([Expr1]/7)*0.23,(IIf([AverageWorkedDays]=2,([Expr1]/7)*0.15,(IIf([AverageWorkedDays]=1,([Expr1]/7)*0.08,0))))))))) AS Expr
FROM tblWeekDates, Employees INNER JOIN tblEntitlements ON Employees.EmployeeID = tblEntitlements.EmployeeI
WHERE (((tblWeekDates.WeekNo)=1) AND ((Employees.EmploymentStatus)="Currently Employed" Or (Employees.EmploymentStatus)="MaternityLeave"))

Update Query
UPDATE tblEntitlements INNER JOIN qryAccrual ON tblEntitlements.EmployeeID = qryAccrual.EmployeeID SET tblEntitlements.PermanentAccrual = [PermanentAccrual]+[Expr2]
 
Check the Design of the Table tblEntitlements and make sure the Field Size
of the Field PermanentAccrual is set to Double / Single / Decimal / Currency
rather than Long or Integer.

--
HTH
Van T. Dinh
MVP (Access)



Neal said:
I have created an update query to sum a value created in a select query to
Click to expand...
a value stored in a table. I need the value to be stored as a decimal number
with 2 decimal places in the table. The select query creates the correct
value (in two decimal places) however when the update query updates the
field in the table the value is rounded to the nearest whole number.
Whatever I try i cannot stop it doing this. I have modifed all the decimal
place properties for the table, the select query and the update query I can
find to no avail. The SQL for both the qeuries is attached at the end of
this message - hope somebody can help.
Select Query:
SELECT Employees.EmployeeID, Employees.EmployeeName, tblWeekDates.WeekNo,
Click to expand...
tblWeekDates.WeekEndingDate, tblWeekDates.WeekCommencingDate,
Employees.EmploymentStatus, DateDiff("d",Date()-28,Date()) AS Expr1,
Employees.AverageWorkedDays,
IIf([AverageWorkedDays]=5,([Expr1]/7)*0.38,(IIf([AverageWorkedDays]=4,([Expr
1]/7)*0.31,(IIf([AverageWorkedDays]=3,([Expr1]/7)*0.23,(IIf([AverageWorkedDa
ys]=2,([Expr1]/7)*0.15,(IIf([AverageWorkedDays]=1,([Expr1]/7)*0.08,0))))))))
) AS Expr2
FROM tblWeekDates, Employees INNER JOIN tblEntitlements ON
Click to expand...
Employees.EmployeeID = tblEntitlements.EmployeeID
WHERE (((tblWeekDates.WeekNo)=1) AND
Click to expand...
((Employees.EmploymentStatus)="Currently Employed" Or
(Employees.EmploymentStatus)="MaternityLeave"));
Update Query:
UPDATE tblEntitlements INNER JOIN qryAccrual ON tblEntitlements.EmployeeID
Click to expand...
= qryAccrual.EmployeeID SET tblEntitlements.PermanentAccrual =
[PermanentAccrual]+[Expr2];
 
In the destination table, what is the data type of the field. If I were a
gambler, I'd put up $100 that says that it's not a single, double, or other
data type that accepts decimals. :)


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Neal said:
I have created an update query to sum a value created in a select query to
Click to expand...
a value stored in a table. I need the value to be stored as a decimal number
with 2 decimal places in the table. The select query creates the correct
value (in two decimal places) however when the update query updates the
field in the table the value is rounded to the nearest whole number.
Whatever I try i cannot stop it doing this. I have modifed all the decimal
place properties for the table, the select query and the update query I can
find to no avail. The SQL for both the qeuries is attached at the end of
this message - hope somebody can help.
Select Query:
SELECT Employees.EmployeeID, Employees.EmployeeName, tblWeekDates.WeekNo,
Click to expand...
tblWeekDates.WeekEndingDate, tblWeekDates.WeekCommencingDate,
Employees.EmploymentStatus, DateDiff("d",Date()-28,Date()) AS Expr1,
Employees.AverageWorkedDays,
IIf([AverageWorkedDays]=5,([Expr1]/7)*0.38,(IIf([AverageWorkedDays]=4,([Expr
1]/7)*0.31,(IIf([AverageWorkedDays]=3,([Expr1]/7)*0.23,(IIf([AverageWorkedDa
ys]=2,([Expr1]/7)*0.15,(IIf([AverageWorkedDays]=1,([Expr1]/7)*0.08,0))))))))
) AS Expr2
FROM tblWeekDates, Employees INNER JOIN tblEntitlements ON
Click to expand...
Employees.EmployeeID = tblEntitlements.EmployeeID
WHERE (((tblWeekDates.WeekNo)=1) AND
Click to expand...
((Employees.EmploymentStatus)="Currently Employed" Or
(Employees.EmploymentStatus)="MaternityLeave"));
Update Query:
UPDATE tblEntitlements INNER JOIN qryAccrual ON tblEntitlements.EmployeeID
Click to expand...
= qryAccrual.EmployeeID SET tblEntitlements.PermanentAccrual =
[PermanentAccrual]+[Expr2];
 
Back
Top