I have a text field in one of my tables that has words separated by
hyphens....I want to get of the hyphens,how do I remove them using VBA?

Arthur,

Use the Replace Function. Here is an example I threw together:

x= "A-Word-seperated-by-hyphens"
x=replace(x,"-"," ")
?x
A Word seperated by hyphens

"Arthur Zulu via AccessMonster.com" wrote:

>
>
> I have a text field in one of my tables that has words separated by
> hyphens....I want to get of the hyphens,how do I remove them using VBA?
>

Hi Arthur,

i hope, i understood you right :

Here is some code from the KnowHow.MDB from Klaus Oberdalhoff :
'*********
Function ReplaceStr(Textin, SearchStr, Replacement, Optional CompMode As
Integer = 2)
'
' Replaces the SearchStr string with Replacement string in the TextIn string.
' Uses CompMode to determine comparison mode
' Aus der Neatcd97.mdb Microsoft
'
Dim WorkText As String, Pointer As Integer
If IsNull(Textin) Then
ReplaceStr = Null
Else
WorkText = Textin
Pointer = InStr(1, WorkText, SearchStr, CompMode)
Do While Pointer > 0
WorkText = Left(WorkText, Pointer - 1) & Replacement & Mid(WorkText,
Pointer + Len(SearchStr))
Pointer = InStr(Pointer + Len(Replacement), WorkText, SearchStr,
CompMode)
Loop
ReplaceStr = WorkText
End If
End Function
'******

You can use it in that way :

newString = replaceStr(oldString,"-","")

Hth
Tino

"Arthur Zulu via AccessMonster.com" wrote:

>
>
> I have a text field in one of my tables that has words separated by
> hyphens....I want to get of the hyphens,how do I remove them using VBA?
>

Why create a user defined function to replace an intrinsic function?

"Tino Faeustel" wrote:

> Hi Arthur,
>
> i hope, i understood you right :
>
> Here is some code from the KnowHow.MDB from Klaus Oberdalhoff :
> '*********
> Function ReplaceStr(Textin, SearchStr, Replacement, Optional CompMode As
> Integer = 2)
> '
> ' Replaces the SearchStr string with Replacement string in the TextIn string.
> ' Uses CompMode to determine comparison mode
> ' Aus der Neatcd97.mdb Microsoft
> '
> Dim WorkText As String, Pointer As Integer
> If IsNull(Textin) Then
> ReplaceStr = Null
> Else
> WorkText = Textin
> Pointer = InStr(1, WorkText, SearchStr, CompMode)
> Do While Pointer > 0
> WorkText = Left(WorkText, Pointer - 1) & Replacement & Mid(WorkText,
> Pointer + Len(SearchStr))
> Pointer = InStr(Pointer + Len(Replacement), WorkText, SearchStr,
> CompMode)
> Loop
> ReplaceStr = WorkText
> End If
> End Function
> '******
>
> You can use it in that way :
>
> newString = replaceStr(oldString,"-","")
>
> Hth
> Tino
>
> "Arthur Zulu via AccessMonster.com" wrote:
>
> >
> >
> > I have a text field in one of my tables that has words separated by
> > hyphens....I want to get of the hyphens,how do I remove them using VBA?
> >

"Klatuu" wrote:

> Why create a user defined function to replace an intrinsic function?
>

Because i'm today within my Access97 project (yes, it lives! ).
And i thougt - there was something like replace --> press <F1> --> no
replace() function at all. So i found the replacestr()

But sorry, Klatuu 's right !!

> "Tino Faeustel" wrote:
>
> > Hi Arthur,
> >
> > i hope, i understood you right :
> >
> > Here is some code from the KnowHow.MDB from Klaus Oberdalhoff :
> > '*********
> > Function ReplaceStr(Textin, SearchStr, Replacement, Optional CompMode As
> > Integer = 2)
> > '
> > ' Replaces the SearchStr string with Replacement string in the TextIn string.
> > ' Uses CompMode to determine comparison mode
> > ' Aus der Neatcd97.mdb Microsoft
> > '
> > Dim WorkText As String, Pointer As Integer
> > If IsNull(Textin) Then
> > ReplaceStr = Null
> > Else
> > WorkText = Textin
> > Pointer = InStr(1, WorkText, SearchStr, CompMode)
> > Do While Pointer > 0
> > WorkText = Left(WorkText, Pointer - 1) & Replacement & Mid(WorkText,
> > Pointer + Len(SearchStr))
> > Pointer = InStr(Pointer + Len(Replacement), WorkText, SearchStr,
> > CompMode)
> > Loop
> > ReplaceStr = WorkText
> > End If
> > End Function
> > '******
> >
> > You can use it in that way :
> >
> > newString = replaceStr(oldString,"-","")
> >
> > Hth
> > Tino
> >
> > "Arthur Zulu via AccessMonster.com" wrote:
> >
> > >
> > >
> > > I have a text field in one of my tables that has words separated by
> > > hyphens....I want to get of the hyphens,how do I remove them using VBA?
> > >

Thanks Klatuu..tried the replace"-" with "," and it works just fine....But
i guess what i really wanted to do is delete all characters left of the
Hyphen...
for instance: "12/05/05 - 25/05/05"...

delete all characters left of "-" and remain with 12/05/05...with out using
functions like left(string,8)as the length of the string varies from record
to record.

is there anyone that can help?

many thanks,

Arthur

--
Message posted via http://www.accessmonster.com

Arthur,

It doesn't matter what the length of the string is in this example. This
will leave everything to the right of the hyphen.

right(x,instr(x,"-")-1)

So if your original string is "12/05/05 - 12/05/05"
The result is " 12/05/05" (notice leading space)

depending on your exact needs, you can either replace the -1 with -2 if you
know a space will always follow the hypen. If you are unsure whether it will
be a space or a character you want to keep, then you could use

Trim(right(x,instr(x,"-")-1)) which will do away with leading and trailing
spaces.

Now, if your string may have more than one hypen, one method to do this
would be to use the Split function. Let's say your string is "12/05/05 -
12/05/05 - 12/05/06"

x= "12/05/05 - 12/05/05 - 12/05/06"
y = Split(x,"-") or you can get rid of the spaces on either side of the
hyphen by
y = Split(x," - ")

The result is an array. In this case, you would have 3 elements to the
array. (assuming we are using the example that eliminates the spaces as above)
y(0) = "12/05/05"
y(1)="12/05/05"
y(2)="12/05/06"
You can determine how many elements you have with the Ubound function
(assume Option Base 0)
Ubound(y) = 2
so, if all you want is the last part of the string to the right of all the
hypyens, the answer will be in the last element of the array.


"Arthur Zulu via AccessMonster.com" wrote:

> Thanks Klatuu..tried the replace"-" with "," and it works just fine....But
> i guess what i really wanted to do is delete all characters left of the
> Hyphen...
> for instance: "12/05/05 - 25/05/05"...
>
> delete all characters left of "-" and remain with 12/05/05...with out using
> functions like left(string,8)as the length of the string varies from record
> to record.
>
> is there anyone that can help?
>
> many thanks,
>
> Arthur
>
> --
> Message posted via http://www.accessmonster.com
>

Tino,

I was not aware the replace function is not part of Access 97.

"Tino Faeustel" wrote:

> "Klatuu" wrote:
>
> > Why create a user defined function to replace an intrinsic function?
> >
>
> Because i'm today within my Access97 project (yes, it lives! ).
> And i thougt - there was something like replace --> press <F1> --> no
> replace() function at all. So i found the replacestr()
>
> But sorry, Klatuu 's right !!
>
> > "Tino Faeustel" wrote:
> >
> > > Hi Arthur,
> > >
> > > i hope, i understood you right :
> > >
> > > Here is some code from the KnowHow.MDB from Klaus Oberdalhoff :
> > > '*********
> > > Function ReplaceStr(Textin, SearchStr, Replacement, Optional CompMode As
> > > Integer = 2)
> > > '
> > > ' Replaces the SearchStr string with Replacement string in the TextIn string.
> > > ' Uses CompMode to determine comparison mode
> > > ' Aus der Neatcd97.mdb Microsoft
> > > '
> > > Dim WorkText As String, Pointer As Integer
> > > If IsNull(Textin) Then
> > > ReplaceStr = Null
> > > Else
> > > WorkText = Textin
> > > Pointer = InStr(1, WorkText, SearchStr, CompMode)
> > > Do While Pointer > 0
> > > WorkText = Left(WorkText, Pointer - 1) & Replacement & Mid(WorkText,
> > > Pointer + Len(SearchStr))
> > > Pointer = InStr(Pointer + Len(Replacement), WorkText, SearchStr,
> > > CompMode)
> > > Loop
> > > ReplaceStr = WorkText
> > > End If
> > > End Function
> > > '******
> > >
> > > You can use it in that way :
> > >
> > > newString = replaceStr(oldString,"-","")
> > >
> > > Hth
> > > Tino
> > >
> > > "Arthur Zulu via AccessMonster.com" wrote:
> > >
> > > >
> > > >
> > > > I have a text field in one of my tables that has words separated by
> > > > hyphens....I want to get of the hyphens,how do I remove them using VBA?
> > > >

Thanks Klatuu,
I started out using your replace function....so replace "-" with a blank
space-large enough so when i pick the last 10, it will be the characters i
need plus a few blanks (" ")....then i used the function that trims
all characters save for the last ten(right(Mystr,10)) and it worked
wonders...am looking at your latest posting and looks like even this does
the trick..thanks again...you're life saver....

Arthurz

--
Message posted via http://www.accessmonster.com