Calculating minimum on text field

[matpj]

I have a text field that contains version numbers in the format:

10.5.0
9.13.1
12.1.0 etc
I need to sort these to get the minimum value that appears.
I have used the min function, but that returns 10.5.0 in the example above, where it should return 9.13.1

can anyone tell me how it is possible to do what I want to do?

 

[matpj]

sorry to drag this one up again,
Been on holiday for a while (lucky me!)

how can I amend this code to ignore results containing null values?
I think it is erroring because of them, as many of the records don't contain a value for Version Number
CODE
Public Function EvaluateVersionNumber(VersionNumber As String) As Integer

Dim arr As Variant
Dim i as Integer

arr = Split(VersionNumber, ".")
For i = 2 To 0 Step -1
EvaluateVersionNumber = EvaluateVersionNumber + (arr(i) * (10000 / (10 ^ (2 * i))))
Next
End Function



thanks,
Matt

 

[PHV]

Take a look at the Nz and IsNull functions.
Be aware that only Variant can hold null value, so you may have to change the type of the function parameter.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

We have discussed this problem in several other threads. This example pulls the first three octets of an IP address.

SELECT myTable.myField,
InStr([myField],".") AS a,
InStr([a]+1,[myField],".") AS b,
Left([myField],[a]-1) AS firstOctet,
Mid([myField],[a]+1,-[a]-1) AS secondOctet,
Mid([myField],+1,3) AS thirdOctet
FROM myTable;

Note that the field "thirdOctet" does not need to always have three characters. It will return accurate values even if the third octet is only one or two digits.

I think that Access will force you to display a and b. So I would not show the users this query (a and b would confuse them). I would use this as the data source for a second query. The second query would sort on each octet.

 

[matpj]

hi Steve,

I have tried this, but get #error in fields 'b', 'firstOctet', 'secondOctet' and 'thirdOctet' and nothing in the first field or field 'a'in a lot of records.
I think this is because sometimes the field is empty.
I have put other criteria after your SQL:

WHERE PRIMARY_VERSION (my_feild) IS NOT NULL, but this doesn't have any effect.

do you know how I can incorporate the 'not null' bit in the first bit of SQL?

Matt

 

[OhioSteve]

I am not a network guy. However, I suspect that all IP addresses have four octets, and all octets have at least one character. If that is the case, then the following example excludes IPs with 0-3 octets. It also displays all four octets, not just three.

SELECT
myTable.myField,
InStr([myField],".") AS a,
InStr([a]+1,[myField],".") AS b,
InStr(+1,[myField],".") AS c,
Left([myField],[a]-1) AS firstOctet,
Mid([myField],[a]+1,-[a]-1) AS secondOctet,
Mid([myField],+1,[c]--1) AS thirdOctet,
Mid([myField],[c]+1,3) AS fouthOctet
FROM myTable
WHERE (((Len([myField]))>7));

 

[matpj]

Steve,

i'm just adapting this for a 3 'octet' version number.
My problem is that some of the version number records in my table are blank, and so this script returns an error.

I just need to include a statement to check for a null value before it executes the 'InStr' bits!

 

[CosmoKramer]

My problem is that some of the version number records in my table are blank

Is the entire version number field blank, or is it missing a 'node'?? (i.e. 12.1)

Hoc nomen meum verum non est.

 

[matpj]

the entire field is blank!

 

[CosmoKramer]

Maybe the field is not null, it might be a blank. What if the criteria for your version number only looked for records that had a period?? Something like:

Like "*.*"

Hoc nomen meum verum non est.

 

[OhioSteve]

Read my sql. I wrote:

WHERE (((Len([myField]))>7))

That means, "count the number of characters in myField. Only evaluate records if the length of myField is greater than seven." If myField was blank, then the length = 0. So my sql definetely excludes blank records. Incidentally, the period character is a valid ascii character, so it will be counted.

I choose this test because I thought that you had four octets in every record. Now I see that you are actually recording version numbers, and the version numbers just have three parts. So perhaps an even better test would be:

WHERE (((Len([myField]))>5))

That would include "9.9.9", but not "9.9".

Of course you should also adopt my sql so that it does not calculate the fourth octet.

 

[CosmoKramer]

Going back to matpj's original post, the goal was to find the lowest single version number, sorting numerically on each node. If you look at my earlier post that starts with: [tt]OK, OK, I guess it pays to read the ENTIRE thread[/tt], I believe that function and query will accomplish just that. To ignore the null or empty records, add this WHERE clause:

SELECT TOP 1 [YourTable].VersionNumber
FROM [YourTable]
[COLOR=red]WHERE ((([YourTable].VersionNumber) Like "*.*"))[/color]
ORDER BY CInt(Left([VersionNumber],InStr([VersionNumber],"."))), CInt(Mid([VersionNumber],InStr([VersionNumber],".")+1,InStr([VersionNumber],"."))), IIf(CharCount([VersionNumber],".")=1,"",CInt(Right([VersionNumber],((Len([VersionNumber])-(Len(CInt(Left([VersionNumber],InStr([VersionNumber],"."))))+Len(CInt(Mid([VersionNumber],InStr([VersionNumber],".")+1,InStr([VersionNumber],"."))))+2))))));

Hoc nomen meum verum non est.

 

[matpj]

I have saved the code as a module called CharCount, but when I run your SQL above I get the error 'Undefined Function CharCount in expression'