R said:
Simple question. I am creating a table in Access, and one of the fields I
want to have the default value to be what is in another field.
For example, the table has a field called "Fee" that displays the dollar
amount, and the second field is called "Cash". I want the default value in
the "Cash" field to show what the value in the "Fee" field is.
If I was doing this in Excel, I would use a simple reference formula. For
example, "Cash is column B and "Fee" is column A. B1 =A1. I don't know how to
do such a simple formula in Access though!
If someone could solve my issue, and EXPLAIN how to go about similar issues,
that would be fantastic.
There is also a follow up question. The next field in my Access table is
labled "Eftpos". I want it to have a default value of "Fee" minus "Cash". In
Excel I would write this as C1 =B1-A1
Interesting puzzle. I wouldn't call myself an expert (plenty of those
here!) - I lurk here to see what I can learn, and sometimes answer the
easy ones. This one's a bit like an exam question - it really got me
thinking...
The default value of a table field is the value Access places there
automatically when a new record is created. This value can be an
expression including the result of a function call: it's common to use
=now() for a date/time field, for example. There are lookup functions
you can use which could pick up any value in the database. However
(without testing) I doubt you could refer to another field in the same
record as the record wouldn't (quite yet) exist at the time you needed
the value. Anyway, if we're talking about a record at the point of
creation surely you could use the same default value expression for both
fields?
This makes me think you may not be using the term "default value" in the
way I am. So maybe this isn't about what to put in the "default value"
field in the field's properties at table design-time?
Duane (a real expert!) has already suggested how you might use a form to
derive a value. You can use event-handlers, like after-update, or
on-focus, to trigger snippets of VBA code. I use this to pre-fill boxes
on a form depending on the values of boxes already filled: for example
if the Hours and Rate boxes are already filled-in it's easy to have the
Charge box calculated automatically as you Tab into it.
However, depending on what you are trying to do you may not need a form.
You don't HAVE to store a derived value in the table if the values
from which it is derived are already stored (although there are
circumstances where you may need to). Instead, you include a calculated
field in a query. Instead of simply picking a field to be returned in
your query, you can use an expression. Access will give this a name of
"Exprn:" where n is some number if you do this, or you can supply your
own name for the value. In your expression you can refer to fields from
elsewhere, so you could have an expression:
Eftpos: [Fee]-[Cash]
There's an implied "=" after the colon, but you don't include it.
Try entering that in the Query Builder (and also have a look at what you
get in SQL view.)
So, you could create a query which returned the fields Fee and Cash from
your table(s) and also the calculated difference as an "extra" value,
"unbound" to any field in the table(s). And, as a form can be based on
a query (and usually is), this mechanism can be used to pre-populate an
unbound box ("control") on a form. (If you're going to use the form to
change the existing values you'll need to trigger a recalculation in the
form, of course).
You might like to investigate the "Expression Builder". It takes a
little while to master, but it eventually makes it easy to create even
the most complicated expressions. You have to be systematic, as it's
easy to leave "placeholders" dangling, but if you start with simple
expressions it's a godsend.
This might also be useful:
http://allenbrowne.com/casu-14.html
or this:
http://www.dummies.com/how-to/content/
creating-a-calculated-field-with-access-2007.html
HTH
Phil, London
