• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1035
  • Last Modified:

Troubleshooting VBA Min/Max Code

I have the following code that shows the values in a MsgBox. It is a small part of the programme and I am testing it. The MsgBox  is not being shown....Can you help please!!

Private Sub Command78_Click()   'This is the VBA for Main Plans and Not for Corporate or Family Plans
On Error GoTo Err_Command78_Click

Dim iContractTermServed As Double
Dim iContractTermRemaining As Double
Dim iContractTermRemaininginYears As Double
Dim iContractValueRemaining As Double
Dim iContractvalueRemainingMax As Double
Dim iContractTermServedinYears As Double

iContractTermServed = Me.Termination_Date.Value - Me.Main_Pt_Contract_Start_Date
iContractTermServedinYears = iContractTermServed / 365
iContractTermRemaining = Me.Contract_End_date.Value - Me.Termination_Date.Value
iContractTermRemaininginYears = iContractTermRemaining / 365
iContractValueRemaining = iContractTermRemaininginYears * Me.Plan_Premium * 12
iContractvalueRemainingMax = 1.5 * Me.Plan_Premium * 12  

If iContractTermServedinYears <= 0.25 Then  
    masgBox = IIf(iContractvalueRemainingMax < iContractValueRemaining, CDbl(iContractvalueRemainingMax) * 0.9, CDbl(iContractValueRemaining) * 0.9)

End If

    Exit Sub

    MsgBox Err.Description
    Resume Exit_Command78_Click
End Sub
2 Solutions
Wayne Taylor (webtubbs)Commented:
Hi robbhat,

The first thing I've noticed is the spelling error. 'masgBox' should be MsgBox.


Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
webtubbs is correct, and the formatting of your msgbox is incorrect ... generally it'd be this:

MsgBox "Message  " & IIf(iContractvalueRemainingMax < iContractValueRemaining, CDbl(iContractvalueRemainingMax) * 0.9, CDbl(iContractValueRemaining) * 0.9), vbOkOnly, "Information"

Are you sure this is true:

iContractTermServedinYears <= 0.25

If not, then the msgbox won't be shown. Set a breakpoint (place your cursor at the beginning of the code and press F9), then execute the code. You can then step through the code with the F8 button. Note you can also "check" your statements during break mode by running them in the Immediate window (View - Immediate will show you this). For example, if you want to verify the above line returns true, type this in the Immediate window:

?iContractTermServedinYears <= 0.25

and press the Enter key ... this will return True or False, depending on the value
robbhatAuthor Commented:
I have rectified the MsgBox spelling and the problem is slightly worse. The Debug appears, highlights MsgBox and states "Function call on left-hand side of assignment must return Variant or Object"

Hi LSMConsulting, the value is true. The reason I know this is that on my code under each line I have a MsgBox displayed to show the answer (I took these MsgBoxes off to make it easier to trouble shoot for the Experts). But, in anycase, because of what I wrote above, the syntax must be faulty. Can you please help me?

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

try assigning the value of your IF statement to a declared variable, then use:

Dim myValue as <long, integer, whatever>

myValue = <your IF calculation>
Msgbox ("The value is " & myValue)
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You must format your MsgBox call correctly ... it should look something like what I posted earlier (there is no = in your MsgBox call, unless you are comparing the RESULT of your MsgBox to a value, which you're not - you're just displaying a message).

<The reason I know this is that on my code under each line I have a MsgBox displayed to show the answer >

Then your question makes no sense ... if the MsgBox is being shown, then you've answered your own question. I'd STRONGLY encourage you to not make assumptions regarding your data values, but to use the Immediate window to verify that your If statement is correct ... in many cases, what you assume is happening is not, in fact, what's going on. Your MsgBox would show a textual version of the calculation, which may or may not represent the actual computation. Using the Immediate window, and verifying whether this statement is actually True will provide solid proof.
robbhatAuthor Commented:
Do you mean the If statement calculation for selecting the Minimum value. If so, I have done this:

Dim iMyMinMaxValue1 As Integer

iMyMinMaxValue1 = iContractvalueRemainingMax < iContractValueRemaining, CDbl(iContractvalueRemainingMax) * 0.9, CDbl(iContractValueRemaining) * 0.9

But, its not accepting the syntax

can you help me with that?
iMyMinMaxValue1 = IIF(iContractvalueRemainingMax < iContractValueRemaining, CDbl(iContractvalueRemainingMax) * 0.9, CDbl(iContractValueRemaining) * 0.9)

or , if you also want to know which of the two values is the larger:

Dim strMaxValue as String

strMaxValue = IIF(iContractvalueRemainingMax < iContractValueRemaining, "Max value is ContractRemainingMax, and the value is " & CDbl(iContractvalueRemainingMax) * 0.9, "Max value is ContractRemaining, and the value is " &CDbl(iContractValueRemaining) * 0.9)

MsgBox "The Max value is " & strMaxValue, vbOkOnly

MsgBoix is a Function that displays a Message and can return, as the value of the function, an indicator of which button the user clicked, in order to close the displayed message box.  You cannot assign a value to the MsgBox (as in MsgBox = ....)

IIF is also a function (called the Immediate IF function, which tests the 'truth' of the first statement, and if it is TRUE, returns the value of the second statement, otherwise returns the value of the third statement:

Value = IIF(TestStatement, ValueIfTrue, ValueIfFalse)

if TestStatement is TRUE, Value = ValueIfTrue
if TestStatement is NOT TRUE, then Value = ValueIfFalse

I hope this clears things up for you.


robbhatAuthor Commented:
Out of all the questions I have posited, 95% have been answered by your Experts. They are fantastic. Some are extra fantastic like LsmConsulting and Arthur Wood to mention a few of the exceptional people helping us. These experts are extraordinary because they take time to explain. For novices like me this is invaluable. The site itself is PRICELESS. Because of the site I have managed to build an access website with a lot of effects which I could never have dreamt of. Keep it going.....Brill

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now