Convert text to number find Max

[HomeGrowth]

I have these records

DocNumber, VersionNumber(text field)
A,1.0
B,1.0
B,2.0
c,9.0
c,10.0

The result that i want is
A,1.0
B,2.0
c,10.0

In query, I convert the VersionNumber from text to number using CInt(VersionNumber). Now I want to find out which is the Max. In define the Totals, DocNumber is Group By and VersionNumber is Max. Access returned an error stating 'Data type mismatch in criteria expression'.

 

[SkipVought]

Select [b]DISTINCT[/b] ......

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[PHV]

SELECT DocNumber, Max(Val(VersionNumber))
FROM yourTable
GROUP BY DocNumber

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

 

[HomeGrowth]

PHV

Thank for the sql code, it work fine. How do I retrieve the rest of the fields that belong to these record?

 

[HarleyQuinn]

List them in the SELECT and GROUP BY e.g.

SELECT DocNumber, Field2, Field3, Max(Val(VersionNumber))
FROM yourTable
GROUP BY DocNumber, Field2, Field3

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.