trim non numeric values from within a query.

[Guest]

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.

 

[Guest]

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])

 

[Guest]

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?

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.

 

[Guest]

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

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?

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.

 

[Guest]

Thanks,

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

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

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?

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.