How to insert "-" in certain position of records

[vc1881]

Hello, I am using VB.NET 2005 and MS ACCESS 2003

My database contains data in the following format:

1305000248767

I need to insert "-" in several location to obtain the following format

1305-00-024-8767

How do I append the records to insert the "-" at these locations.

Thanks,

Victor

 

[mansii]

You can do it in several ways.
One thing to remember is that the field must be long enough to accept new values.

Try using the Mid function.

Good luck.

 

[vc1881]

Can you please send me a small sample with rhe Mid function?

Thanks

 

[huggyboy]

Try this instead - its a .net string method - there are lots of different ones that are more useful than the traditional visual basic string commands

Dim strwork As String = "1305000248767"
strwork = strwork.Insert(4, "-").Insert(7, "-").Insert(11, "-")

 

[mansii]

Here is an example using the ExecuteNonQuery method:

Dim cmd1 As New OleDb.OleDbCommand
cmd1.Connection = connection 'change this
cmd1.CommandText = _
	"Update FieldName " & _
	"set FieldName = mid(FieldName ,1,4) + '-' + mid(FieldName ,5,2) + '-' + mid(FieldName ,7,3)  + '-' + mid(FieldName ,10,4)"
cmd1.ExecuteNonQuery()

Make sure that each record has the same length (13).
Good luck.

 

[mansii]

Sorry, it should be:

"Update TableName "

 

[vc1881]

Thank you for the code.

Victor