Get The Minimum Value From A Textbox

[Curious27]

I have a report where I am trying to get the minimum value from a calculated textbox named "TMetalic" which calculates the total of each component.

The main group named 'Caliber" shows different Calibers
The next group named "Component" lists three different components with detail items and quantities. In the Component Footer is where "TMetalic" sets, totaling the quantity of each component.

I want to place a textbox named "CanBuild" next to the group named "Caliber" to show a minimum value I can build based on the lowest total of all three components.

Example:
Caliber = 9mm
Components and Totals:
Brass = 2,000
Primer = 1,000
Projectile = 2,000
The minimum value is 1,000 Primers
Next to Caliber: 9mm is a textbox "CanBuild" showing "1,000" as the number of cartridges I can reload.

Any ideas, I have reading and trying sense yesterday.

[NeoPa]

Hi Curious.

I think you may already know that individual Component totals are no longer available once the next Component is processed and thus, by the time the Caliber total is reached only the very last of them is still available. Comparing them at this stage just can't work.

What you could do however, is to write some code in various Events to get around it.

The Calibre Header would set a stored variable to -1 to indicate that it's, as yet, unset.

The Format or Print Event of the Component Footer replaces this when the current value is less than the existing one or the existing one is unset (-1).

The Calibre Footer would set a special (unbound) TextBox to the value of this variable so that you get the value you want printed.

[Curious27]

Hi NeoPa

You are right, I did lose sight of the component totals not being available as individual totals. I'll write some code to separate the component totals so a minimum total can be placed in textbox "CanBuild".

Thanks again and have a Happy Thanksgiving.

[Curious27]

NeoPa

Here is an update as to how you and I have solved my problem. As stated above I wanted to place a textbox "CanBuild" next to the Group Header "Caliber" to show how many cartridges can be loaded, based on the totals of each component and selecting the lowest amount available.

What I did was create three textboxes in the Component Footer and naming them, txtTBrass, txtTPrimer, txtTProjectile. I made them not visible for the report aesthetics.
Then in the Control Source for each one, I put it's own expression;

=Sum(IIf([Component]='Brass',[mAvailQty],0))
=Sum(IIf([Component]='Primer',[mAvailQty],0))
=Sum(IIf([Component]='Projectile',[mAvailQty],0))

Doing this, pulled out each component, Brass, Primer and Projectile's totals. Then in the textbox "CanBuild" I placed it it's Control Source this expression;

=MinOf([txtTBrass],[txtTPrimer],[txtTProjectile])

Now in "CanBuild" I have the minimum amount of cartridges I can make, based on the component stock levels.

I would also like to thank you for the valuble lesson you taught me on the value of making a Public Function; we named as "MinOf". This was put together to solve a problem I had a couple months back where you told me it would be better to make a Public Function and have it available through out my project rather than writing an expression for the Control Source each time I needed to find a minimum value. Well, here I am calling on that public function and it works like a charm. Appreciated, is the fact that you took the time to teach me the How, Why and Where of this function.

For those interested in that function, I'll place it here.

Public Function MinOf(varA As Variant _
                    , varB As Variant _
                    , varC As Variant) As Variant
    If IsNumeric(varA) _
    And IsNumeric(varB) _
        And IsNumeric(varC) Then
        MinOf = varA
        If MinOf > varB Then MinOf = varB
        If MinOf > varC Then MinOf = varC
        
    End If
End Function

Again, Thank you.

[NeoPa]

Hi Curious.

How very pleasant to receive such a post. Very gracious of you :-)

I'm happy to see that, with a clearer understanding of the issue, you were able to find a solution, that was different from my suggestion, yet was a perfect solution for you.

[Curious27]

That's the beauty of programming, so many ways to achievement.

Have a good day.