• Status: Solved
• Priority: Medium
• Security: Public
• Views: 276

# 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”,”transactionstbl”,”custno = [customerno]”)
And other combinations but no luck yet. Maybe dmax is not suitable since there are two tables. Any help will be appreciated.

David

0
DavidDF1913
• 3
• 2
2 Solutions

Infotrakker SoftwareCommented:
DMax should work:

DMax("Transactions", "Transactionstbl", "CustomerNo=" & me.YourCustomerNumberField)

Assuming CustomerNo is a Numeric field, that should work.
0

Commented:
you will need a function to do that, place this codes in a regular module

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("Transactions", "Transactionstbl", "CustNo=" & me.txtCustomerNo))

if custno Is text Data type

me.text0=fncGetMax(dlookup("Transactions", "Transactionstbl", "CustNo='" & me.txtCustomerNo & "'"))

0

Commented:
or this variation of the codes

Function fncGetMax(vStr as string) As Double
Dim j, xMax, sArr
sArr=Split(vStr,",")
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
0

Author Commented:
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

0

Commented:
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()?
0

Author Commented:
This worked fine. Both solutions helpful. Thank you.

David
0

## Featured Post

• 3
• 2
Tackle projects and never again get stuck behind a technical roadblock.