Please, I need a quick help with replace

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

On importing text from a text file I get in some fields,
squares whereever there was a tab in the text file.I used
the replace function as follows:
Replace([FieldName],chr(9),"").

this worked very well in access 2k but not in access 97. I
am using Access 97 for one of my clients. what is the
similar solution for that replace function in 97. I know I
can go to the table and use the "ctl H" but I need to do
this programatically since I am not around when the user
imports the text.
thank you
Al
 
Al said:
On importing text from a text file I get in some fields,
squares whereever there was a tab in the text file.I used
the replace function as follows:
Replace([FieldName],chr(9),"").

this worked very well in access 2k but not in access 97. I
am using Access 97 for one of my clients. what is the
similar solution for that replace function in 97. I know I
can go to the table and use the "ctl H" but I need to do
this programatically since I am not around when the user
imports the text.
thank you
Al
Click to expand...

The Replace function was introduced with Access 2000 (and couldn't be
used directly in queries until one of the service packs, IIRC). For
Access 97, you have to create your own version. Here's one:

'----- start of code -----
Public Function Replace( _
Expression As String, _
Find As String, _
Replacement As String, _
Optional ByVal Start As Long = 1, _
Optional ByVal Count As Long = -1, _
Optional Compare As Long = vbBinaryCompare) _
As String

' Functional equivalent of the A2K Replace() function, to be used
' when that function is not available.

Dim strResult As String
Dim lngFL As Long
Dim lngPos As Long

If Count < -1 Then Err.Raise 5

lngFL = Len(Find)
If lngFL > 0 Then
Do Until Count = 0
lngPos = InStr(Start, Expression, Find, Compare)
If lngPos = 0 Then Exit Do
strResult = strResult & _
Mid$(Expression, Start, lngPos - Start) & _
Replacement
Start = lngPos + lngFL
Count = Count - 1
Loop
End If

Replace = strResult & Mid$(Expression, Start)

End Function
'----- end of code -----
 
On importing text from a text file I get in some fields,
squares whereever there was a tab in the text file.I used
the replace function as follows:
Replace([FieldName],chr(9),"").

this worked very well in access 2k but not in access 97. I
am using Access 97 for one of my clients. what is the
similar solution for that replace function in 97. I know I
can go to the table and use the "ctl H" but I need to do
this programatically since I am not around when the user
imports the text.
thank you
Al
Click to expand...

The Replace() function was not available in Access 97.

You will need to write a User Defined function to remove the chr(9)
from the text.

Copy and Paste the below function into a module.

Function RemoveChar(FieldIn As String) As String

Dim intX As Integer
Dim intY As Integer
intY = 1
Dim NewString As String

intX = InStr(1, FieldIn, chr(9))
Do While intX <> 0
NewString = NewString & Mid(FieldIn, intY, intX - intY)
intY = intX + 1
intX = InStr(intY, FieldIn, chr(9))
Loop

NewString = NewString & Mid(FieldIn, intY, Len(FieldIn) - intY + 1)
RemoveChar = NewString

End Function
======

You can call it from a Select query:
Exp:RemoveChar([FieldName])

To permanently remove the chr(9) from the field, use the expression in
an update query.

Update YourTable Set YourTable.FieldName = RemoveChar([FieldName])
Where [FieldName] is not null;
 
Fred,
thank you very much It works.
Al
-----Original Message-----
Al said:
On importing text from a text file I get in some fields,
squares whereever there was a tab in the text file.I used
the replace function as follows:
Replace([FieldName],chr(9),"").

this worked very well in access 2k but not in access 97. I
am using Access 97 for one of my clients. what is the
similar solution for that replace function in 97. I know I
can go to the table and use the "ctl H" but I need to do
this programatically since I am not around when the user
imports the text.
thank you
Al
Click to expand...

The Replace function was introduced with Access 2000 (and couldn't be
used directly in queries until one of the service packs, IIRC). For
Access 97, you have to create your own version. Here's one:

'----- start of code -----
Public Function Replace( _
Expression As String, _
Find As String, _
Replacement As String, _
Optional ByVal Start As Long = 1, _
Optional ByVal Count As Long = -1, _
Optional Compare As Long = vbBinaryCompare) _
As String

' Functional equivalent of the A2K Replace() function, to be used
' when that function is not available.

Dim strResult As String
Dim lngFL As Long
Dim lngPos As Long

If Count < -1 Then Err.Raise 5

lngFL = Len(Find)
If lngFL > 0 Then
Do Until Count = 0
lngPos = InStr(Start, Expression, Find, Compare)
If lngPos = 0 Then Exit Do
strResult = strResult & _
Mid$(Expression, Start, lngPos - Start) & _
Replacement
Start = lngPos + lngFL
Count = Count - 1
Loop
End If

Replace = strResult & Mid$(Expression, Start)

End Function
'----- end of code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
thank you Dirk,
It works.
Al
-----Original Message-----
Al said:
On importing text from a text file I get in some fields,
squares whereever there was a tab in the text file.I used
the replace function as follows:
Replace([FieldName],chr(9),"").

this worked very well in access 2k but not in access 97. I
am using Access 97 for one of my clients. what is the
similar solution for that replace function in 97. I know I
can go to the table and use the "ctl H" but I need to do
this programatically since I am not around when the user
imports the text.
thank you
Al
Click to expand...

The Replace function was introduced with Access 2000 (and couldn't be
used directly in queries until one of the service packs, IIRC). For
Access 97, you have to create your own version. Here's one:

'----- start of code -----
Public Function Replace( _
Expression As String, _
Find As String, _
Replacement As String, _
Optional ByVal Start As Long = 1, _
Optional ByVal Count As Long = -1, _
Optional Compare As Long = vbBinaryCompare) _
As String

' Functional equivalent of the A2K Replace() function, to be used
' when that function is not available.

Dim strResult As String
Dim lngFL As Long
Dim lngPos As Long

If Count < -1 Then Err.Raise 5

lngFL = Len(Find)
If lngFL > 0 Then
Do Until Count = 0
lngPos = InStr(Start, Expression, Find, Compare)
If lngPos = 0 Then Exit Do
strResult = strResult & _
Mid$(Expression, Start, lngPos - Start) & _
Replacement
Start = lngPos + lngFL
Count = Count - 1
Loop
End If

Replace = strResult & Mid$(Expression, Start)

End Function
'----- end of code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Thank you Fred
I tried this as a function in a code and in an Update query
and select query, they all work. I also tried Dirk code and
it works well with any character. thank you both, I am very
gratfull
Al
-----Original Message-----
On importing text from a text file I get in some fields,
squares whereever there was a tab in the text file.I used
the replace function as follows:
Replace([FieldName],chr(9),"").

this worked very well in access 2k but not in access 97. I
am using Access 97 for one of my clients. what is the
similar solution for that replace function in 97. I know I
can go to the table and use the "ctl H" but I need to do
this programatically since I am not around when the user
imports the text.
thank you
Al
Click to expand...

The Replace() function was not available in Access 97.

You will need to write a User Defined function to remove the chr(9)
from the text.

Copy and Paste the below function into a module.

Function RemoveChar(FieldIn As String) As String

Dim intX As Integer
Dim intY As Integer
intY = 1
Dim NewString As String

intX = InStr(1, FieldIn, chr(9))
Do While intX <> 0
NewString = NewString & Mid(FieldIn, intY, intX - intY)
intY = intX + 1
intX = InStr(intY, FieldIn, chr(9))
Loop

NewString = NewString & Mid(FieldIn, intY, Len(FieldIn) - intY + 1)
RemoveChar = NewString

End Function
======

You can call it from a Select query:
Exp:RemoveChar([FieldName])

To permanently remove the chr(9) from the field, use the expression in
an update query.

Update YourTable Set YourTable.FieldName = RemoveChar([FieldName])
Where [FieldName] is not null;
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Back
Top