Calculation in Update query drops decimal places

  • Thread starter Thread starter Daniel Collison
  • Start date Start date
D

Daniel Collison

In a select query, I create a field named "Age at Admit". The value for the
field is derived from the following SQL statement:

SELECT ([tbl Admission Episode]![admit_date]-[tbl Client
Demographics]![DOB])/365 AS [Age at Admit]
FROM [tbl Admission Episode] INNER JOIN [tbl Client Demographics] ON [tbl
Admission Episode].originl_recipient_id = [tbl Client Demographics].recip_rid;

The query returns values that include numbers to the right of the decimal
point. Example: 15.5342465753425

In an update query, I utilized the same logic to update a field in a table.
The sql follows:

UPDATE [tbl Admission Episode] INNER JOIN [tbl Client Demographics] ON [tbl
Admission Episode].originl_recipient_id = [tbl Client Demographics].recip_rid
SET [tbl Admission Episode].[Age at Admit] = ([tbl Admission
Episode]![admit_date]-[tbl Client Demographics]![DOB])/365
WHERE ((([tbl Admission Episode].originl_recipient_id) Is Not Null));

The query updates the table with whole numbers only. Example: 16.00.

I have tried to set the properties for the fields in question in both the
update query and the table to:

Field Size: long integer
Format: Standard
Decimal Places: 2

I still get whole numbers.

Any thoughts on how to remedy the problem?

Thanks,
 
The problem is that by definition a long integer is a whole number and it
does not matter how many decimal places you tell it to display it will always
be a whole number.
 
Daniel Collison said:
In a select query, I create a field named "Age at Admit". The value for
the
field is derived from the following SQL statement:

SELECT ([tbl Admission Episode]![admit_date]-[tbl Client
Demographics]![DOB])/365 AS [Age at Admit]
FROM [tbl Admission Episode] INNER JOIN [tbl Client Demographics] ON [tbl
Admission Episode].originl_recipient_id = [tbl Client
Demographics].recip_rid;

The query returns values that include numbers to the right of the decimal
point. Example: 15.5342465753425

In an update query, I utilized the same logic to update a field in a
table.
The sql follows:

UPDATE [tbl Admission Episode] INNER JOIN [tbl Client Demographics] ON
[tbl
Admission Episode].originl_recipient_id = [tbl Client
Demographics].recip_rid
SET [tbl Admission Episode].[Age at Admit] = ([tbl Admission
Episode]![admit_date]-[tbl Client Demographics]![DOB])/365
WHERE ((([tbl Admission Episode].originl_recipient_id) Is Not Null));

The query updates the table with whole numbers only. Example: 16.00.

I have tried to set the properties for the fields in question in both the
update query and the table to:

Field Size: long integer
Format: Standard
Decimal Places: 2

I still get whole numbers.

Any thoughts on how to remedy the problem?
Click to expand...


A Long Integer field is by definition an *integer*, a whole number with no
fractional part. When the division is performed, the result is a
floating-point value with decimal places, but when you try to save that
value in a Long Integer field, that floating-point value has to be converted
to an integer, so the fractional part is rounded off.

If you want your [Age at Admit] field to include fractional years, then you
have to change it to some data type that allows decimal places: Single,
Double, or possibly Decimal.
 
Back
Top