“Cleaning” information before you import it into your database is pretty standard – after all, one of the reasons you’ve started to use a database is so that you can predictably query that database for set criteria right…right? So how you handle external situations where, for example, you have to import info from a file where one person enters “$” but another assumes you know it’s a monetary value – or worse, where someone enters “am” or “pm” but another person uses the 24-hour clock? As usual, plenty of ways to approach the problem but one way is to check whether a string is just a set of numbers, or whether it contains any “non-numbers”. With a little VBA, this kind of check is simple – and this article will show you just how simple…
Why do we need to do this?
If you’re importing any kind of info that you don’t have control over how it is entered or extracted then you’ll have come across this problem many (many!) times before – people enter details in the way that’s most comfortable to them, not in a uniform way. This means it’s only a matter of time before you have to make it uniform before you can import it.
A typical example of this is where you need to import data into a numerical field but when you look at the data you see that some have separated thousands with commas, others have added a currency sign – and who knows what else.
A quick word of caution!
Importing data that hasn’t been cleaned into your database can be pretty disastrous – at best it can skew your reporting, at worse you could end up with all sorts of things broken – don’t let “dirty” data imports be the reason you’re trying to fix Access problem all weekend!
How to “get rid of” alphabetical characters
You could do a search and replace manually of course, but doing that can take time and you may well miss something (if you’re only searching, for example, for dollar signs, you may miss the euro sign or commas, spaces etc). So the easiest and more reliable way of doing it is to let Access do the hard work using the code below.
Onto the code…
Public Function removeNonNumeric(inString As String) As Long Dim outputString, chrString As String Dim strPos As Integer For strPos = 1 To Len(inString) chrString = Mid(inString, strPos, 1) If IsNumeric(chrString) Then outputString = outputString & chrString Next removeNonNumeric = CLng(outputString) End Function
What the code does
The function here is very straight-forward. All we do here is to loop through the string that is passed in, one character at a time. If the character is a number then we add that to our temporary string, and when we’re done we convert that into a “long”.
Adapting the code to suit you
As you can see – the function returns a data type of “long” – if you’re working with integers, or floating points etc then you will want to change the function accordingly so that it returns the right type of value for your database.
Author Introduction:
Mitchell Pond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair SQL Server database and excel recovery software products. For more information visit www.datanumen.com
1