Strange results of a VBA IF Statement

[cyd44]

I have a form to enter an Booking for an adventure park. I have written a VBA procedure to calculate cost of a booking and work out any discount. This is a simple algorithm and works ok except that it places the right data into the wrong Form variable.

My code is as follows

Dim Cost As Currency
Dim APrice As Currency
Dim Discount_Due As Boolean
Me.Disc = 0
APrice = Nz(DLookup("[ACost]", _
                     "[tblActivity]", _
                     "[AName] ='" & Me.Adventure & "'"))
 
"This gets the cost from Activity Table based upon Name of Activity Chosen"
 
Me.Price = APrice * Me.Num_in_Party
If Me.Num_in_Party > 9 Then
Me.Disc = Me.Price - (Me.Price / 10)
Me.Total_Cost = Me.Price - Me.Disc
MsgBox (Me.Disc & Me.Total_Cost)
Else
Me.Disc = 0
Me.Total_Cost = Me.Price - Me.Disc
MsgBox (Me.Disc & Me.Total_Cost)
End If
Me.Payment_Date = Me.Date_of_Booking
Me.Cust_Type = "Private"
MsgBox (" Booking Details Are Calculated")
End Sub

The logic will calculate discount at 10% if the number in the party is greater than 9, else it should set discount as 0.

The results are
1. When I make the number greater than 9 the results in the form are correct.
2. When less than nine it puts the discount value in the total cost field and the Total Cost value into the Discount Field.

It also switches the values around using the MsgBox captures in the code.

This is illogical to me as the statement uses the same vairaibles for the If and the Else and appears to switch around giving the right values to the wrong variables.

Could anyone please put me out of my misery here?

 

[John Big Booty]

I'd write that a little differently;

Dim Cost As Currency
Dim APrice As Currency
Dim Discount_Due As Boolean
Me.Disc = 0
APrice = Nz(DLookup("[ACost]", _
                     "[tblActivity]", _
                     "[AName] ='" & Me.Adventure & "'"))
 
"This gets the cost from Activity Table based upon Name of Activity Chosen"
 
Me.Price = APrice * Me.Num_in_Party
If Me.Num_in_Party > 9 Then
     Me.Disc = Me.Price * 0.1
Else
     Me.Disc = 0
End If

Me.Total_Cost = Me.Price - Me.Disc
MsgBox ("Discount of " & Me.Disc "applied, Total Cost " & Me.Total_Cost)

Me.Payment_Date = Me.Date_of_Booking
Me.Cust_Type = "Private"
MsgBox (" Booking Details Are Calculated")
End Sub

 

[John Big Booty]

In your Code you use;

Me.Disc = Me.Price - (Me.Price / 10)

this sets your discount at 90% of the total price, you then go on to use the formula;

Me.Total_Cost = Me.Price - Me.Disc

Which if you have a party of ten means you would only be charging 10% of the total price a very generous discount

 

[cyd44]

Many thanks, your code works fine and I had the Form fields mixed up hence I was getting what I thought were false results.

Appreciate your help