Correct evaluation of 'greater than'?

[jonno_g]

Hi all,

I have a strange one for you. A few weeks ago I coded an app to extract attributes out of an AutoCAD drawing using VBA code running from MS Access. Initially I was surprised that it handled such a complex revision numbering system straight off the bat. I say complex, because there are basically two systems of revision numbering in one - major revisions are pure numeric, and 'in between' revisions are either pure alpha, or alpha-numeric (but with the number first). I'd rather not explain the whole history of it, as it took many months to develop to a functional level, but suffice it to say that it is not possible to change the revision numbering system so I must find a programmatic solution to this problem.

OK, history lesson over. The problem is this:

In essence, the typical progression of revisions goes like this: A, B, C, 0, 0A, 0B, 1,....etc.

All of the drawings display both the current and one immediately preceding revision and its detail. It is possible, therefore, to see a drawing with current rev. B, but also displaying the detail of rev. A. (both as attributed blocks). This is fine, as the VBA code simply grabs all of the detail from each of the blocks, places that data into an array, and then compares the two strings that contain the 'A' and the 'B', and then copies the detail from the array of B (the greater of A and B) into a new array and uses that new array to populate the database with the current revision detail. All good so far!

The problem occurs when you have a drawing that displays revisions C and 0. When I first wrote the code this would evaluate as C > 0 = true, but since I wrote that code I have received a new PC which is now evaluating that as C > 0 = false.

Now obviously many things could have changed seeing as I now have a new PC, I guess what I'm looking for is some advice as to where to start looking. The main thing that changed, apart from the hardware of course, is that I've gone from XP Pro to XP Pro x64. I'm still using the same version of MS Office (Office 2000 - don't ask!), and the same version of AutoCAD (2009). I've checked things like the regional and language settings, and as best I can tell they're the same as my old PC. At the moment I'm out of ideas. It doesn't make any sense to me why one system would evaluate it one way and another a different way.

BTW, here's the code that does the comparison:

If arAttr2(0).TextString > arAttr3(0).TextString Then
arCurrRev = arAttr2
Else
arCurrRev = arAttr3
End If

Any ideas will be greatly appreciated.

Cheers,...Jon.

 

[jonno_g]

If you look at an ASCII chart, you will see that numbers have a lower ASCII value than letters so the numbers should always have sorted first.

You're exactly right. Since I first posted this I have realised that this is the case. As such, rather than trying to ascertain which is the latest 'revision number', I've had to look at the date instead. This leaves a little more room for user error to creep in, but I've put in place other checks to guard against such errors causing too much trouble.

Look up "Option Compare" in help and it may shed some light on what could have changed.

I thought that was the case, too, but I couldn't get it to have any impact on how these items were being sorted.

I believe that certain Windows settings can impact sort order.

I have to assume you're right, as I couldn't find anything else within Access to affect a change, but I also couldn't find the appropriate Windows options either.

As I said earlier, I've taken a very different approach now, but I'd sure like to know what the settings were on my old PC that meant that its sort order was so far different to my new one. (I can only assume, from the dearth of responses to this thread, that the vast majority of other PC's are configured the same way as my new one.)

Thanks for your input.

Jon.

 

[Banana]

One thing about Access is that they don't make explicit the collation and character sets being used. Pat already gave you a clue about Windows' settings.

That said, because we don't have a explicit collation, I personally would just create my own list of values assigned to a certain character and treat everything as a string then use a function to get my custom assigned value for a given character. This way, I am assured that sort order is what I expect it to be, regardless of where Access is installed.

It shouldn't be to imply that collation/character sets can't be specified; you could also google and see if it's possible to specify such in Access or via SQL with Jet.

HTH.

 

[jonno_g]

OK, Banana, I think your post just buzzed over my head somewhere!!

I think you were saying that in order to guarantee that my code sorts in the order I require that I should create a table containing the character to sort, in the order in which I want them to sort, and then use a query to retrieve their values as needed during the sorting process.

Is that what you were saying?

 

[Atomic Shrimp]

I think that's more or less it - you probably need to add a field to explicitly control the sorting order. If the revision records are added to the table in the sequence you actually want them, then the field could even be an autonumber - or at least it could if you were starting from scratch.

 

[gemma-the-husky]

are you sure the missort is a zero

it couldn't possibly be a letter O could it, since C > O IS false

 

[Banana]

Q

OK, Banana, I think your post just buzzed over my head somewhere!!

I think you were saying that in order to guarantee that my code sorts in the order I require that I should create a table containing the character to sort, in the order in which I want them to sort, and then use a query to retrieve their values as needed during the sorting process.

Is that what you were saying?

Yep. What I was saying is that in any other databases such as MS SQL Server, Oracle, DB/2, MySQL, when you create a new database, you have option to choose which character sets (e.g. Latin alphabet, Cyrillic alphabet, Unicode set, Kanji set, and so forth) and collation (determine how a character set would be sorted; whether it should be case insensitive (c=C) or case sensitive (c<C), and whether Müeller should be different from Mueller).

In Access, I don't recall specifying such, and I don't think it's a per-database setting. At least, I couldn't find any option in Access's preferences allowing one to specify character sets and collations, which means that this is dependent on computer's regional and language setting and possibly other factors. For those reasons, I would just go with a table to be assured that they are getting sorted as I'm expecting it is to be sorted.

HTH.

 

[jonno_g]

are you sure the missort is a zero

it couldn't possibly be a letter O could it, since C > O IS false

Very sure.

Also, it persists when comparing A and 2, for example.

Which I would expect, now that I know what the problem was.

 

[jonno_g]

Re: Q

Yep....

In Access, I don't recall specifying such, and I don't think it's a per-database setting. At least, I couldn't find any option in Access's preferences allowing one to specify character sets and collations, which means that this is dependent on computer's regional and language setting and possibly other factors. For those reasons, I would just go with a table to be assured that they are getting sorted as I'm expecting it is to be sorted.

OK, now everything you said makes perfect sense. I, too, do not recall changing any such settings on my previous PC, so I'm assuming that it's something that our local IT dept. did at some stage, but then did differently when they set up my new PC. Unfortunately I'm not privvy to their logic on such decisions.

Anyway, back to the issue of using a table to generate the sort order - such a table for this application would need to have 66924 records (allowing for any possible combination of up to 2 numeric and 2 alpha characters per revision) hence my desire to be able to do the comparison programmatically.

 

[Banana]

Um, 66,924 unique characters?

If you're just using 0-9 and A-Z, that's just 36 records. You then write out a function to get the assigned value for each character in a given string, add it up in base 36 then compare the value against another string's value.

Or if you'd prefer to just co

 

[jonno_g]

Um, 66,924 unique characters?

If you're just using 0-9 and A-Z, that's just 36 records. You then write out a function to get the assigned value for each character in a given string, add it up in base 36 then compare the value against another string's value.

I already tried a similar approach using the ASCII values for the characters and ran into major problems.

When trying to compare the values for, say, "9M" versus "10A". This gives (57+77) versus (49+48+65), which is fine because 134 is less than 162.

But, when trying to compare "9M" to just "10", (10 is still the later revision), you'll find that (57+77) is greater than (49+48) which brings the whole thing down.

However, when using base 36 comparison (given 0=0 and Z=35), you get "9M" vs. "10A" as (9+22) vs. (1+0+10) and 31 is greater than 11 so that won't work.

Reverse the values, I hear you say. OK, so now A=0 and 9=35 - somewhat confusing but I could live with that.

Lets see: "9M" vs. "10A" gives (35+12) vs. (27+26+0), 47 is less than 53.

Similarly, "9M" vs. "10" gives (35+12) vs. (27+26), 47 is still less than 53 - we could be on a winner here!

Except that it falls over on a most fundamental level in that while "A" (0) is now less than "1" (27), which is exactly as it should be, a comparison of revisions "0Z" (26+25) against "1" (27) fails.

Even more confusing is a comparison of "0B" and "1" - (26+1) vs. (27).....

 

[Banana]

Actually, it shouldn't be a simple addition. You have to raise it by a certain power.

So suppose we had 9M. It should be calculated as thus:

M= 22 * 36^0 = 22
9 = 9 * 36^1 = 324

22 + 324 = 346

Then we consider 10:

0 = 0 * 36^0 = 0
1 = 1 * 36^1= 36

0 + 36 = 36

so 9M > 10

But 10A would be:

A = 10 * 36^0 = 10
0 = 0 * 36^1 = 0
1 = 1 * 36^2 = 1296

10 + 0 + 1296 = 1306

Thus, 10A > 9M > 10.


Here's a site to help you test your conversion.

HTH.


Edit: Also, I didn't mention, but if you don't want 0-9 to be 0-9 in the base 36 but rather 26-35, you can do that; just be sure to assign them that value in a table and refer this for your calculation.

 

[jonno_g]

Actually, it shouldn't be a simple addition. You have to raise it by a certain power.

So suppose we had 9M. It should be calculated as thus:

M= 22 * 36^0 = 22
9 = 9 * 36^1 = 324

22 + 324 = 346

Then we consider 10:

0 = 0 * 36^0 = 0
1 = 1 * 36^1= 36

0 + 36 = 36

so 9M > 10

That's exactly where it falls over again - 9M is not greater than 10 when referring to our revisioning system.

...I didn't mention, but if you don't want 0-9 to be 0-9 in the base 36 but rather 26-35, you can do that; just be sure to assign them that value in a table and refer this for your calculation.

Yeah, I already looked at that approach in my last post. My last four examples used that approach, but they failed anyway.

They may work using the values raised to a power, but you've already proven that overall that approach won't work as 9M evaluates as greater than 10 - which it is not.

 

[Banana]

Hm, I'm confused now.

In your initial post you said:

In essence, the typical progression of revisions goes like this: A, B, C, 0, 0A, 0B, 1,....etc.

And now we're saying that 10 should be greater than 9M? Is 10A supposed to be greater than 10 and 9M?

Can you provide some more examples of what you expect what values to be greater against what values?

 

[Banana]

I re-read your previous examples and I think this is what you want to see:

9M  < 10A

9M < 10

A  < 1

0Z < 1

If this is correct, then A-Z should be a fraction dividing against 27 as the denominator, and added to the result.

Therefore:

9M  < 10A

 9 + (13/27) = 9.481481481481482

10 + (1/27) = 10.037037037

[COLOR="Red"][b]9.481481481481482 < 10.037037037[/b][/COLOR]

9M < 10

 9 + (13/27) = 9.481481481481482

10 + (0/27) = 10

[COLOR="red"][B]9.481481481481482 < 10[/B][/COLOR]

A  < 1

0+ (1/27) = 0.037037037

1 + (0/27) = 1

[COLOR="red"][B]0.037037037 < 1[/B][/COLOR]

0Z < 1

0 + (26/27) = 0.962962963

1 + (0/27) = 1

[COLOR="red"][B]0.962962963 < 1[/B][/COLOR]

Is that what you are after?

 

[jonno_g]

And now we're saying that 10 should be greater than 9M? Is 10A supposed to be greater than 10 and 9M?

You are reading '10' as TEN, aren't you? Not "One, 'O'"? (I know that you are, your examples show that.)

What about my original post implied that "9M" would be greater than "10"?

It may be the way I've written it, but I though it was clear.

In essence, the typical progression of revisions goes like this: A, B, C, 0, 0A, 0B, 1,....etc.

In other words: You start with an Alpha, which is an "in-between" revision (i.e. you won't necessarily make it all of the way through 'Z' before moving on to the next full numeric revision) then, once that revision is complete (approved) it moves on to a 'whole number' revision (zero being the first) and sits there until further changes are required in future.

Once further changes become necessary you start making those changes to revision '0A' (zero-A) and progress from there until the revision is complete - say '0F' (zero-F) for arguments sake. Once approved, it becomes revision 1.

Thus, revision "9M" must come before "10" (ten).

Oh, and yes 10A>10>9M.

 

[jonno_g]

If this is correct, then A-Z should be a fraction dividing against 27 as the denominator, and added to the result.

BINGO!! I think you've just nailed it!

I'll try it out as soon as I get the chance.

Thanks a bunch for that.

 

[Banana]

I did read it as TEN, but was confused because in n-base, 9M should be greater than 10 and didn't pick up on your paragraph that A-Z is alpha or in between rather than an actual representation of value.

I have fingers crossed for you! Hope it works out. It's much easier than mucking with a custom collation anyway!

 

[Banana]

Realized one flaw-

If you want A to be less than 0A, my method above would say they're equal.

A workaround would be to always add one for numeric portion, if present, so:

0A = (0 + 1) + 0.037037037 = 1.037037037

A = 0.037037037 

0.037037037 < 1.037037037

1A = (1 + 1) + 0.037037037 = 2.037037037

10A = (1 + 10) + 0.037037037 = 11.037037037

 

[jonno_g]

Ah, good catch!