Re: formatting a calculated number field on a form


"Myrna Larson" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:e0t6o1dqgach29vplvu5k62e63ene4h5ut@xxxxxxxxxx
> >I made the apparently erroneous assumption that your database was not for
>>tracking information about newborns and infants. For anybody over the age
>>of one (actually, six months, I think) it will work.
>
> I only used age at death as an example. I am also calculating other time
> intervals, though not on the form. For example the interval between,
> surgery
> and heart attack, age 40 and heart attack, surgery and death, etc. I have
> 5
> dates (birth, surgery, heart attack, last contact, death). I may want to
> calculate the number of years between any two of those 5 dates.
>
>>Again, I made an assumption based on something in an earlier posting:
>>"We are tracking longevity and the occurrence and timing of heart attacks
>>and strokes"
>>The assumption was that there is a history for each patient, in which case
>>a
>>related table would have made sense. Your next paragraph makes it clear
>>that you are tracking only the first incident, which was not clear to me.
>
> Yes, sorry I was not more explicit about the purpose of the project, but
> none
> of these details have anything to do with my problem (formatting the
> number)
> or its solution, so I didn't provide them.
>
>>I should have said "a way" rather than "the way". I think that they are a
>>bit different in that a parameter query limits the recordset before it
>>becomes the form's record source, while a filter can limit the number of
>>records (by selecting a value from a combo box, for instance), but then
>>you
>>can remove the limitation. I expect the explanation is a bit imprecise.
>>>From what you have said I expect a filter would not be the best choice for
>>you needs.

Sorry. I said the exact opposite of what I intended, which was that a
filter may well be your best choice. However, you would not need to write
multiple parameter queries. You could allow for any parameter to be blank.
You could search for all heart attack incidents in a date range, or all
incidents of any sort in a date range, or all heart attacks regardless of
date, or whatever you choose.
>
> Why not? I may want to change the filter on-the-fly during an editing
> session,
> to double check certain items and in doing so, see ONLY the relevant
> records,
> for example see only those records that were updated in 2005 and have had
> a
> heart attack, or edited in 2005 and have had a stroke. I don't think
> writing a
> new parameter query for each possible scenario is worth the effort, as
> each of
> these filters would be used only once or twice at most.
>
>>Since you were doing a calculation it did not occur to me that you were
>>trying to format a field. I assumed (again) that you were calculating on
>>the fly, and formatting for display purposes. Does this mean you have
>>stored the calculation in your table?
>
> In a way, I am calculating on the fly. The data form uses a query as its
> data
> source, and the 4 ages (surgery, heart attack, contact, and death) that
> are
> displayed on the form are automatically updated when the dates are
> modified.

When I hear "field" I tend to think of a table field. Apparently you are
applying the format to a query field. My usual approach is to use forms to
view and change data, so I don't make much use of formatting in queries. I
got stuck on that point.
>
>>In response to a follow-up posting you made, the DateDiff expression I
>>offered will work as needed. Somebody who dies the day before or after
>>their birthday will show an even number of years, with 00 to the right of
>>the decimal point, any time you are using decimal years and the person is
>>at
>>least a year old. The problem you anticipated (40.01 or 39.99) does not
>>seem to occur, at least not in my tests.
>
> I don't need any help with calculating the age/time interval <g>. I've
> been
> programming in Basic since the 1970's, and problems relating to time
> intervals, and calculations involving decimal years, happen to be one of
> my
> special interests.
>
> I must admit that when I saw 'DateDiff("yyyy"' and the number 365.25 in
> your
> formula I dismissed it immediately. As I mentioned above, I am calculating
> other intervals with this VBA procedure. Age at death was just one
> example.
> One might encounter an interval of only a few days when, say, a patient
> has
> surgery and suffers a heart attack 1 day later.

My first thought on reading the above, had I not followed the rest of this
thread, would have been that these are two separate incidents rather than
two fields in one record. Much of what I wrote earlier was centered on that
assumption, which I think is reasonable enough in the relational database
world.
>
> Your solution began with the equivalent of this expression:
>
> IIf(DateDiff("yyyy",[Date1],[Date2])=0
>
> This will fail whenever the Date1 and Date2 are in the same calendar year:
> the
> expression will return 0. The biggest error would be the case of Date1 =
> Jan 1
> and Date2 = Dec 31. The correct result is 364/365 = 0.997, not 0.

Three decimal places offers a level of precision not possible with two
decimal places, of course. I suggested 365.25 only because with two decimal
places there is some imprecision anyhow. "yyyy" in the DateDiff expression
could have been "d".
>
> Otherwise your formula does give a reasonable approximation when the dates
> are
> in different calendar years.
>
> There are other business applications, say determining whether a person is
> eligible for a fringe benefit that requires at least 1 year of service, in
> which the rounding in undesirable. In such a case, I can imagine that one
> would not want the entitlement to occur one or more days early, i.e. hire
> date
> of 2/26/2004 and current date of 2/24/2005. The formula returns 1.00, and
> we
> are actually 2 days short.
>
> That doesn't happen to be an issue in my project, but because it CAN be an
> issue, I never divide by 365.25. My method in the general case is to
>
> 1. calculate the difference in whole years from the starting date to the
> anniversary date prior to (or on) the 2nd date
>
> 2. calculate the number of days from that prior anniversary date to the
> 2nd
> date
>
> 3. determine the number of days between the prior anniversary date and
> the
> next anniversary date. That will be 365 or 366, never 365.25
>
> 4. divide the number obtained in step 2 by the number from step 3, and
> add
> that result to the number obtained in step 1, i.e.
>
> whole years + (extradays/365 or 366)
>
> 5. round as appropriate
>
>>You chose to reference a VBA procedure in a query expression. You could
>>also have used VBA to place the calculated value onto the form, or any
>>number of other options. Your wanted to avoid an additional query field.
>>Had I understood that I would not have responded.
>
> Yes, I know I could. But I set up the calculations in the query because
> this
> query is used as the basis of other queries. And I don't NEED another
> query
> field. I set it up exactly the way I wanted it. My problem, again, was
> getting
> the calculated result to format as wanted, not how to do the calculation,
> or
> where to put the formula, etc.
>
> As I said, thanks for your thoughts.


.

Relevant Pages

  • Re: formatting a calculated number field on a form
    ... I only used age at death as an example. ... and heart attack, age 40 and heart attack, surgery and death, etc. ... >bit different in that a parameter query limits the recordset before it ... I assumed that you were calculating on ...
    (microsoft.public.access.forms)
  • Re: Macros to run update queries
    ... Thanks for the reply, however, calculating the age at the end of the ... The age field is in the table and needs to remain there as, ... query, it would mean the addition of around 100 queries to four ...
    (microsoft.public.access.macros)
  • Re: Calculated Field in a Table
    ... I calculate the value of the column based off of the Birthdate field? ... use an expression to calculate the age on all forms and reports, ... doing it as described in the query you only have to write the expression ... By calculating it on the fly ...
    (microsoft.public.access.tablesdbdesign)
  • Re: conditional formatting in form slows down calculations
    ... table to see all dates and a filtered query to see recent dates. ... endless calculating when the form opens. ... Once it finished the calculating, ... > CF (Conditional Formatting) is a weird beast, and it can cause the strange ...
    (microsoft.public.access.forms)
  • Re: query error
    ... "Jeff Boyce" wrote: ... The second query groups them by job number for one total time on job. ... you've already recorded start and stop times, the TaskTime value could be ... I have a specific job number that is not calculating ...
    (microsoft.public.access.queries)