Get the maximum value for text data type 1

[qwerty70]

Dear All,

I have a bound form called "frmDrawings". This form is bound to table "tblDrawings", it has 2 fields: txtDrwgNum and txtRevNum.

My table contains the following data:

txtDrwgNum txtRevNum
20-SD-2001 1A
20-SD-2001 2F
20-SD-2001 3E

What I would like to do in my form is to insert a command command that when I clicked it, it should retrieve only the drawing number that has the highest revision which is "3E" as per my sample data.

Your help is highly appreciated.

qwerty70

 

[Remou]

Do you mean:

[tt]SELECT tblDrawings.txtDrwgNum, Max(tblDrawings.txtRevNum) AS MaxOftxtRevNum
FROM tblDrawings
GROUP BY tblDrawings.txtDrwgNum;[/tt]

 

[qwerty70]

Remou, thanks for your prompt reply. It works perfectly on alpha-numeric revision number. I tried to enter "10" but it is still showing "3A" as the latest.

Actually, our drawings here have to revision numbering formats; alpha-numeric and number.

Please help. Thanks in advance.

qwerty70

 

[missinglinq]

I think you need to use the Val function, which will return the numerical protion of your revision number and thus make 10 ahead of 3F. This worked on the data you provided:

SELECT [tblDrawings].[txtDrwgNum], Max(val([tblDrawings].[txtRevNum])) AS MaxOftxtRevNum
FROM tblDrawings
GROUP BY [tblDrawings].[txtDrwgNum];

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PHV]

SELECT A.txtDrwgNum, Max(A.txtRevNum) AS MaxOftxtRevNum
FROM tblDrawings AS A INNER JOIN (
SELECT txtDrwgNum, Max(Val(txtRevNum)) AS MaxOfVal
FROM tblDrawings GROUP BY txtDrwgNum
) AS B ON A.txtDrwgNum = B.txtDrwgNum AND Val(A.txtRevNum) = B.MaxOfVal
GROUP BY A.txtDrwgNum

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

 

[MichaelRed]

I think it is a bit more convoluted than addressed so far. Val will only pick the value of the first numeric substring. Assuming the samples are simplistic and do not reflect the pverall range of the actual (or even potential) recordset, I can easily envision the field having more complex values, such as:

20-SD-2001 1A.22
20-SD-2001 2.3F-A
20-SD-2001 3E100B

and so forth

In the more complex situation(s) val may be inadequet - but then there is some need for a more complete 'deffinition' of the 'highest value'.





MichaelRed

 

[PHV]

Michael, doesn't my suggestion handles such issues ?

 

[MichaelRed]

PHV, I honestly didn't check it and did not go to the nth degree in generating convolouted A-N strings for the rev number, but your soloution obviously works when the definition of highest value refers to the first numeric substring. But it is the DEFFINITION I was more concerned with. Try some values like:

A4b10
a4B10
b1c.145
XX_25

then you need a more detailed deffinition?

Do the a, b, c, XX have 'value'? Is A > a? Should one 'ignore' the alpha portion? look particularly at the first two of these. Numerically, the seem to be equal and if 'Case' is not considered (most db searchecs and sorts defaulf to ignore case?) they are equal. So, again, it is the application deffinition of highest which I feel is inadequate here.




MichaelRed

 

[qwerty70]

Thanks to all who exerted their effort in helping me sort this out.

PHV's code works 100% correct. It really suits to my requirements. Thanks PHV, a star for you.

Regards,

qwerty70