Spreadig a DoCmd.RunSQL over multiple lines

[xanadude]

Hi All

I was hoping somebody could help as I am using the RunSQL to insert data from a form into a table using the following code.

        DoCmd.RunSQL "INSERT INTO Bowers_Core_Table ([weekCommence],[agentName],[auditor],[ref],[greeting_1],[greeting_2],[greeting_3],[greetingTotal],[greetingNotes],[DPAFail],[DPA],[DPATotal],[DPANotes],[throughout_1],[throughout_2],[throughout_3],[throughout_4],[throughoutTotal],[throughoutNotes],[resolve_1],[resolve_2],[resolve_3],[resolveNotes],[resolveTotal],[close],[closeNotes],[notes_1],[notes_2],[notes_3],[notes_4],[notesTotal],[notesNotes],[FirstCallResolution],[FCRNotes],[TotalScore],[PCUsed],[WindowLogon],[DateScored],[WOW],[WOWNotes])" & _
        "VALUES ('" & Me.weekCommence & "','" & Me.agentName & "','" & Me.auditor & "','" & Me.refNumber & "','" & Me.greeting1y & "','" & Me.greeting2y & "','" & Me.greeting3y & "','" & Me.greetingScore & "','" & Me.greetingNotes & "','" & Me.DPAn & "','" & Me.DPAy & "','" & Me.DPAScore & "','" & Me.DPANotes & "','" & Me.Throughout1y & "','" & Me.Throughout2y & "','" & Me.Throughout3y & "','" & Me.Throughout4y & "','" & Me.throughoutScore & "','" & Me.throughoutNotes & "','" & Me.Resolve1y & "','" & Me.Resolve2y & "','" & Me.Resolve3y & "','" & Me.resolveNotes & "','" & Me.ResolveScore & "','" & Me.closey & "','" & Me.closeNotes & "','" & Me.notes1y & "','" & Me.notes2y & "','" & Me.notes3y & "','" & Me.notes4y & "','" & Me.notesScore & "','" & Replace(Me.notesNotes, "'", "''") & "','" & Me.fcry & "','" & Replace(Me.FCRNotes, "'", "''") & "','" & Me.TotalScore & "','" & Environ("COMPUTERNAME") & "','" & Me.auditor & "','" & Me.EntryDate & "','" & Me.WOWCheck & "','" & Replace(Me.WowNotes, "'", "''") & "')"

This works at present but I need to modify a few area to use the Replace option for issues when entering quotes. Example :& Replace(Me.WowNotes, "'", "''") &

But my problem is I have hit the line length on the Values line and wish to spread this over a couple of lines to get it to work and enter all the fields.

Advice much appreciated

Thanks

 

[pr2-eugin]

Could not actually understand your problem.. Are you trying to add line breaks?

DoCmd.RunSQL "INSERT INTO Bowers_Core_Table ([weekCommence],[agentName],[auditor],[ref],[greeting_1],[greeting_2],[greeting_3], " & _
             "[greetingTotal],[greetingNotes],[DPAFail],[DPA],[DPATotal],[DPANotes],[throughout_1],[throughout_2],[throughout_3], " & _
             "[throughout_4],[throughoutTotal],[throughoutNotes],[resolve_1],[resolve_2],[resolve_3],[resolveNotes],[resolveTotal], " & _
             "[close],[closeNotes],[notes_1],[notes_2],[notes_3],[notes_4],[notesTotal],[notesNotes],[FirstCallResolution],[FCRNotes], " & _
             "[TotalScore],[PCUsed],[WindowLogon],[DateScored],[WOW],[WOWNotes]) VALUES " & _
             "('" & Me.weekCommence & "','" & Me.agentName & "','" & Me.auditor & "','" & Me.refNumber & "','" & Me.greeting1y & "','" & _
             Me.greeting2y & "','" & Me.greeting3y & "','" & Me.greetingScore & "','" & Me.greetingNotes & "','" & Me.DPAn & "','" & _
             Me.DPAy & "','" & Me.DPAScore & "','" & Me.DPANotes & "','" & Me.Throughout1y & "','" & Me.Throughout2y & "','" & _
             Me.Throughout3y & "','" & Me.Throughout4y & "','" & Me.throughoutScore & "','" & Me.throughoutNotes & "','" & Me.Resolve1y & "','" & _
             Me.Resolve2y & "','" & Me.Resolve3y & "','" & Me.resolveNotes & "','" & Me.ResolveScore & "','" & Me.closey & "','" & _
             Me.closeNotes & "','" & Me.notes1y & "','" & Me.notes2y & "','" & Me.notes3y & "','" & Me.notes4y & "','" & _
             Me.notesScore & "','" & Replace(Me.notesNotes, "'", "''") & "','" & Me.fcry & "','" & Replace(Me.FCRNotes, "'", "''") & "','" & _
             Me.TotalScore & "','" & Environ("COMPUTERNAME") & "','" & Me.auditor & "','" & Me.EntryDate & "','" & Me.WOWCheck & "','" & _
             Replace(Me.WowNotes, "'", "''") & "')"

 

[xanadude]

Sorry if not very clear a little brain fried myself
I think what you have shown is what I was looking for I will try and confirm

Thank You

 

[xanadude]

Thank you this is exactly what I needed... so simple.... both me and the solution

Again thanks

 

[pr2-eugin]

Glad to have helped.. Good Luck..

 

[xanadude]

Hi back again with similar question

using this code

  DoCmd.RunSQL "INSERT INTO CODING_CORE_TABLE ([teamSenior],[agentName],[weekCommence])" & _
  "VALUES ('" & Me.teamSenior & "','" & _
  Me.agentName & "','" & _
  Me.weekCommence & "')"

I need to go to a long version again so would like to know how to put the target table entries over multiple line..... just cant get my head around it

To look something like this

  DoCmd.RunSQL "INSERT INTO CODING_CORE_TABLE ([teamSenior], _
  [agentName], _
  [weekCommence])" & _
  "VALUES ('" & Me.teamSenior & "','" & _
  Me.agentName & "','" & _
  Me.weekCommence & "')"

again thanks for your time

 

[pr2-eugin]

Concatenation is a Key factor.. For Strings, you need to Close the String before giving a Line Break and concatenate at the beginning of new line or before breaking the line....

Lets take an example..

StringValue  = "Hello, my name is Paul Eugin, it is very good to see you here. Hope you are doing good."

If you want to add line breaks.. you need to first break them at necessary bits.. Once you break you will get Compiler Error.. For this minute ignore it, and carry on..

StringValue  = "Hello, [B][COLOR=Red]_[/COLOR][/B]
                my name is Paul Eugin, [COLOR=Red][B]_[/B][/COLOR]
                it is very good to see you here.[COLOR=Red][B] _[/B][/COLOR]
                Hope you are doing good. [COLOR=Red][B]_[/B][/COLOR]

This is where you are at.. Now apply String Close..

StringValue  = [COLOR=Red][B]"[/B][/COLOR]Hello, [COLOR=Red][B]"[/B][/COLOR] _
                [COLOR=Red][B]"[/B][/COLOR]my name is Paul Eugin, [COLOR=Red][B]"[/B][/COLOR] _
                [COLOR=Red][B]"[/B][/COLOR]it is very good to see you here. [COLOR=Red][B]"[/B][/COLOR] _
                [COLOR=Red][B]"[/B][/COLOR]Hope you are doing good.[COLOR=Red][B]"[/B][/COLOR]

Now, finally concatenate them together..

StringValue  = [COLOR=Red][B]"[/B][/COLOR]Hello, [COLOR=Red][B]"[/B][/COLOR] [COLOR=Blue][B]&[/B][/COLOR] _
                [COLOR=Red][B]"[/B][/COLOR]my name is Paul Eugin, [COLOR=Red][B]"[/B][/COLOR] [COLOR=Blue][B]&[/B][/COLOR] _
                [COLOR=Red][B]"[/B][/COLOR]it is very good to see you here. [COLOR=Red][B]"[/B][/COLOR] [COLOR=Blue][B]&[/B][/COLOR] _
                [COLOR=Red][B]"[/B][/COLOR]Hope you are doing good.[COLOR=Red][B]"[/B][/COLOR]

There you are.. When you get the idea, you would automatically Close, Concatenate and Break..

 

[xanadude]

Thank you that actually makes sense to this fried brain .....

Much appreciated

 

[pr2-eugin]

Just always remember this.. Close, Concatenate and Break... Good Luck..

 

[Galaxiom]

I'll add my two cents worth.

I prefer to put the concatenation operator and any required spaces at the begining of the line. It is easier to see them there all in the same columns

StringValue  = "Hello," _
             & " my name is Paul Eugin,"  _
             & " it is very good to see you here."  _
             & " Hope you are doing good."

Also note that there is a limit of 22 continuations in a statement.

After that you can use what I call iterative concatenation for want of a better term.

StringValue = "whatever"
StringValue = StringValue & " some more characters"

SOme developers prefer this to continuation and use it all the time. I cannot fathom any good reason for this as it adds a lot of unnecessary typing, makes it harder to read and slower to process.

Another alternative is to build separate strings and then concatenate the varaibles together.

For example. I don't often need more than 22 lines in an SQL string but when I do I will use continuation to build the Select and Where sections as separate strings then concatenate them together.