Combine Every Other line from 2 fields

[Josh Moore]

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?

[ADezii]

Are the Results 4 Records with a Cr/Lf, or 8 Records with Odd Records representing Chords and Even Records representing Lyrics?

'Record 1
G
You are my Sunshine

'Record 2
C G
My only sunshine

'Record 3
G 
You make me happy

'Record 4
D
When skies are gray

OR

'Record 1 - G

'Record 2 - You are my Sunshine

'Record 3 - C G

'Record 4 - My only sunshine

'Record 5 - G

'Record 6 - You make me happy

'Record 7 - D

'Record 8 - When skies are gray

[Josh Moore]

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.

[ADezii]

1. Query Definition (Table name = tblTest):
   

   SELECT tblTest.Lyric, tblTest.Chord, [Chord] & Chr(13) & Chr(10) & [Lyric] AS Lyric_Chord
   FROM tblTest;

2. Query OUTPUT:
   

   Lyric                Chord    Lyric_Chord
   You are my sunshine    G          G
                                     You are my sunshine
   My only sunshine       C G        C G
                                     My only sunshine
   You make me happy      G          G
                                     You make me happy
   When skies are gray    D          D
                                     When skies are gray

[Josh Moore]

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?

[Josh Moore]

Attached is an screenshot of a few entries in my database table...hopefully this will help.

[ADezii]

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.

SELECT tblTest.Lyric, tblTest.Chord, fCombineFields([Lyric],[Chord]) AS Conbined
FROM tblTest;

Public Function fCombineFields(strLyric As String, strChord As String)
Dim strBuild As String
Dim varChord As Variant
Dim varLyric As Variant
Dim intCtr As Integer
 
varChord = Split(strChord, vbCrLf)
varLyric = Split(strLyric, vbCrLf)
 
For intCtr = LBound(varChord) To UBound(varChord)
  strBuild = strBuild & varChord(intCtr) & vbCrLf & varLyric(intCtr) & vbCrLf
Next
 
'remove Trailing Cr/Lf
strBuild = Left$(strBuild, Len(strBuild) - 2)
 
fCombineFields = strBuild
End Function

[Josh Moore]

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!

[Josh Moore]

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.

[ADezii]

@Josh:
What, exactly, don't yoou understand?

[Josh Moore]

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:

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
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:

Verse1:
     E
How lovely is Your dwelling place,
A2           Bsus
 oh Lord Almighty
 E                                 Bsus
My soul longs and even faints for You
     E
For here my heart is satisfied,
A2              Bsus
 within Your presence
   E                               Bsus
I sing beneath the shadow of Your wings
 
Chorus:
           A2
Better is one day in Your courts
           Bsus
Better is one day in Your house
           A2
Better is one day in Your courts
                Bsus
Than thousands elsewhere
                                     (E)
(Than thousands elsewhere)
 
Bridge 1:
C#m7                     Bsus
 My heart and flesh cry out,
A2                   Bsus
 for You the living God
C#m7                  Bsus                 A2        Bsus
 Your spirit’s water for my soul
C#m7                     Bsus
 I’ve tasted and I’ve seen,
A2                   Bsus
 come once again to me
C#m7           Bsus        A2
 I will draw near to You
                    Bsus         F#m         A2
 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.

[ADezii]

Glad you were able to work through the problem, even with limited VBA info.