Hitesh Joshi wrote:
anyone would like to give any hint/advice/input??
Hitesh Joshi wrote: No, sometimes. Most of the time data has 6 digit numeric value.
I tried following update query but it's not working:
UPDATE tblFIX SET tblFIX.Trailer = Left( tblFIX.Trailer,13)
WHERE (((tblFIX.Trailer) Like "EXTERN*") AND
((tblFIX.FIXDateTime)=Date()) AND ((tblFIX.Broker)="XXXX"));
I recommend creating a Public User-Defined Function (UDF) to filter out
the non-digits.
Public Function DigitsOnly(varIn As Variant) As Variant
Dim varTemp As Variant
Dim strChar As String
Dim I As Integer
DigitsOnly = Null
If Len(Nz(varIn, "")) = 0 Then Exit Function
varTemp = Null
For I = 1 To Len(varIn)
strChar = Mid(varIn, I, 1)
If IsNumeric(strChar) Then
varTemp = varTemp & strChar
End If
Next I
DigitsOnly = varTemp
End FunctionstrTemp
End Function
Here's a recursive version:
Public Function DigitsOnly(ByVal varIn As Variant) As Variant
DigitsOnly = Null
If Len(Nz(varIn, "")) = 0 Then Exit Function
If Len(varIn) = 1 Then
If IsNumeric(varIn) Then
DigitsOnly = varIn
Else
DigitsOnly = Null
End If
Else
DigitsOnly = DigitsOnly(Left(varIn, Len(varIn) - 1)) &
DigitsOnly(Right(varIn, 1))
End If
End Function
qryDigitalFilter:
UPDATE tblFIX SET tblFIX.Trailer = DigitsOnly(tblFix.Trailer) ...
Note that the IsNumeric function will only be True for digits when only
one character is used. The functions will replace a ZLS with Null and
leave Nulls alone. Add an extra few lines if you want to preserve
ZLS's. I didn't test to see if preserving ZLS's using the code below
breaks anything or not.
If Len(Nz(varIn, "")) = 0 Then
DigitsOnly = varIn
Exit Function
End If
Hope this helps,
James A. Fortune
CD********@FortuneJames.com 
