Change of data sequence ?

[Peter]

In an Access 97 database, we get a field containing data
like 23/2001, 367/2001, 12/2002, 3/2005.

There is a request to change the display as
BB2001/23
BB2001/367
BB2002/12
BB2005/3

Is there any easy way to do it ?

Thanks

 

[Naresh Nichani MVP]

Hi:

You could write this code in a VBA Module.

Function ConvertField()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strField as String
Dim arr() as string

Set db = CurrentDb()
Set rs = db.OpenRecordset("YourTableName",dbOpenDynaset)
DO while not rs.EOF
strField = rs.Fields("FIeldtoChange")
arr = split(strfield,"/")
If Ubound(arr) = 1 then
strField = "BB" & arr(1) & "\" & arr(0)
rs.Fields("FieldToChange") = strField
rs.Update
end if
rs.Movenext
loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function

Click inside code and press F5 to run it. Make sure you backup the database
before doing this.

Regards,

Naresh Nichani
Microsoft Access MVP


You could do thsi wth