Expression builder for default value

[Meg]

Years ago, I built a database for my company in approach
and am trying to now build it in access but am currently
stuck on one thing. I have a table
called "transactions". In that table I have three
fields:"transaction amount","commission rate",
and "commission amount". The commission amount is
usually "transaction amount"*"commission rate" but may
ocassionally be a set amount. I'm having a hard time
getting the syntax correct since I don't have fields to
drag into the formula the way Crystal has. Can anyone
help me past this hump? Thanks in advance.

 

[Lynn Trapp]

You need to calculate the commission amount in a query. You may have to
write an IIF() statement to insert amounts that are not supposed to be
calculated but can't advise you on that without more information about the
data and the circumstances under which you would NOT use a calculation. At
any rate, you do NOT want to store the commission amount unless you need it
for historical purposes.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp

 

[John Nurick]

Hi Meg,

Life in Access is simpler if you don't use spaces or special characters
in the names of fields, tables, etc. Call them CommissionRate instead of
commission rate, and so on.

You can't put a formula like this into the default value of a table,
because the default value is assigned at the moment the new record is
created - and therefore before the transaction amount and commission
rate are available.

Instead, use a form to display and enter the data, with a few lines of
VBA code to generate the default value once the transaction amount and
commission rate have been entered. Assuming the form has textboxes
called txtTransactionAmount, txtCommissionRate and txtCommissionAmount
to display the three fields, put something like this

If IsNull(Me.txtCommissionAmount.Value) Then
'no commission amount entered yet,
'so generate default value
Me.txtCommissionAmount.Value = Me.txtTransactionAmount.Value *
Me.txtCommissionRate.Value
End If

in the AfterUpdate event procedures of both txtCommissionRate and
txtTransactionAmount.