Substring

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

Untested:

dim sAddress as string, n as integer
sAddress = trim$ (nz (me![txtAddress], ""))
n = instr (sAddress, vbcr)
if n > 0 then
sAddress = rtrim$ (left$ (sAddress, n - 1)) & " " & _
ltrim$ (mid$ (sAddress, n + len (vbcr)))
endif
msgbox sAddress

The line break is probably a return/linefeed combination: not just a return.
If so, replace vbcr with vbcrlf in two places above.

If the address could contain >several< lines (seperated by rerturn
[linefeed]):

n = instr (...)
while n > 0
sAddress = ...
n = instr (...)
wend

where ... means "as before".

HTH,
TC
 
I have an address box that may have 2 lines in it, their
is a return after the first line, I need to substring out
from the return and then concat them together ie

1111 Street
the park

becomes

1111 street the park

any idea how I would do it by looking for the enter

thanks

Nigel
 
Nigel said:
I have an address box that may have 2 lines in it, their
is a return after the first line, I need to substring out
from the return and then concat them together ie

1111 Street
the park

becomes

1111 street the park

any idea how I would do it by looking for the enter

thanks

Nigel
Click to expand...

If you're using Access 2000 or later, you can do both the splitting and
concatenation in one operation by using the Replace function to
substitute a space for the Chr(13) & Chr(10) character combination. For
example, in an update query:

UPDATE MyTable
SET StreetAddress =
Replace(StreetAddress, Chr(13) & Chr(10), " ")

Note that in the original release of Access 2000, the Replace function
could not be called directly from a query, so you had to create your own
"wrapper" function for it; e.g.,

Public Function MyRep(SourceText, ReplWhat, ReplWith)

MyRep = Replace(SourceText, ReplWhat, ReplWith)

End With

You would then call MyRep in the query instead of the Replace function.

I *think* one of the later service packs for A2K fixed this problem, but
I'm not sure. Certainly it's fixed in Access 2002.
 
thnaks that worked great, I then did some extra stuff to
search place in the street address if it wasn't 2 lines

IIf(Replace([streetaddress],Chr(13) & Chr(10)," ") Is
Null,[streetaddress],Replace([add1],Chr(13) & Chr(10)," "))

Nigel
 
Back
Top