Re: formatting a calculated number field on a form
- From: Myrna Larson <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 17 Nov 2005 20:54:44 -0600
Are you suggesting that I should not use the built-in Filter-by-Form to allow
the user to set the filter criteria, but that I should develop my own form for
that purpose?
On 17 Nov 2005 02:15:37 -0800, peregenem@xxxxxxxxxxxx wrote:
>
>Myrna Larson wrote:
>> I have created a query with a field that calculates ages as described
above.
>> The expression used on the query for age at death is
>>
>> AgeAtDeath: Age(DoB,DoD)
>>
>> and it is formatted as Fixed.
>>
>> The query is the data source for a form. I would like to achieve the
following
>> appearance on the form:
>>
>> 1. For a live patient, I see 0.00. I want it to look blank.
>> 2. For a dead patient that lived exactly 69 years, I see 69 rather than
69.00.
>> For a patient that lived 38.2 years, I see 38.2 rather than 38.20.
>>
>> I can get around those 2 issues by changing the expression on the form to
>> something like
>>
>> Iif(Age(DoB,DoD)=0,"",Format$(Age(DoB,DoD),"0.00"))
>>
>> But now the field on the form contains text rather than a number, so there
are
>> problems filtering correctly. If, for example, I want to see only patients
who
>> died after age 40, I type in the Filter-by-form box
>>
>> >40
>>
>> and Access changes that to
>>
>> >"40"
>>
>> The consequence is that patients who died between the ages of 5.00 and 9.99
>> are incorrectly included since the filter is comparing text rather than
>> numbers.
>
>The principles of tiered architecture tells us to use the data layer to
>return raw values and use the front end to apply any formatting. Access
>tends to blur the lines between these boundaries e.g. a .mdb file is
>referred to as and 'Access database' even though it contains front end
>elements such as reports and forms.
>
>Worse, I see evidence of users embedding hard coded references to front
>end elements in the database layer e.g. a Query object (a.k.a. a VIEW),
>which is very much a data layer element, containing a form's control in
>the SQL where clause. Using a tiered approach, entirely possible in
>Access, would be to create a Parameter Query object (a.k.a. a
>PROCEDURE, stored proc, etc) in the data layer with strongly typed
>parameters (possibly supplying default values); the front end would
>then invoke the Query/proc passing values as required.
>
>Why is this tiered approach not favored in Access? I really have no
>idea. I get the impression that most Access font end developers choose
>to have forms with bound controls: always bound, always connected, the
>data is immediately committed as soon as a control's value is changed,
>etc. What criteria do they use to make this choice: default behavior,
>less/no programming required, lack of/no specific training in computing
>science, quick kludge due to time/cost constraints? Again, I have no
>idea.
>
>To answer your question less obliquely, if you can't/won't change to a
>tiered approach, how about having the Query/VIEW return two columns:
>one being the raw data which is kept hidden from the user but available
>to the front end developer under the hood in the form, and one column
>formatted for display purposes only.
.
- Follow-Ups:
- Re: formatting a calculated number field on a form
- From: peregenem
- Re: formatting a calculated number field on a form
- References:
- formatting a calculated number field on a form
- From: Myrna Larson
- Re: formatting a calculated number field on a form
- From: peregenem
- formatting a calculated number field on a form
- Prev by Date: Re: Can you copy switchboards between databases?
- Next by Date: Re: Form with 11 cmbo boxes - very slow to save design changes
- Previous by thread: Re: formatting a calculated number field on a form
- Next by thread: Re: formatting a calculated number field on a form
- Index(es):
Relevant Pages
|
