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

Pull the data out as a string, then re-assign the string using .SubString() to insert the hyphen(s) in the correct places. For Example:

__________________
Currently using: VS 2005 & 2008 Pro w/sp1 on Win7 Ultimate x64.


There are 3 kinds of people in the world: Those who can count and those who can't.
4 out of 3 people have trouble with fractions.

Windows has a 64 bit GUI for a set of 32 bit extensions on a 16 bit shell for an 8 bit OS using a 4 bit kernel made by a 2 bit company that can't stand 1 bit of competition.

Hello,

Thanks for your reply, I'm afraid I can't assign mystring to a field value.
Do you have another approach?

This is what I have so far, but can't figure out the mySQL_Statement, or perhaps I need to use a For Next stament to go over each record??

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aopt2002orgorg.mdb;Persist Security Info=True;Jet OLEDBatabase Password=testaopupdate"
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)

'data adapter
Dim objDataAdapter As New OleDb.OleDbDataAdapter(mySQL_Statement, objConnection)

'dataset object
Dim objDataSet As New DataSet
'fill dataset
objConnection.Open()
objDataAdapter.Fill(objDataSet, "SN")
objConnection.Close()

Thanks

I'd use the substring function for T-SQL. Here's an example of formatting a social security number.

I believe (don't use Access) that instead of SUBSTRING you use MID (same parameters) and '&' to concatenate rather than '+'.

Matt,

Thanks for your input, I believe SSN will be a new field created automatically. I was able to make the conversion, but the code I used was much longer, I will also try it with your code.

I initially made a mistake the data in the field didn't have the first 4 characters, basically it was in two separate fields:

Field 1: 1340 and Field 2: 001188989, I modified the code to obtain 00-118-8989

Now I need to do a check with a second table which contains

1340-00-118-8989(1)
1305-00-198-8989
1340-00-118-8989(1)
1315-00-108-7777
etc...

How do I start my search after the fifth position to check against data in the table which contains the 00-118-8989 format and only take into consideration the 11 chatracters ( will also need to avoid the (1) data from the other table).

I propbably should open a new thread, but since you're familiar with what I'm trying to do, I thought it would be easier to get some feedback.

Thanks,

Victor

You want to use these 2 values to do a JOIN on the tables?

How would you use this statement with a where clause?
I will search the tables bases on values entered in a textbox.

Thanks,

Victor