Display highest value in a numeric series by dmax or a query
I have two tables in two forms. CustomersTbl in form, customerfrm and transactionsTbl in sub form,transform.
The forms are linked by the numeric fields, customerNo (customertbl) and custno (transactionstbl)
Each customerno has several transactions
Sample data:
Customerno, transactions
2223 1,2,3,4,
2233 1,2,3,4,5,6
I want to see the highest transaction number used by a customerno that is currently being displayed. For example, if user is editing customer 2233, a text box will display 6.
I tried dmax(“transactions”,”trans
And other combinations but no luck yet. Maybe dmax is not suitable since there are two tables. Any help will be appreciated.
David
The forms are linked by the numeric fields, customerNo (customertbl) and custno (transactionstbl)
Each customerno has several transactions
Sample data:
Customerno, transactions
2223 1,2,3,4,
2233 1,2,3,4,5,6
I want to see the highest transaction number used by a customerno that is currently being displayed. For example, if user is editing customer 2233, a text box will display 6.
I tried dmax(“transactions”,”trans
And other combinations but no luck yet. Maybe dmax is not suitable since there are two tables. Any help will be appreciated.
David
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked:
DMax("Transactions", "Transactionstbl", "CustomerNo=" & me.YourCustomerNumberField
I put it in a text box on the main form and used onclick to show the result. How can I get it to pop up the new value whenever a new customer number is selected?
David
DMax("Transactions", "Transactionstbl", "CustomerNo=" & me.YourCustomerNumberField
I put it in a text box on the main form and used onclick to show the result. How can I get it to pop up the new value whenever a new customer number is selected?
David
how did you get the value 6 from this records for customerNo 2233?
Customerno, transactions
2223 1,2,3,4,
2233 1,2,3,4,5,6
using Dmax()?
Customerno, transactions
2223 1,2,3,4,
2233 1,2,3,4,5,6
using Dmax()?
ASKER
This worked fine. Both solutions helpful. Thank you.
David
David
Function fncGetMax(ParamArray sArr()) As Double
Dim j, xMax
xMax = Nz(sArr(0), 0)
For j = 1 To UBound(sArr)
If Nz(sArr(j), 0) > xMax Then
xMax = Nz(sArr(j), 0)
End If
Next
fncGetMax = xMax
End Function
to use the function
me.text0=fncGetMax(dlookup
if custno Is text Data type
me.text0=fncGetMax(dlookup