How do I grab the largest number in a table? 2

[robojeff]

How do I grab the largest number in a table?

I have a query that does this but I would like to grab this number and assign it to a variable that I can increment and embed it into a string of characters from inside of an event procedure...

There must be an easy way to do this...

thanks

SELECT DISTINCTROW Max([Mtbl].[DCode]) AS [Max Of DCode]
FROM Mtbl;

 

[dhookom]

How about:

Dim dblMaxDCode as Double
dblMaxDCode = DMax("DCode","Mtbl")

Duane
Hook'D on Access
MS Access MVP

 

[robojeff]

Thanks Duane-

your solution works great and is very simple!

 

[robojeff]

Dim dblMaxDCode as Double
dblMaxDCode = DMax("DCode","Mtbl")

This works really well, but I just realized that if this maximum DCode number (when incremented) becomes greater than 15, then I have to increment another number in the table called SBU and set DCode to zero.

This changes everything as I now have to search for the maximum value of SBU in the table and then the maximum value of DCode that exists with the maximum value of SBU.

In other words I need to set the table to look like this:

Dcode . . . SBU
13 . . . . . 1
14 . . . . . 1
15 . . . . . 1
0 . . . . . 2 <- need to increment SBU & reset DCode
1 . . . . . 2
2 . . . . . 2

Is there an easy way to modify this in order to search for the highest value of DCode of the highest value of SBU?

Thanks

 

[PHV]

dblMaxDCode = DMax("DCode","Mtbl","SBU=" & DMax("SBU","Mtbl"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886