If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
calculated field referencing a pull down menu
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 Thank you for your help! Kendra |
#2
|
|||
|
|||
calculated field referencing a pull down menu
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 -----Original Message----- 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 Thank you for your help! Kendra . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculated Field Problem: Picking a date based on value in another field | Dkline | Database Design | 3 | June 23rd, 2004 04:05 PM |
Assigning a calculated field to field table. | Eleazar | Using Forms | 0 | June 17th, 2004 07:03 PM |
Pull down menu (combo box) on chart sheet | Pablo | Charts and Charting | 1 | March 18th, 2004 10:33 PM |