calculated field referencing a pull down menu

[Vernon]

Good morning,

The end goal of this question is to calculate the
obligation fees field, called "Obl fees", by multiplying
the Land Use Type, called "LU_Type", by the Units,
called "Units", fields. I have already added in the
following visual basic script to the LU_Type and Units
fields using the code builder in the Events Tab in the
field Before Update:

If Not IsNull(Me.[LU_Type] ) And Not IsNull(Me.[Units])
Then
Me.[Obl Fees] = Me.[LU_Type] * Me[Units]
End If

However, I forgot to address one major complication: the
LU_Type field is a pull down menu linked to a table
called "tbl_LU_Type". tbl_LU_Type includes two columns:
the first includes text listing the the names of the land
use types (ie single family, multifamily, retail) and the
other includes the corresponding monetary fees associated
with each (ie $2400.00, $5650.50)... How can I make the
equation reference only the second column of the
tbl_LU_Type table (called "Fee Per Unit") while the
LU_Type field shows only the name of the land use type
(not the monetary value)?

If you need any clarifications in answering this question,
please email me (e-mail address removed)

Thank you for your help!
Kendra

 

[Anne]

Hi Kendra -

The dfirst function would allow you to get the correct
amount from the table based on the LU_Type field.

dfirst("Fee_Per_Unit","tbl_LU_Type","LU_Type='" & Me.
[LU_Type] &"'")

(I'm assuming that LU_Type is a string, if it's a number,
omit the single ' before and after Me.LU_Type.)

This means 'return the fee_per_unit from tbl_lu_type where
the Lu_type in the table = the LU_type from the form.'

Another option, if you have a drop down box of the types
on your form, is to add a new, hidden column to the drop
down box and then reference the column like this: me.
[lu_type].column(1). (Careful 'cause the columns index
starting at zero so column #1 is really the 2nd column)

As an aside, are you sure you want to be storing this
calculated value in the table at all? It would be much
easier to just calculate it on the fly in a query any time
you needed it. I would only do this calculation if
there's some reason you need to store the calculated value
(For example if the fee amount charges over time and you
need to know the fee that was charged this particular
time. Although even in that case, I'd probably store the
fee per unit separately from the # of units and just
multiply in a query).

And one final aside, you can simplify your code by using
the the NZ function: nz(Me.[LU_Type],0) * nz(Me[Units],0)
would be zero in any case where either of the fields is
null.

Hope that helps, if not re-post to this thread. - Anne