If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
rounding in update query
I have a table with a Chronological Age column, defined as Single, fixed,
with auto decimal places. I have an update query that does a DateDiff function to calculate and populate the field, based on birthdate and a test date. The property on the calculated field is Fixed and Auto. Here's the calculation: DateDiff("yyyy",[DOB],[DatA]) where DatA is the date of the test (not necessarily today's date) When I run the query, it updates the chronological age column with values like 9.00, instead of 9.33. But we need to have the fractional age, instead of the "whole number" age. I've read through all of the posts and still can't figure out what I'm doing wrong. I'd like to solve this without doing VB. Is that possible? Thanks so much! |
#2
|
|||
|
|||
rounding in update query
On Tue, 13 May 2008 15:56:03 -0700, j9 wrote:
I have a table with a Chronological Age column, defined as Single, fixed, with auto decimal places. I have an update query that does a DateDiff function to calculate and populate the field, based on birthdate and a test date. The property on the calculated field is Fixed and Auto. Here's the calculation: DateDiff("yyyy",[DOB],[DatA]) where DatA is the date of the test (not necessarily today's date) When I run the query, it updates the chronological age column with values like 9.00, instead of 9.33. But we need to have the fractional age, instead of the "whole number" age. I've read through all of the posts and still can't figure out what I'm doing wrong. I'd like to solve this without doing VB. Is that possible? Thanks so much! Sorry but DateDiff() does NOT give fractional values. Your expression is asking for the difference in year boundaries between 2 values (i.e. the difference in years between 12/31/2007 and 1/1/2008 using DateDiff() is 1 year). If you can use data in the form of "25 years 3 months 12 days", use the Diff2Date function found at: http://www.accessmvp.com/djsteele/Diff2Dates.html If you do want data in the decimal form of 9.33 (which is not exact) you can use DateDiff("d",[DOB],[DatA]) / 365.25 which will be approximately correct. In addition to the above, if you did wish to show just the year age at a particular date, an accurate calculation would be: In a query: Age: DateDiff("yyyy",[DOB],[DatA])-IIf(Format([DOB], "mmdd")Format([DatA],"mmdd"),1,0) Directly as the control source of an unbound control: =DateDiff("yyyy",[DOB],[DatA])-IIf(Format([DOB], "mmdd")Format([DatA],"mmdd"),1,0) Where [DOB] is the birthdate field. The above takes into effect whether or not the birthday has already occurred this current year. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
Thread Tools | |
Display Modes | |
|
|