Min function on two fields (combo boxes) in form

[MajorYoshi]

Good day,

(using Access 2003) I'm working on a 'little' database for our weekly Battletech/Mechwarrior sessions. It's a character generator. I'm doing most of the work through a form right now and would like for it to do some calculations based on selections that are made from a combo box.

I'm using the control source in one place to make a calculation based on the combo box selection.

so I have a text box that has the following in the control source:

=15-[Att_Int]

This works correctly. Whatever number is selected from [Att_Int] is subtracted from 15 and displays in the text box just fine.

So I thought I would try to dig into the Min function to add a bit more automation to this form.

What I am trying to do is find the lowest of the selections from two combo boxes and display them in a text box just like the example above.

In the control source for the new text box I have:

=Min([Att_Rfl] Or [Att_Int])

All it is returning is: -1

The two combo boxes in the current record are currently set at [Att_Rfl]=6 and [Att_Int]=7 so with the desired MIN function the text box should be returning the Att_Rfl score (6).

I'm also trying to do a Max in a similar manner for a different field. Obvously I've got something wrong!

Please note that I know nothing of VB. That's why I'm trying to get it to work through the Control Source, if possible.

Thanks bunches,
Major Yoshi

 

[TheAceMan1]

How are ya MajorYoshi . . . . .

[blue]What I am trying to do is find the lowest of the selections from two combo boxes and display them in a text box just like the example above.[/blue]

Right idea . . . wrong function. You need to use the [blue]IIf[/blue] function which has the following setup:

[blue]IIf(expr, return data if [purple]expr true[/purple], return data if [purple]expr false[/purple])[/blue]

So . . . with you substituting proper names for X & Y:

To Get Min:

[blue]=IIf(X<Y, X, Y)[/blue]

To get Max:

[blue]=IIf(X>Y, X, Y)[/blue]

See Ya! . . . . . .

 

[MajorYoshi]

Hi AceMan1. I can tell you I'm doing MUCH better now!

This works like a champ (as you knew it would ;-). Many, many thanks. My wife and I have been trying to get this to go for a few hours now. (She offered to make you brownies btw)

I almost had it figured out using a couple queries and a sql join (found that on another forum) so that it would display all of the stats I need to query on in one column, allowing me to use the MIN expression function. But, when I tried this it gave me a lovely error about not having the correct VB automation object loaded (which I thought was very odd since it was just a SQL query and I haven't messed with the VB Resources other than adding Excel...).

But, this works very nicely. Again, many thanks to you!

Be well,
MajorYoshi