Solved

# Display highest value in a numeric series by dmax or a query

Posted on 2011-05-02
270 Views
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
Question by:DavidDF1913

LVL 84

Accepted Solution

DMax should work:

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

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

LVL 119

Expert Comment

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

LVL 119

Assisted Solution

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 Comment

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

LVL 119

Expert Comment

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 Closing Comment

This worked fine. Both solutions helpful. Thank you.

David
0

## Featured Post

### Suggested Solutions

TaskManager all tabs values are 0% 4 42
Sql code problem 6 14
Printing Problem 13 21
Create exported XLS from Query 19 2