 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / February 2008How do you get the maximum value to display in text box form
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. 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? > In a form, if I have 4 text boxes and 3 of them will contain a number. How > do [quoted text clipped - 5 lines] > the > numbers on the form, which could be different for various records. 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  SignatureDave Hargis, Microsoft Access MVP > 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. 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 > Use a function like this as the control source of text box 4: > [quoted text clipped - 24 lines] > > 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. 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? > 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. 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. >I know that access is different than excel but why do we use if statements >in [quoted text clipped - 9 lines] >> the >> numbers on the form, which could be different for various records. 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 [quoted text clipped - 25 lines] > >> the > >> numbers on the form, which could be different for various records. 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”) [quoted text clipped - 44 lines] >> >> the >> >> numbers on the form, which could be different for various records. 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. > 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? [quoted text clipped - 4 lines] > > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.