Re: formatting a calculated number field on a form
- From: peregenem@xxxxxxxxxxxx
- Date: 17 Nov 2005 02:15:37 -0800
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: Myrna Larson
- Re: formatting a calculated number field on a form
- References:
- formatting a calculated number field on a form
- From: Myrna Larson
- formatting a calculated number field on a form
- Prev by Date: Re: Access 2003 default Dropdown control not compatible with Access 20
- Next by Date: Re: UnBound Fields
- 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
|
