Question about Hard Return Removal 1

[Gunny]

I am working in Access 2000. I need to remove the hard returns that import with the data and replace it with a space. This occurs in an Address field that has the basic address then a hard return and the apartment number. I am looking for the VBA code that will accomplish this when a command button is pushed because of the volume of the import entries.

 

[bigfoot]

Is the data in a string variable?

A hard return is an ASCII character 13 and a ASCII character 10.
13 - Return
10 - Line feed

If so try:

If Right$(szMyString,1) = Chr$(10) or Right$(szMyString,1) = Chr$(13) Then
szMyString = Left$(szMyString,Len(szMyString)-1) & " "
End If

Hope this helps

-Gary


They never have to knock if your door is always open.

 

[Gunny]

Thanks Gary,

I'll give this a try and let you know how it works....

 

[Iray]

use VBA's Replace$ function ...

NewString$ = Replace$(DataInStr$,vbCrLf," &quot

NewString$ will contain DataInStr with all the CrLF pairs replaced with a single space.

David

 

[bigfoot]

Hey David, I forgot that command.
It's one of the lesser known commands, I'm going to keep this one in mind for future projects.

-G They never have to knock if your door is always open.