How do you get the maximum value to display in text box form

[esparzaone]

In a form, if I have 4 text boxes and 3 of them will contain a number. How do
you get the maximum value to display in the 4th. I have tried many different
things, I can make it work for a single field in a table, but that finds the
value through all records. I just want it to display the max value from the
numbers on the form, which could be different for various records.

 

[BruceM]

You could use something like this as the Control Source of the 4th text box
(the underscore is for ease of reading here, and must be removed from the
Control Source):
=IIf([Field1]>[Field2] And [Field1]>[Field3],[Field1], _
IIf([Field2]>[Field1] And [Field2]>[Field3],[Field2],[Field3]))

You could use the same expression as a query field. Substitute the name you
choose for the field and a colon for the = sign:

HighNumber: IIf etc.

Will there ever be a "tie" for high number?

 

[Klatuu]

Use a function like this as the control source of text box 4:

Private Function HighNum() as String
Dim strMax as String

With Me
If .Text1 > strMax Then
strMax = .Text1
End If
If .Text2 > strMax Then
strMax = .Text2
End If
If .Text3 > strMax Then
strMax = .Text3
End If
If .Text4 > strMax Then
strMax = .Text4
End If
End With

HighNum = strMax
End Function

 

[earadcoolcip]

Hello,

This may seem strange and is way off topic; Klatuu, are you a BF2 player?
Ive seen that username quite a bit on BF2 Tracker.

Adam

 

[esparzaone]

I know that access is different than excel but why do we use if statements in
access and =max in excel to get the highest number?

 

[BruceM]

You said it yourself: they are different. Max in Excel looks for the
highest value in a range. You can build your own Access function, as Klatuu
has suggested, or modify it to loop through a variable number of controls
(maybe by using the Tag property, or the control name, or something else to
identify the controls in question). You could also loop through fields, but
the Tag function is out in that case. However, there is no built-in way to
identify a range because the table columns (fields) have names you give
them, not cell addresses, so there is no way to identify a range by using
cell addresses as you would in Excel. A1:E1 identifies six contiguous cells
in the first row of an Excel spreadsheet, but in an Access table the six
leftmost fields do not lend themselves to the same type of ready
identification.
Access is a relational database, while Excel is a flat file. A table
resembles a spreadsheet, but is an entirely different creature. Different
ways of thinking about and managing data apply.

 

[esparzaone]

The formula I am looking for is something along the lines of:
Dmax(“[A1]â€+â€[B1]â€,â€FORMNAMEâ€,â€[A1]â€+â€[B1â€]>â€&â€0â€)

I’m guessing the biggest issues are 1. you cannot specify a form as a
domain 2. You cannot have multiple fields in the expression or criteria.

 

[esparzaone]

The formula I am looking for is something along the lines of:
Dmax(“[A1]â€+â€[B1]â€,â€FORMNAMEâ€,â€[A1]â€+â€[B1â€]>â€&â€0â€)

Is there a query I should bring in to the form to calculate the highest
number? This is what I have so far. I populate the form with information
from the tables based off of a drop down box in the form. There could be
several rows and columns of data. I want to insert a text box to give the
max value by row. I am fair at access but I am not sure how to go about this.

 

[BruceM]

In response to another thread, a form cannot be a domain because it is a
sort of window on the data, not the record source itself. However, a bound
form has a record source, so you can use that as the domain. In regards to
multiple fields in the expression or criteria, you can do that, but the
syntax neeeds to change:

=Dmax("[A1] + [B1]","YourTable","[A1] + [B1] > 0")

It also seems to work to write the criteria a bit differently. Frankly,
there are some things about the use of quotes and ampersands in these
situations that I don't quite get.

=Dmax("[A1] + [B1]","YourTable","[A1] + [B1] > " & 0)

In any case the 0 in the criteria is a number, so it does not have quotes
around it.

Let's say YourTable has the following records (ID is the primary key):
ID A1 B1
1 1 2
2 2 2
3 2 1

DMax("[A1] + [B1]","YourTable") is 4 (A1 + B1 in record ID 2).
However, this is different from what you originally asked. DMax is a domain
aggregate function, which means it searches the entire domain for, in this
case, the highest A1 + B1 value. To search for the field with the highest
value you need an expression or function in a control or in the query being
used as the record source.

Klatuu has suggested a function that can be used as the control source of a
text box. To use the function, open the form's code module. To do this,
open the form in design view. Click View > Code (or use the code icon on
the toolbar). With the code window open, paste the suggested code. At the
top, just under Option Compare Database (or Option Explicit, it it's there),
is a good place. Modify the code to reflect the names of your actual text
boxes 1, 2, and 3 (in place of Text1, etc.). Click Debug > Compile to be
sure the code contains no syntax errors. If all is well, nothing will
happen; otherwise you will see a highlighted line of code. In the fourth
text box, set the control source to:
=HighNum()

You should be able to use the function in a calculated field in a query, as
long as you are using a mdb database with the Jet database engine. You are
probably using Jet (which ships with Access, and which you are using unless
you specifically set up your database with SQL server). I don't see an
advantage to that, but I expect it could be done. You would have something
like this in a new column in query design view:
HighestNum: HighNum()
Use the query as the form's record source, and bind a text box to
HighestNum.

By the way, a row is typically referred to as a record. It may appear as a
row similar to an Excel row if you are using datasheet view, but as I said
it is quite different.

The formula I am looking for is something along the lines of:
Dmax(“[A1]â€+â€[B1]â€,â€FORMNAMEâ€,â€[A1]â€+â€[B1â€]>â€&â€0â€)

Is there a query I should bring in to the form to calculate the highest
number? This is what I have so far. I populate the form with information
from the tables based off of a drop down box in the form. There could be
several rows and columns of data. I want to insert a text box to give the
max value by row. I am fair at access but I am not sure how to go about
this.

You said it yourself: they are different. Max in Excel looks for the
highest value in a range. You can build your own Access function, as
Klatuu
has suggested, or modify it to loop through a variable number of controls
(maybe by using the Tag property, or the control name, or something else
to
identify the controls in question). You could also loop through fields,
but
the Tag function is out in that case. However, there is no built-in way
to
identify a range because the table columns (fields) have names you give
them, not cell addresses, so there is no way to identify a range by using
cell addresses as you would in Excel. A1:E1 identifies six contiguous
cells
in the first row of an Excel spreadsheet, but in an Access table the six
leftmost fields do not lend themselves to the same type of ready
identification.
Access is a relational database, while Excel is a flat file. A table
resembles a spreadsheet, but is an entirely different creature.
Different
ways of thinking about and managing data apply.