Get the maximum value of a table column in a certain range

[Akchayan]

Hello all,


I have a form that is connected to a table.
Table name: "tblCustomers


Form name: "foCustomerCapture
In the table "tblCustomers" I have a column called customer number. I have divided this customer number into 3 different categories
Category 1 are numbers from 100-199
Category 2 are numbers from 200-299
Category 3 are numbers from 300-399


And I want the maximum value of the categories to be displayed on the right side of my form.
It should look something like this:



I have imagined that the content of the control could look like this for category 1, for example: customer_number<200, MAX
Just from the logic, so the code is not right but that is the goal that should come out in the end.


I haven't found a solution yet. Do you have an idea what I should enter?

[ranman256]

set the source of the text boxes , or assign at form load:

txtBox1 = Dmax([field2Return], "table" ,"[Category 1] between 100 and 199")
txtBox2 = Dmax([field2Return], "table" ,"[Category 2] between 200 and 299")
txtBox3 = Dmax([field2Return], "table" ,"[Category 3] between 300 and 399")

[Akchayan]

set the source of the text boxes , or assign at form load:

txtBox1 = Dmax([field2Return], "table" ,"[Category 1] between 100 and 199")
txtBox2 = Dmax([field2Return], "table" ,"[Category 2] between 200 and 299")
txtBox3 = Dmax([field2Return], "table" ,"[Category 3] between 300 and 399")

I have entered this here in the Control Content field:

but it didnt work, do you know what i did wrong?

[Gicu]

I don't think you have a category set anywhere so try this:
txtBox1 = Dmax("Kundennummer", "tblKunde" ,"[Kundennummer] < 199")

Cheers,

[Akchayan]

I don't think you have a category set anywhere so try this:
txtBox1 = Dmax("Kundennummer", "tblKunde" ,"[Kundennummer] < 199")


Cheers,

Thank you very much! It worked.

[Gicu]

You're very welcome!
Cheers,