HappyJoni

I have test data where the field 'city' contains 'M3NASHA' (notice the '3' in the second position). I run it through the following code to check to see if the field is numeric for
QCing purposes and it passes when I thought it would cause the msgbox to display. Am I doing this wrong? Shouldn't IsNumeric catch this?

If IsNumeric(city) Then
MsgBox("Error: city should not contain numbers " & vbCrLf & Clean_Rec)
End If


Thanks!
Joan

TheRealTinTin

What data type is city? If it is Short, Integer, Long, Decimal, Single, or Double then the function will always return True - see here.

I'm not on a .NET machine just now but if the data type is wrong, I would've expected an error to be returned when trying to assign a non-numeric value to city (assuming it is defined as some sort of number).

Have you tried stepping through the code to check the value of city before it hits the If function?

HappyJoni

Thanks for responding

I have city declared as a string...which is what it's supposed to be and that's the reason I'm checking to make sure. If there are any numbers in that field I have to write out the record to an error log.

I did step through the code and 'city' contained exactly what I expected, 'M3NASHA', both before it entered the 'if' statement and after.

Is there another way I should be doing this? How can you check a string to make sure there are no numbers in it?


Thanks!
Joni

TheRealTinTin

I can't see why IsNumeric doesn't work then. I will try this later when I get home. As a work around, you could use the Like function with 0-9 in the pattern parameter.

Qua

IsNumeric returns true if the input can be interpreted as a numeric value. In your case, your are giving it a city, which is never a numeric value and thus it always returns false.

But you're not really interested in knowing whether the city name is numeric. What you are actually interested in is knowing whether the name contains any digits. You can check this with a simple regular expression:

hDC_0

..or you could use the Char.IsDigit method.

Just in case anyone is wondering what the difference between Char.IsNumeric and Char.IsDigit --see here.
Here is a sample of the many types of test that can be done with char.

If you want to get a count the number of digits in a string with char.IsDigit,
(or test whether a character in a specified position of a string is a digit),
then you'll find the code here.

In order to get Qua's code to work you should probably include:
Here is a tutorial on using Regex.

AtmaWeapon

I can't reproduce the behavior you are describing. IsNumeric() is a treacherous function because it uses Val()'s behavior and it can be a little... odd. For example, "200dollars" will be considered numeric because it starts with a number.

There's a few ways to decide if a value is a number.

First it helps to know what the incoming type is. If you know that, you've got a big head start. It's best if it's a string. Then you have the three standard tools:

• Character-by-character use of IsDigit()
• Regular expressions
• The various TryParse() methods.

I like the 3rd choice, because odds are you want the number anyway. The rest are just good for casual, "Is this a number?" checks and you'd still have to do the conversion later.

If you don't know what type it is, you're in a bit of a pickle. *Probably* the best bet is calling .ToString() on whatever you've got to make a string out of it, and try the previous methods on that. If I knew more, I could be more detailed.

HappyJoni

Thanks everyone for your responses.. I haven't tried any of your suggestions yet but will later on tonight.

AtmaWeapon - the file that I'm checking is a flat text file that I will eventually have to load into a Visual FoxPro table with 35 columns. What I'm trying to do here is just make sure the text is all in the right position before I load it into the table. I'm trying to do this by checking fields that should be all numeric to make sure it contains only digits. I'm doing the same with fields that should only contain letters (like I was trying to do with the field 'city'). That's basically all I'm trying to do. I don't need to perform any calculations or manipulate the data in any way so I'm declaring all the variables for each field as a string.

AtmaWeapon

Something like this should do the trick, based heavily on hDC_0's post:

A regex could do it in less lines, but that's not the only metric.

HappyJoni

So.. I wound up going with what AtmaWeapon suggested in the last reply. It works great! Thank you so much

I looked into using the Regex suggestion but not 100% on how it works so was a little nervous about using it. I'm definately going to look into it more, seems like it would be a great tool to use once I understand it better.

Thanks everyone!
Joni