Numeric String comparison

[kevlray]

So I have some code that is most of the time a number, but sometimes be a string (i.,e "1000000" or "<1000000"). But I have to compare to a number (i.e, 600000). The values come from an MS-SQL back end. I tried using Format() (i.e, Format(rst!.DMSCC.Value, "#########") ) and compare that to "600000". So the compare (If statement) comes back false when rst!.DMSCC.Value = "1000000" for the statement IF Format(rst!.DMSCC.Value, "#########") > "600000".

Since I cannot count on the rst!.DMSCC.Value always to evaluate to a number (i.e, "<1000000"), I am not certain what using VAL would do.

 

[Isaac]

Why can't you just convert it to an actual string and do the comparison?

 

[Deleted member 147267]

The Format function converts data to a string. I'd try converting the string to a number, e.g. Cdbl. If you can have Nulls, you'll need to wrap the conversion around Nz:
Cdbl(Nz(rst!DMSCC,0))

 

[The_Doc_Man]

Test the first character of the string to see if it is a digit. If so, VAL will convert it to a number up to the first non-digit in the sequence. What you do if it is NOT a digit string is perhaps a bit more of a problem.

 

[theDBguy]

Hi. Be careful when comparing strings. As strings, "22" is not greater than "3."

 

[Deleted member 147267]

ooops. I missed that one of the strings had < as a literal character. To add to Doc's last comment, if the first character is not numeric, just use Mid function to extract the rest, starting at position 2. If you can have non-numeric characters after the number, that won't work for all. If your non-numeric character count can be greater than one, then you need something more complex. I see it as being doable, but there's too many ifs at present.

@kevlray, that code is free typed and not a copy/paste?
rst!.DMSCC should not work.

 

[kevlray]

No kidding as I lose hair trying to come up with an good solution. There is a lot of old data the using the "<1000000", but the new data they are not doing it and since I only look eight months back. I may be able to ignore the "<1000000".

 

[Isaac]

I didn't realize you meant you might have random characters in the string of otherwise-numbers, also.
I guess the safest thing is just to bite the bullet and write a simple function to replace all non-numeric characters, then convert to number, then compare. That covers all of our if's and wonderings and etc. and would take about 5 min to write.

 

[Deleted member 147267]

Using a variable, I don't know if there's a need to convert anything. For any code, a thorough understanding of the issue is required if you're going to succeed for all conditions, so while simple, this may not handle all conditions. The ifs were not answered, although they could have been?

Dim varValue As Variant

varValue = rst.Fields("DMSCC")

If Not IsNumeric(Left(varValue,1)) Then varValue = Mid(varValue,2)

I presume there is a recordset loop where the test would be made, then the variable compared to the other number.

 

[Isaac]

@Micron I agree if he is sure that is the only possible pattern. But given the comment about 'lose hair trying to come up with a good solution', it seemed more iffy than that - pun intended. I figured doing a numerical comparison after replacing non-numeric char's would be a pretty simple way to solve it and walk away. But maybe he is looking for something else i don't understand..

 

[kevlray]

And I just showed part of the code that I am dealing with.

 

[jdraw]

Can you use some more generic approaches to help you get focus on the issue?
For example: (depending on a) volume, and b) criticality)
-get a distinct count f the "contents" of the field
-get a count and record identifier for those records where the field is Not numeric
-get a count and record identifier for those records where the field is numeric
-other patterns/conditions

What is the ultimate goal -to be able to work with what exists, or to correct/adjust current and make the datatype and values consistent going forward?

 

[kevlray]

I went with Micon's solution (with a slight modification). Just to give everyone a little background of the database. First off, I did not create the database itself. I was asked to produce some code to automate (flag) bad tests for dairies. So the dairy milk gets tested for like five different tests, I do not know what each of the test names mean (SPCS, LPC, COLI , DMSCC, CRYO). For each of these tests, there is a particular data value that is bad (the DMSCC criteria is a bit more complicated, the reason that I reached out to this group). Of course there is more to it than that. If they have had a previous bad test, I may have to flag it another way. So it has been an adventure.