Numeric String comparison (1 Viewer)

kevlray

Registered User.
Local time
Today, 03:07
Joined
Apr 5, 2010
Messages
1,046
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

Lifelong Learner
Local time
Today, 03:07
Joined
Mar 14, 2017
Messages
8,738
Why can't you just convert it to an actual string and do the comparison?
 

Micron

AWF VIP
Local time
Today, 06:07
Joined
Oct 20, 2018
Messages
3,476
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

Immoderate Moderator
Staff member
Local time
Today, 05:07
Joined
Feb 28, 2001
Messages
27,001
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

I’m here to help
Staff member
Local time
Today, 03:07
Joined
Oct 29, 2018
Messages
21,358
Hi. Be careful when comparing strings. As strings, "22" is not greater than "3."
 

Micron

AWF VIP
Local time
Today, 06:07
Joined
Oct 20, 2018
Messages
3,476
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

Registered User.
Local time
Today, 03:07
Joined
Apr 5, 2010
Messages
1,046
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

Lifelong Learner
Local time
Today, 03:07
Joined
Mar 14, 2017
Messages
8,738
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.
 

Micron

AWF VIP
Local time
Today, 06:07
Joined
Oct 20, 2018
Messages
3,476
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?
Code:
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

Lifelong Learner
Local time
Today, 03:07
Joined
Mar 14, 2017
Messages
8,738
@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

Registered User.
Local time
Today, 03:07
Joined
Apr 5, 2010
Messages
1,046
And I just showed part of the code that I am dealing with.
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:07
Joined
Jan 23, 2006
Messages
15,364
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

Registered User.
Local time
Today, 03:07
Joined
Apr 5, 2010
Messages
1,046
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.
 

Users who are viewing this thread

Top Bottom