I'm creating an invoice using a sou 1

[TrekBiker]

I'm creating an invoice using a source that includes a LoyaltyDiscount, and this is displayed on the report.

Depending on its value I want to display a thank you message in a text box called DiscountText in the footer section. The code below in the Detail section doesn't fire so I get the text every time. Tried repeating the code in the footer but it still doesn't fire.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

   'Controls whether or not to show Loyalty Discount
    If Me.LoyaltyDiscount = 0 Then
        Me.DiscountText.Visible = False
    Else
        Me.DiscountText.Visible = True
    End If

End Sub

This same invoice is a redesign of one where the Loyalty Discount was calculated in the report instead of being in the source query, and this is the only difference, I think. LoyaltyDiscount displays properly in the new design.

Any clues?

 

[TrekBiker]

Sorry, goofed by not including a subject.

 

[dhookom]

Why use code? Can't you simply set the control source to something like:

=IIf(Nz([LoyaltyDiscount],0) = 0,"", Format([LoyaltyDiscount],"percent"))

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks Duane, also set the box so it could shrink.

This is the new source, which works both with zero or positive LoyaltyDiscount.

=IIf(Nz([LoyaltyDiscount],0)=0,"","We are pleased to include in your Invoice amount an additional Loyalty Discount of £" & Round([LoyaltyDiscount],2) & " based on your recent orders.")

 

[dhookom]

The only enhancement I would offer is to store the "We are pleased..." in a table so it's not hard-coded in an expression. Everything in this world changes so I typically store data values in tables where they can more easily be maintained.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016