Highest value of an alpha-numeric field (1 Viewer)

[fat controller]

I have a table which has a text field containing alpha-numeric information, simlilar to the below:

850.1
850.2
860.01
860.04
979 - A
979 - B
1001.2
1001.3

This data is historical, and cannot be changed as it is used outside the database as a reference number.

What I need to do is to have a small query that shows me the highest number from that field, ignoring anything after a decimal point or hyphen - so from the list above, 1001 would be the highest number. Is there a way to do this?

EDIT - I have a workaround that seems to work for the time being, which is to have a query which returns records where the ID (autonumber) is above a certain value - hence it returns only those where this alpha-numeric field is above 1000. This kinda gets me out of the hole, at least until it reaches 9999 - BUT it is less than elegant, so any suggestions gratefully recieved - - and no, the field cannot be changed from text to a number....

 

[Minty]

Have you looked at the Val() function? https://www.techonthenet.com/access/functions/numeric/val.php

 

[1268]

I have a table which has a text field containing alpha-numeric information, simlilar to the below:

850.1
850.2
860.01
860.04
979 - A
979 - B
1001.2
1001.3

This data is historical, and cannot be changed as it is used outside the database as a reference number.

What I need to do is to have a small query that shows me the highest number from that field, ignoring anything after a decimal point or hyphen - so from the list above, 1001 would be the highest number. Is there a way to do this?

EDIT - I have a workaround that seems to work for the time being, which is to have a query which returns records where the ID (autonumber) is above a certain value - hence it returns only those where this alpha-numeric field is above 1000. This kinda gets me out of the hole, at least until it reaches 9999 - BUT it is less than elegant, so any suggestions gratefully recieved - - and no, the field cannot be changed from text to a number....

Iif(Left(Right([yournum],2),1)=".",mid([yournum],1,len([yournum])-2,mid([yournum],1,len([yournum])-4)) or something like that assuming the data is constantly .x or _-_A.

Yournum is the field you need to inspect. Put this in the build function of your query and update yournum.

Once you get it working change from select query to a max query.



Sent from my SM-G950U using Tapatalk

 

[arnelgp]

i made a small function that
can be called from your Query.
copy and pasted in Standard Module.

Public Function fncNewValue(ByVal pVariant As Variant) As Variant
    Dim part1, part2
    Dim pos As Long, pos2 As Long
    fncNewValue = pVariant
    pos = InStr(pVariant, ".")
    If pos > 0 Then
        fncNewValue = DoPart(pVariant, pos)
    Else
        pos = InStr(pVariant, "-")
        If pos > 0 Then
            fncNewValue = DoPart(pVariant, pos)
        End If
    End If
End Function

Private Function DoPart(pVariant, pos)
    Const alpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Dim part1, part2
    Dim pos2 As Long
    part1 = Left(pVariant, pos - 1)
    part2 = Trim(Mid(pVariant, pos + 1))
    If IsNumeric(part2) Then
        part2 = Val(part2)
    Else
        pos2 = InStr(alpha, part2)
        If pos2 > 0 Then
            part2 = pos2
        End If
    End If
    DoPart = part1 & part2

End Function

your query should look like this:

SELECT TableName.FieldName, Val(fncNewValue([FieldName])) AS Expr1
FROM TableName
ORDER BY Val(fncNewValue([FieldName])) DESC;

you can remove Expr1 from the query.

SELECT TableName.FieldName
FROM TableName
ORDER BY Val(fncNewValue([FieldName])) DESC;

replace Tablenname, FieldName with your tablename
and fieldname to sort.

 

[jdraw]

Try this where fatNo is your text field and tblFatSeq is your table name

SELECT fatno
FROM tblFatSeq
ORDER BY Val(IIf(IsNumeric([fatno]),[fatno],Val([fatno]))) DESC;

fatno
1001.3
1001.2
979 - B
979 - A
860.04
860.01
850.2
850.1

 

[Mark_]

Try this where fatNo is your text field and tblFatSeq is your table name

Are you calling his table FAT? How totally insensitive! Not all tables can be slim like those model tables...

 

[fat controller]

You guys are just brilliant, thank you

I will have a play with this later, and get back to you I am liking the look of arnelgp's function, as it may prove useful further down the line. Could I use this same function for an unbound combo-box to have the records appear in descending order based on the value of the alpha-numeric field?

 

[arnelgp]

Yes maan it can.

 

[The_Doc_Man]

Working too hard, guys...

As long as the field begins with a number, VAL() simply evaluates the number and STOPS when it hits an impossibility. More specifically, using the "Immediate Window" to test this, I verified that

VAL( "1001.3" ) returns 1001.3
VAL( "979 - B" ) returns 979

IIRC, the first character in the input string that CAN'T be part of a decimal number stops the parse. So the space between "979" and "-" would stop and give you the digits it found. If you removed the spaces, the dash would stop it, too.

So you could EASILY sort based on VAL(ID) pretty much as-is. The only fly in the ointment is if that field could ever BEGIN with an alphabetic or punctuation character. However, that didn't seem to be an issue. As long as it is NOT an issue, don't re-invent wheels. Use the tools already in the tool kit.

 

[jdraw]

Doc,

I agree --I started with that. Then, settled on the approach in #5.
Not infallible, but OK with OP's data.
I suspect arnel's function or similar is needed to handle the various alphanumeric suffixes.

 

[The_Doc_Man]

What I need to do is to have a small query that shows me the highest number from that field, ignoring anything after a decimal point or hyphen

In fact, INT(VAL(x)) will do exactly this. It appears that Fat Controller doesn't CARE about the alphabetic suffixes.

This is something one could do via DMAX( "INT(VAL([x]))", "table-name" ) pretty easily. At WORST, you might need an NZ inside there if it is possible that the ID field would ever be null or blank. So the expression would look a little ugly... who cares as long as you can write it once and walk away from it?

BigNum = DMAX( "INT( VAL( [X] ) )", "table-name", "NZ( [X], [COLOR="Red"]''[/COLOR] ) <> [COLOR="red"]''[/COLOR]")

The items in red are single-quote / apostrophe characters. The others are normal double-quote characters. As long as you have no leading alphabetics or punctuation, this would work. And it would even work if the leading punctuation was either a plus sign or minus sign.

 

[MarkK]

...a small query that shows me the highest number from that field, ignoring anything after a decimal point or hyphen

SELECT Max(Int(Val(Field1))) FROM tTable;

Mark