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?

 

[Craig0201]

It's returning quite correctly. 10.5.0 and 9.13.1 are both string so 1 does indeed have a lower ASCII number hence comes first.

What you're going to have to do is find a way of evaluating it into a number.

Assuming that there is a maximum of two . characters and a maximum of 2 digits in any one section----

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

For 10.5.0, this will return 105000 and for 9.13.1 will return 91301. 91301 is less than 105000 therefore your min will work.

Craig

 

[matpj]

thanks for that craig.

where do I put that code?
I am just using access with a Query at the moment, and then exporting the data to Excel.

 

[Craig0201]

Matt,

Paste it into a module and then use it in a query like any other function.

Craig

 

[matpj]

aaaahhhh!
thats what they are for!
I've never used them before!

i'll give it a go! thanks for you help,


Matt

 

[matpj]

VersionNumber being the field from one of the 3 table sin my query, do I need to specify the table?

e.g.:

"Public Function EvaluateVersionNumber(PVCSTable.VersionNumber As String) As Integer"

etc

 

[Craig0201]

Matt,

The function works on any field. Just copy and paste in then use like any other function. It even appears in Function Builder as a Custom Function

Craig

 

[matpj]

I seem to be getting runtime error 9 and run time error 6(overflow)

I thought that it was where some of the version numbers have NA and soem 5v4 etc (didn't realise they were in there)

but it is doing it on some 'normal' versions (12.0.0 etc)

 

[Craig0201]

Matt,

These values will need changing to the digits dot digits dot digits format to work. The errors are happening because I am splitting on the . character. When there are less than 2 dots then the For Next loop fails as the array is not the size expected.

Craig

 

[matpj]

oh :-(

there's no way I can change those as they are valid version numbers for some of our products.
I suppose I could report on the products seperately as your formula works so well!

thanks for your time.

 

[lespaul]

What you can do is modify that code slightly to count the number of '.' in each string and set the array to that instead of hard coding it.

I don't do much access coding so I'm not really sure what the exact syntax would be. Basically:

search each character of VersionNumber
if the character is a . then increment a counter
use the counter in the array

(the syntax below is more Delphi, but you should get the idea!)

Public Function EvaluateVersionNumber(VersionNumber As String) As Integer

    Dim arr As Variant
    Dim i as Integer
    Dim c as Integer

    i = 0
    c = 0
    While i < length(VersionNumber) do
      if VersionNumber[i] = '.' then
         c = c + 1

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

HTH

Leslie

 

[CosmoKramer]

Here's an example of part of a query that sorts IP addresses. It breaks up the field into integer parts and sort on those parts:

SELECT TOP 1 YourTable.NumText
FROM YourTable
ORDER BY CInt(Left([NumText],InStr([NumText],"."))), CInt(Mid([NumText],InStr([NumText],".")+1,InStr([NumText],"."))), CInt(Right([NumText],((Len([NumText])-(Len(CInt(Left([NumText],InStr([NumText],"."))))+Len(CInt(Mid([NumText],InStr([NumText],".")+1,InStr([NumText],"."))))+2)))));

This should work providing all instances of "NumText" have 3 sets of integer values, separated by periods.

Substitute your table and field name(s) and you should be all set.....


Hoc nomen meum verum non est.

 

[lespaul]

but the problem is not all of the strings have 3 .'s!

Leslie

 

[matpj]

thank you all for your posts.
Can anyone confirm if Leslie's code will work in Access?
I don't know Delphi or Microsoft VB..

 

[Craig0201]

Mat,

You can modify the code to deal with different numbers of . characters but it still won't deal with the NAs, 5v3 etc.

Public Function EvaluateVersionNumber(VersionNumber As String) As Double
    Dim arr As Variant
    Dim i As Integer

    arr = Split(VersionNumber, ".")

    Do While i < UBound(arr) + 1
        EvaluateVersionNumber = EvaluateVersionNumber + (arr(i) * (10000 / (10 ^ (2 * i))))
        i = i + 1
    Loop
End Function

Craig

 

[matpj]

Is there any way that some code can be written to see if the first of the 'segments' contains 1 or 2 digits and then put a 0 infront of it if it contains 1?
This would then turn 9.10.1 into 09.10.1.

would this then work?

 

[CosmoKramer]

OK, OK, I guess it pays to read the ENTIRE thread....

Copy & paste this function written by scriverb into a module (Bob, I hope you don't mind the inclusion of your code here):

Public Function CharCount(vSearchStr As String, vTargetStr As String) As Long
Dim i As Integer, vOccurance As Integer
vOccurance = 0
  For i = 1 To Len(vSearchStr)
      If Mid$(vSearchStr, i, 1) = vTargetStr Then
          vOccurance = vOccurance + 1
      End If
  Next i
CharCount = vOccurance
End Function

Use this function in your query to determine how many periods are in the field. If there is only one, substitute a Null for the third value:

SELECT TOP 1 [YourTable].VersionNumber
FROM [YourTable]
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]

do I need to change the code to map to my actual field names, or does the function just apply to what ever field I put the function against?

sorry but I have never used functions other than Min/Max!

 

[CosmoKramer]

Yes, you have to change the field and table names where necessary. This query is breaking up the version number into the separate numbers delimited by periods, then sorting on those numbers from left to right.

As Craig0201 stated earlier, it won't take into account any "non-period" delimiters.....

Hoc nomen meum verum non est.

 

[OhioSteve]

There is an alternative approach to this problem.

You could standardize the format of the values in this field. If everything had the format "##.##.##", then it would be easy to evaluate the field. There are many ways to guarantee that all of the data will have a certain format. For example, you could create a lookup table that lists valid values. Or, you could use an input mask. You could use a series of update queries to fix the existing data.