Determining if value is a number or text

[Mike Smith]

I have a field where the users input a product code. I need to look at the third digit of the code they enter and determine if the character is a number or alpha character. I can get the third character with the Mid function but I don't know how to evaluate the character...is it alpha or number? Depending on which it is I then code for two different tasks to occur. Any ideas appreciated!

 

[charityg]

if instr(1,"0123456789",mid(field,3))>0 then
number
else
alpha
endif

 

[DALeffler]

If IsNumeric(Mid(Field,3,1)) Then
Do the Numeric stuff
Else
Do the Alpha stuff
End If

You might want to test the above to see what happens when the Mid function returns a zero length string or a space...

Doug.

 

[Atomic Shrimp]

I had a bit of fun and games with the IsNumeric function a while back; it was recognising the letters A to F as numeric too, I assume because they can be used as part of a Hex expression.

Has anyone else seen it do that?

 

[joeyreyma]

Mike

nah! i frequently used it in my programs. i just tried it in the debug window but can't get it. can you pls. post your example. i'm very curious about it.

 

[Atomic Shrimp]

Joey, I've never been able to reproduce the problem, but Here is the topic where it first arose.

 

[Ken Grubb]

I've been playing around with this one, and I'm able to reproduce the problem.

In the original post about this problem, the poster was using 6 character strings so that is what I've been using.

I can reproduce the problem ONLY when I use a single D or E somewhere in positions 2 thru 5 and numbers 0 thru 9 in the other 5 positions of a 6 character string.

I've keyed about 40 random combinations into an Access table and I have a Query displaying the value and True/False showing what IsNumeric and Mike Gurman's ReallyIsNumeric Functions return. In doing so, I found a few "lines in the sand".

12D307 and values below it that meet the criteria I've explained above return True for IsNumeric but False for ReallyIsNumeric.

12D308 thru 33E999 (of values that meet the criteria I've explained above) return False for IsNumeric but False for ReallyIsNumeric.

34D000 and values above it that meet the criteria I've explained above return True for IsNumeric but False for ReallyIsNumeric.

[Perhaps Mike Gurman should copyright ReallyIsNumeric and send an Email to Redmond along with a number.]

Ken Grubb
Burlington, NC, USA

 

[DALeffler]

Let's see 'da code, mannnng!

Interesting as anything I've read here before!

This I do done gotta see!

How da' heck we s'posed to tell the 'puter what to do if'n the 'puter doan e'en be know'in the talk we be talk'in?

Ja-E-sus-H.-Ke-riste!

Doug (I really would like to see that code...)

[This message has been edited by DALeffler (edited 07-26-2001).]

 

[joeyreyma]

okay! i've seen the problem. isn't this the effect of accepting variant as parameter???

 

[KDg]

I'm also v. intrigued by this, why does

CDec("34D000") return 34 instead of 3461120. Also IsNumeric("34D0") is true. I'm gonna keep playing and will post if i find anything, thanks Ken - the day just got more interesting. Most interesting puzzle i've ever seen here, i feel the answer is somewhere in the binary but haven't got enough to work it out yet...

Drew

Sorry, ignore the CDec above, barking up the wrong tree i think - i should really have declared it was hex if i wanted i to work

[This message has been edited by KDg (edited 07-27-2001).]

 

[axa]

eureka!! i have it!

the answer to this one is annoying. after playing around with this i have discovered that the vb numeric functions are implicitly recognising the letters E (and D) to indicate that the number is expresed using scientific notation (ie 2E3 is the equivalent of 2 e+3 (or 2 * 10^3 for the maths purists), ie 2000). E basicly indicates the the number to its left should be raised to the exponential given to the right (D is the same, but i guess it could stand for 'decimal places')

i tried out the following combinations (using both D and E, i have only shown E here):

2E0 = 2
2E3 = 2000
2E6 = 2000000
2E-3 = 0.002
2E-6 = 0.000002

i guess you could say this is an 'undocumented' feature.

hope that helps.



axa
_________________________
ps:- back to the original posting...

Mike, the code given by DALeffler should work fine, if the single letter returned by mid() is D or E, and the letter is passed to isnumeric(), the result will be false (non-numeric). isnumeric() only gives you the above problems when D or E appears with other characters in the input argument for isnumeric()

also, if isnumeric() is given a space or a blank string it will return false as you would hope.


by the way, sorry to spoil everybodys fun by figuring out the answer!



[This message has been edited by axa (edited 07-27-2001).]

 

[KDg]

bah! Could the answer be any less interesting? I really wanted it to involve overflows and bits, nevermind. Back to my boring day job, feeling a little more stupid than before. I really want to delete that post above but i guess i'll leave it

thanks axa, it's good to know we can all use scientific notation in numeric terms, cheers!

Drew

[This message has been edited by KDg (edited 07-27-2001).]

 

[Ken Grubb]

One more whack at the expiring equine.

These solutions originate with Rick Rothstein (comp.lang.basic.visual.misc) and Chuck Grimsby (comp.databases.ms-access)

I tweaked the functions so they handle ZLS and Nulls.

Function IsDigitsOnlyMod1(NumberIn As Variant) As Boolean
If IsNull(NumberIn) Or NumberIn = "" Then
IsDigitsOnlyMod1 = False
Else
IsDigitsOnlyMod1 = Not (NumberIn Like "*[!0-9]*")
End If
End Function

Function IsDigitsOnlyMod2(NumberIn As Variant) As Boolean
If IsNull(NumberIn) Or NumberIn = "" Then
IsDigitsOnlyMod2 = False
Else
IsDigitsOnlyMod2 = (NumberIn Like String$(Len(NumberIn), "#"))
End If
End Function

And, you can use this one in a Query without the need to write a Function.

IIf((

.[field] Not Like "*[!0-9]*" And

.[field] <> ""),"True","False")

Ken Grubb
Burlington, NC, USA

 

[joeyreyma]

D is double-precision as in type DOUBLE!