I have a couple of personal applications that have numeric data for
which I do not always have exact values. It might be a date field for
an historical event where I know the month, but not the day, or just
the year or even just the approximate year. In another application
where I wsant to keep track of gifts (Christmas, birthdays, etc.), I
often do not know the exact value of a gift.

I want to record a value and I want to record the exact value when I
have it. But I also want to be able to diffrerentiate between exact
values and approximate values.

One solution is to store the data as text. Then I can put anything in
the field (198x, Spring 1999, 5/2?/02, ~$100, etc.). This is fine for
display purposes, but awful for computation, which I sometimes need to
do.

Another solution is to add a second field to record the "uncertainty"
as either a percentage (±10%) or an amount (±$5, ±30 days). A zero
would indicate an exact value.

A similar approach would be to add two fields to indicate the min/max
values.

My inclination is to go with the second solution (the %) unless
someone can suggest something better.

Any suggestions?

You could use a Boolean (True/False) field with each numeric field that can
be approximate, your choice of True meaning actual, or True meaning
approximate.

Then, when you print the number, you could use a "tilda" ~ alongside it to
indicate that it is approximate. There are other ways, e.g., using the word
"approximately", but the tilda is a commonly-used symbol and is available in
most fonts in which you might print.

I don't know of a field property in the database that you could conveniently
use for this purpose.

Larry Linson
Microsoft Office Access MVP

"Jennifer Murphy" <JenMurphy (AT) geospace (DOT) org> wrote

Hi Jennifer,

With numeric data I have not so much experience, but I have met the
problem with dates in a historical/genealogic database.
The solution I used is to have two fields, one with the date as a text-
value (text_date), the other with the date as a date-type (ref_date).
The text_date is for input and display, the ref_date is for sorting
purposes.

When you enter a date in the text_date control – complete or
incomplete – it is analyzed in the On_Exit event. If IsDate returns
True, that value is stored in ref_date, and text_date is filled with
the value in standard date format.
When IsDate returns False, the text_date is split in day – month –
year, and any missing value is filled with the lowest possible value.
So “??-feb-2001” in the text_date would be filled as “01-feb-2011” in
the ref_date. Only an incomplete year in the text_date: “201?”, would
be converted to “01-jan-2010” in the ref_date.
I am still happy with this approach.

I can imagine you can use a same systematic with numeric values, where
the text_values express the uncertainties: ±$5, whereas the ref_values
contain the numeric value for sorting or summing: 5. An exact value in
the text_value means indeed an exact value.

Imb.