Condition in report

I am trying to set a condition in a text box so that I can

find the largest value in column (x) and return the value in column (y) on the same row. Thanks for any suggestions.

Thomas

Hi Thomas,

There are probably several ways you can do this, but the method that I use is creating a recordset using the DMax function and then retrieving the value from the specific field that I want.

You can try this code behind a command button that will put the value of column Y into a text box after finding the max value of column X.

Dim db As DAO.Database
Dim rs As Recordset
Dim strField As String
Dim strSQL As String
Dim strItem As String
strItem = DMax("[ColumnX]", "TableName")
strSQL = "SELECT TableName.* " & _
"FROM TableName " & _
"WHERE [ColumnX] =" & strItem
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
strField = rs!ColumnY.Value
End With

txtField = strField

Just substitute the table and field names as well as your text box names in place of the items in bold and italics.

lwells

Thanks for the info but I will need a little more details. I have yet to use a command button. Where do you input this code and where do you substitute the text box?

The code was just an example of some code that could be used in your application. Can you possibly explain what exactly you are wanting to do and I will modify the code for you. Your original post said you were trying to set a condition for a text box. What did you mean by that?

lwells

In the report header I have Profit$ and Salesperson. The detail section contains all the related info of Profit$ and corresponding salesperson. The footer contains a summary: Total Sales, Total Profit$, and Profit%. I would like the Salesperson with the highest Profit$ to be in this summary with their Profit$ figure.
I really appreciate you help on this.

What you're wanting to do can't be done directly in a text box. You have to incorporant lwells code or link the cell to a query. If you used a query it would do it automatically, vs a command button.

Command buttons are the things you click on do make something happen. such as "Ok", "Cancel", "Clear"

Thomas,

In this case, in your summary section of your report, place an unbound text box to display the Sales Person name and use the code that I gave you in the On Activate event of your report. You can also place an unbound text box to display the profit amount and use the Dmax function to get that value as well.

Or as MrBullwinkle suggested, create a query to obtain the same information for your report without writing the code.

lwells

Do I need to make more than 1 query to get this info? If I make another query that adds all the seperate Profit$ with corresponding salespersons I am back with the same info I have on the report.

You only need one query to get the max record(s) to display (separate from the query used in your report). Just set the criteria using the DMax function in the criteria grid under the field for Profit$. This will then give you the query showing the record(s) that you are looking for. In your report footer, arrange your text boxes where you want them, and then for the control source for each text box use the DLookup function to find the Profit$ and Sales Person field from the query.

The syntax for your control source of the text boxes would be:
=DLookup("[SalesPerson]","NameofQuery") and =DLookup("[Profit$]","NameofQuery")

You may have to plan on what to do if two different Sales people have identical Profit$ and maybe come up with some criteria to act as the tie breaker.

Will that help any?
lwells

No quite but thanks for all your help. I keep getting this message when I try to run the qry

"The expression you entered contains invalid syntax
You may have entered an operand without an operator"

and it highlights the second Dim in your code.

Thomas

You probably don't have the recordset references loaded. I'm not exactly sure what ones they are.

Add the references to the DAO object library. In your code window go to Tools/References and scroll down until you find the MicroSoft 3.XX Object Library. Depending on your version the actual number will vary.

lwells

sometimes you would have to prioritize the references in order for them to work,
i think its microsoft DAO 3.6 object library,