Removing particular characters from a field

[homer661]

I have a field in my database that looks like these examples:

W3939 -23433
X343 -12345
W3897 -13

Basically, I want to take everything to the right of the dash and put it in its own field. I'm guessing I'd have to build a custom function to do that. Little help?

 

[GingerR]

look up

len (LENGTH of the field)
instr (IN STRING-searches for a particular character or set of characters in a string)

Right([Field1],Len([Field1])-InStr([Field1],"-&quot)

sub. your field name for Field1.
this does what you want. you can put it into an update query if you want to add those values into a field in the table

 

[StillLearning]

Here is a solution:

Public Function CopyIt()
Dim db As Database
Dim rs As Recordset
Dim intPos As Integer       'Location of hyphen

Set db = CurrentDb
Set rs = db.OpenRecordset("table1", dbOpenDynaset)

With rs
Do While Not rs.EOF

    'Find the position of the hyphen
    intPos = InStr(1, rs!mytext, "-", 1) 
    .Edit

    'Add the value to the right of the "-" to the next field
    !mytext2 = Right(rs!mytext, (Len(rs!mytext) - intPos)) 
 
    'Update the  first field up to the hyphen
    !mytext = Left(rs!mytext, intPos)

    .Update
    .MoveNext
Loop
End With
End Function
[/code/]

Note: If you want to remove the hyphen from the first field
      replace !mytext = Left(rs!mytext, intPos) with
      !mytext = Left(rs!mytext, (intPos - 1))

I hopes this helps.