By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Combine Every Other line from 2 fields

P: 7
Hello Experts!

I have two fields, one with lyrics and one with chords (for chord sheet creation). I currently have:

Field 1:
You are my sunshine
My only sunshine
You make me happy
When skies are gray

Field 2:

G
C G
G
D

I want:

G
You are my Sunshine
C G
My only sunshine
G
You make me happy
D
When skies are gray

Does anyone have any idea how I can do this. I don't know VBA very well but am definitely willing to try something out if someone has a sample code. I am a fast learner!

update:
To elaborate a bit, it seems that I need a way to be able to find every carriage return in the two fields, and tell it to write the next line from every other field, base on finding the carriage return? I just don't know how to do this. Can this be done with SQL or is a VBA script necessary. Could someone give me a sample code that might work?
Nov 5 '11 #1
Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,679
Are the Results 4 Records with a Cr/Lf, or 8 Records with Odd Records representing Chords and Even Records representing Lyrics?
Expand|Select|Wrap|Line Numbers
  1. 'Record 1
  2. G
  3. You are my Sunshine
Expand|Select|Wrap|Line Numbers
  1. 'Record 2
  2. C G
  3. My only sunshine
Expand|Select|Wrap|Line Numbers
  1. 'Record 3
  2. You make me happy
Expand|Select|Wrap|Line Numbers
  1. 'Record 4
  2. D
  3. When skies are gray
OR
Expand|Select|Wrap|Line Numbers
  1. 'Record 1 - G
Expand|Select|Wrap|Line Numbers
  1. 'Record 2 - You are my Sunshine
Expand|Select|Wrap|Line Numbers
  1. 'Record 3 - C G
Expand|Select|Wrap|Line Numbers
  1. 'Record 4 - My only sunshine
Expand|Select|Wrap|Line Numbers
  1. 'Record 5 - G
Expand|Select|Wrap|Line Numbers
  1. 'Record 6 - You make me happy
Expand|Select|Wrap|Line Numbers
  1. 'Record 7 - D
Expand|Select|Wrap|Line Numbers
  1. 'Record 8 - When skies are gray
Nov 5 '11 #2

P: 7
The results should be a single field within the existing record containing:

G
You are my Sunshine
C G
My only sunshine
G
You make me happy
D
When skies are gray

It would be fine if this was done with a query in SQL. if that can't be done, I'd like to be able to tell it to just create a new field within the existing record, that combines the two fields within that exist already within the record, as I show above.
Nov 5 '11 #3

ADezii
Expert 5K+
P: 8,679
  1. Query Definition (Table name = tblTest):
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblTest.Lyric, tblTest.Chord, [Chord] & Chr(13) & Chr(10) & [Lyric] AS Lyric_Chord
    2. FROM tblTest;
  2. Query OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. Lyric                Chord    Lyric_Chord
    2. You are my sunshine    G          G
    3.                                   You are my sunshine
    4. My only sunshine       C G        C G
    5.                                   My only sunshine
    6. You make me happy      G          G
    7.                                   You make me happy
    8. When skies are gray    D          D
    9.                                   When skies are gray
Nov 5 '11 #4

P: 7
I'm not sure that this works. My table is not split into multiple entries for each line of the lyric and chord. As I showed before, I have a single record with two fields. One field contains the Lyrics and one field contains the chords. It is multiple lines of text with in the text field, and each line is separated by a carriage return.

I need a way to tell Access to take the first line from the Chords field, then the first line from the Lyrics field, then the second line from the Chords field, and then the second line from the Lyrics field, etc. until it has combined all the contents from both fields.

Do you have a code for that?
Nov 5 '11 #5

P: 7
Attached is an screenshot of a few entries in my database table...hopefully this will help.
Attached Images
File Type: jpg Capture.jpg (62.9 KB, 149 views)
Nov 5 '11 #6

ADezii
Expert 5K+
P: 8,679
The following Revised Code makes the major assumption that both the [Chords] and [Lyrics] Fields cannot be NULL and they they have exactly the same number of lines per Field/per Record.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTest.Lyric, tblTest.Chord, fCombineFields([Lyric],[Chord]) AS Conbined
  2. FROM tblTest;
Expand|Select|Wrap|Line Numbers
  1. Public Function fCombineFields(strLyric As String, strChord As String)
  2. Dim strBuild As String
  3. Dim varChord As Variant
  4. Dim varLyric As Variant
  5. Dim intCtr As Integer
  6.  
  7. varChord = Split(strChord, vbCrLf)
  8. varLyric = Split(strLyric, vbCrLf)
  9.  
  10. For intCtr = LBound(varChord) To UBound(varChord)
  11.   strBuild = strBuild & varChord(intCtr) & vbCrLf & varLyric(intCtr) & vbCrLf
  12. Next
  13.  
  14. 'remove Trailing Cr/Lf
  15. strBuild = Left$(strBuild, Len(strBuild) - 2)
  16.  
  17. fCombineFields = strBuild
  18. End Function
Nov 5 '11 #7

P: 7
This is really great, thanks so much for helping! I understand the SQL part, however, I am not sure I know the steps to insert this function into Access...could you help with that? My questions are below:

1. Can this code be modifed to account for a null field as well as field with different numbers of line?

2. My field names are "Verse1Lyrics" and "Verse1Chords", where would I put those in the function so it knows what fields/table it is looking at?

3. I am interested in learning how to write code like this, so if you wouldn't mind commenting on your sections, I'd appreciate it so I can understand how you are building this code with the functions you are using!

I hope you don't get frustrated with me asking so many questions. I have spent so much time on this database, unfortunately, didn't think that I would ever need to get the data back out in this way, and your help is going to save me so much time!
Nov 5 '11 #8

P: 7
I'd still love some clarification, but I just tried this code, and it seems to work. I was able to create a "module" within Access, copied your function in, and changed [Lyric] and [Chord] in the SQL query to the table fields I needed.

It seemed to return results correctly, even with fields with varying number of lines per record.
Nov 5 '11 #9

ADezii
Expert 5K+
P: 8,679
@Josh:
What, exactly, don't yoou understand?
Nov 5 '11 #10

P: 7
I think I understand ok. Here is how I am handling the null situation with SQL. Probably not the most elegant solution, but this is working well:

Expand|Select|Wrap|Line Numbers
  1. SELECT IIf(IsNull(Songs.IntroChords),"","Intro:"+Chr(13)+Chr(10)+Chr(13)+Chr(10))+"Verse1:"+Chr(13)+Chr(10)+fCombineFields(Songs.Verse1Lyrics,Songs.Verse1Chords)+IIf(IsNull(Songs.PreChorus1Lyrics),"",Chr(13)+Chr(10)+Chr(13)+Chr(10)+"Pre-Chorus:"+Chr(13)+Chr(10)+Nz(fCombineFields(Songs.PreChorus1Lyrics,Songs.PreChorus1Chords)))+IIf(IsNull(Songs.Chorus1Lyrics),"",Chr(13)+Chr(10)+Chr(13)+Chr(10)+"Chorus:"+Chr(13)+Chr(10)+Nz(fCombineFields(Songs.Chorus1Lyrics,Songs.Chorus1Chords))) +IIf(IsNull(Songs.Bridge1Lyrics),"",Chr(13)+Chr(10)+Chr(13)+Chr(10)+"Bridge 1:"+Chr(13)+Chr(10)+Nz(fCombineFields(Songs.Bridge1Lyrics,Songs.Bridge1Chords))) AS Conbined
  2. FROM Songs;
The result I am getting, but doing this, since I have multiple fields for different parts of the song, is working, and displays as follows:

Expand|Select|Wrap|Line Numbers
  1. Verse1:
  2.      E
  3. How lovely is Your dwelling place,
  4. A2           Bsus
  5.  oh Lord Almighty
  6.  E                                 Bsus
  7. My soul longs and even faints for You
  8.      E
  9. For here my heart is satisfied,
  10. A2              Bsus
  11.  within Your presence
  12.    E                               Bsus
  13. I sing beneath the shadow of Your wings
  14.  
  15. Chorus:
  16.            A2
  17. Better is one day in Your courts
  18.            Bsus
  19. Better is one day in Your house
  20.            A2
  21. Better is one day in Your courts
  22.                 Bsus
  23. Than thousands elsewhere
  24.                                      (E)
  25. (Than thousands elsewhere)
  26.  
  27. Bridge 1:
  28. C#m7                     Bsus
  29.  My heart and flesh cry out,
  30. A2                   Bsus
  31.  for You the living God
  32. C#m7                  Bsus                 A2        Bsus
  33.  Your spirit’s water for my soul
  34. C#m7                     Bsus
  35.  I’ve tasted and I’ve seen,
  36. A2                   Bsus
  37.  come once again to me
  38. C#m7           Bsus        A2
  39.  I will draw near to You
  40.                     Bsus         F#m         A2
  41.  I will draw near to You, to You
Thanks so much for the help. I am able to make your code work for what I need to do I think. I don't understand the script language itself, but I was able to work out the Null situation using SQL.
Nov 5 '11 #11

ADezii
Expert 5K+
P: 8,679
Glad you were able to work through the problem, even with limited VBA info.
Nov 5 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.