Enter price to a Report from a table

[ArneGolf]

I am having trouble getting my Invoice report to add the fee dependant on
which option is selected. This works =IIf([Family Membership]=-1,535,"") but
I do not want to manually change the 535 if the price changes. I created a
table called fees and various fields. In this example I want it to enter the
value of the "FamilyMembershipFees" field instead of the 535 I used in the
IIF statement above.

 

[Stefan Hoffmann]

hi Arne,

I am having trouble getting my Invoice report to add the fee dependant on
which option is selected. This works =IIf([Family Membership]=-1,535,"") but
I do not want to manually change the 535 if the price changes. I created a
table called fees and various fields.

I would use a normalized table here:

Fee: FeeName (PK), FeeValue
"Family Membership", 535

Using either DLookup

DLookup("FeeValue", "Fee", "FeeName='Family Membership'")

or a proxy function in a standard modul like this:

Private m_Fee As DAO.Recordset

Public Function FeeFamiliyMembership() As Currency

If m_Fee Is Nothing Then
Set m_Fee = CurrentDb.OpenRecordset("Fee")
End If

m_Fee.FindFirst("FeeName = 'Family Membership'")
FeeFamiliyMembership = m_Fee![FeeValue]

End Function

In this example I want it to enter the
value of the "FamilyMembershipFees" field instead of the 535 I used in the
IIF statement above.

Use DLookup():

=IIf([Family Membership]=-1,
DLookup("FamilyMembershipFees", "Fees"),
"")

or if your fees table always contains exactly on record a join. Add the
table to every record source or query.

mfG
--> stefan <--