Manipulation of Data

[Peter]

From a query, we get the result like
ABC-429/1999
B-36/2002
DE-34/2004
DE-45/2003.

We would like to change it to
ABC1999/429
B2002/36
DE2004/34
DE2003/45

Is there any easy way to do so ? Thank you for your help.

 

[Albert D.Kallal]

I would make a function that returns he data in the correct format. you can
then use a update query to send the results to a new column

The following funciton would do the trick

Public Function FixId(v As Variant) As Variant

Dim p1 As String
Dim p2 As String
Dim p3 As String

If IsNull(v) Then
Exit Function
End If

p1 = Split(v, "-")(0)
p2 = Split(v, "-")(1)

p3 = Split(p2, "/")(1)
p2 = Split(p2, "/")(0)

FixId = p1 & p3 & "/" & p2

End Function

 

[Van T. Dinh]

Do you want to change the data in the Table or simply return data without
the dash in the DatasheetView of the Query?

If the former, use an Update Query with a replace function. If the later,
use a calculated Field in the Select Query like:

FormattedField1: Replace([Field1], "-", "")