how to split a field in a database containing delimiters?

[olgadji]

Hello everybody! I would be happy if someone can help me in my problem.

I have an Access table with 3 columns (ID, book_title and book_description).
The records look like: (the column names are at the top )

ID book_title book_descript
1 My first book (My book 1; Book 1) First book description
2 My book 2 (Book 2; Just Book 2; B2) Second book descr.....

I.e. each book_title field contains a book name in a format : BookName (Bookname1; Bookname2; Bookname3;...;BookNameN)

I need to split the book_title into new rows, like :

ID Book_title Book_desc
1 My first book First book description
2 My book 1 First book description
3 Book 1 First book description...

How could I make it? Got any ideas? I would appreciate your help!!!! Thanks in advance.

 

[KenHigg]

Something like:

MyNewTitle: Left(book_title, InStr(book_title, "(") - 1)

 

[olgadji]

Something like:

MyNewTitle: Left(book_title, InStr(book_title, "(") - 1)

Not exactly, dear kenhigg

 

[KenHigg]

Was it close?

 

[olgadji]

Was it close?

Is the Earth close to the Moon, what do you think?

 

[KenHigg]

Ok then. Good luck.

 

[MSAccessRookie]

Hello everybody! I would be happy if someone can help me in my problem.

I have an Access table with 3 columns (ID, book_title and book_description).
The records look like: (the column names are at the top )

ID book_title book_descript
1 My first book (My book 1; Book 1) First book description
2 My book 2 (Book 2; Just Book 2; B2) Second book descr.....

I.e. each book_title field contains a book name in a format : BookName (Bookname1; Bookname2; Bookname3;...;BookNameN)

I need to split the book_title into new rows, like :

ID Book_title Book_desc
1 My first book First book description
2 My book 1 First book description
3 Book 1 First book description...

How could I make it? Got any ideas? I would appreciate your help!!!! Thanks in advance.

I misread your intentions the first time and removed my original reply.

I misread your intentions the first time and removed my original reply.

My new reply is based on the following assumption.

The line "1 My first book (My book 1; Book 1) First book description" that you displayed is based on a table record that looks like this:

ID: 1
Title: My first book (My book 1; Book 1)
book_descript: First book description


Assuming that this is true, then you might have to use VB (due to the need for loop checking of entries in the title list), and the queries below should get you started in the direction of what you want:

[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]To get the first book in the list[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] [/FONT][FONT=Times New Roman]   Left(book_title, InStr(book_title, "(") - 1)[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]To get the Second book in the list:[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]  Mid(book_title, InStr(book_title, "(") + 1, [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   ((InStr(book_title, ";" - 1) – (InStr(book_title, "(") + 1))[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]To get the nth book in the list (not the last), VB Code that remembers [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]the location of each previous ";" character will need to be used[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]To get the Last book in the list:[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]Mid(book_title, InStr(book_title, ";") + 1, [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   ((InStr(book_title, ")" - 1) – (InStr(book_title, ";") + 1))[/FONT][/SIZE]