Prev: Blank Page (header)
Next: My subreport shows in total report, but does not print.
From: ArneGolf on 13 Feb 2010 07:53
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.
From: Stefan Hoffmann on 13 Feb 2010 08:16
hi Arne,

On 13.02.2010 13:53, ArneGolf wrote:
> 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 <--
 | 
Pages: 1
Prev: Blank Page (header)
Next: My subreport shows in total report, but does not print.