trim non numeric values from within a query.

F

fishy

I have inherited a database that has rather a personal method of taking
telephone numbers i.e. 12345c ext 1234, Mob12345, EX12345, 12345ex dir etc...
I want to run a query that will trim out all the non numeric chaacters but
don't know where to start.
 
O

Ofer Cohen

Start with backing up your data

'Create a function in a module that return only the numeric from a string
Function GetNumeric(Param As Variant)
Dim I
If Len(Trim(Param) & "") = 0 Then
GetNumeric = Null
Exit Function
End If
For I = 1 To Len(Param)
If IsNumeric(Mid(Param, I, 1)) Then
GetNumeric = GetNumeric & Mid(Param, I, 1)
End If
Next I
End Function

Then use the function in a query t update the data

UPDATE TableName SET TableName.[FieldName] = GetNumeric([FieldName])
 
F

fishy

this gets rid of my existing data, but whilst I am educating the rest of the
business, is there anyway of cleansing this in a query I can run for
update/append?

Ofer Cohen said:
Start with backing up your data

'Create a function in a module that return only the numeric from a string
Function GetNumeric(Param As Variant)
Dim I
If Len(Trim(Param) & "") = 0 Then
GetNumeric = Null
Exit Function
End If
For I = 1 To Len(Param)
If IsNumeric(Mid(Param, I, 1)) Then
GetNumeric = GetNumeric & Mid(Param, I, 1)
End If
Next I
End Function

Then use the function in a query t update the data

UPDATE TableName SET TableName.[FieldName] = GetNumeric([FieldName])

--
Good Luck
BS"D


fishy said:
I have inherited a database that has rather a personal method of taking
telephone numbers i.e. 12345c ext 1234, Mob12345, EX12345, 12345ex dir etc...
I want to run a query that will trim out all the non numeric chaacters but
don't know where to start.
Click to expand...
Click to expand...
 
O

Ofer Cohen

Not sure what you are asking for, I gave you an example on how to update the
data in the field to numeric only.
If you don't want to change the data, but you want to display the field in a
select query, try

Select FieldName , GetNumeric([FieldName]) As NewFieldName Fromc TableName
--
Good Luck
BS"D


fishy said:
this gets rid of my existing data, but whilst I am educating the rest of the
business, is there anyway of cleansing this in a query I can run for
update/append?

Ofer Cohen said:
Start with backing up your data

'Create a function in a module that return only the numeric from a string
Function GetNumeric(Param As Variant)
Dim I
If Len(Trim(Param) & "") = 0 Then
GetNumeric = Null
Exit Function
End If
For I = 1 To Len(Param)
If IsNumeric(Mid(Param, I, 1)) Then
GetNumeric = GetNumeric & Mid(Param, I, 1)
End If
Next I
End Function

Then use the function in a query t update the data

UPDATE TableName SET TableName.[FieldName] = GetNumeric([FieldName])

--
Good Luck
BS"D


fishy said:
I have inherited a database that has rather a personal method of taking
telephone numbers i.e. 12345c ext 1234, Mob12345, EX12345, 12345ex dir etc...
I want to run a query that will trim out all the non numeric chaacters but
don't know where to start.
Click to expand...
Click to expand...
Click to expand...
 
F

fishy

Thanks,

Managed to get my head around it and works a treat.

Ofer Cohen said:
Not sure what you are asking for, I gave you an example on how to update the
data in the field to numeric only.
If you don't want to change the data, but you want to display the field in a
select query, try

Select FieldName , GetNumeric([FieldName]) As NewFieldName Fromc TableName
--
Good Luck
BS"D


fishy said:
this gets rid of my existing data, but whilst I am educating the rest of the
business, is there anyway of cleansing this in a query I can run for
update/append?

Ofer Cohen said:
Start with backing up your data

'Create a function in a module that return only the numeric from a string
Function GetNumeric(Param As Variant)
Dim I
If Len(Trim(Param) & "") = 0 Then
GetNumeric = Null
Exit Function
End If
For I = 1 To Len(Param)
If IsNumeric(Mid(Param, I, 1)) Then
GetNumeric = GetNumeric & Mid(Param, I, 1)
End If
Next I
End Function

Then use the function in a query t update the data

UPDATE TableName SET TableName.[FieldName] = GetNumeric([FieldName])

--
Good Luck
BS"D


:

I have inherited a database that has rather a personal method of taking
telephone numbers i.e. 12345c ext 1234, Mob12345, EX12345, 12345ex dir etc...
I want to run a query that will trim out all the non numeric chaacters but
don't know where to start.
Click to expand...
Click to expand...
Click to expand...
 
Top