transform sql to vba ... issue with new line

[brigitteAT]

My probles is solved in the meantime! Thank you all so much!!!

I want several add on queries and procedures (private subs) to run automatically with one double-click

thus I tried to copy the sql into the procedure, but I have no idea how to create the (necessary ?) new lines in vba


it is probably quite simple but I failed:
the original sql looks like this (new lines start at "INSERT", "SELECT" and "FROM"

INSERT INTO FL ( FLDatum, FLJAHR, FLBUART, FLBELEGNR, FLText, FLKONR, FLKTO, FLKUC, FLGKb, FLGKn, FLKb,
FLKn, FLGKKTO, FLGKONR, FLPeriode )
SELECT [Abfrage MI-VS-Summe].FJDatum, [Abfrage MI-VS-Summe].FJJahr, [Abfrage MI-VS-Summe].FJBUART,
[Abfrage MI-VS-Summe].FJBelegNr, [Abfrage MI-VS-Summe].FJText, [Abfrage MI-VS-Summe].FJONR, [Abfrage
MI-VS-Summe].FJKTO, [Abfrage MI-VS-Summe].FJUC, [Abfrage MI-VS-Summe].SummevonFJHB, [Abfrage MI-VS-
Summe].SummevonFJHB, [Abfrage MI-VS-Summe].SummevonFJsb, [Abfrage MI-VS-Summe].SummevonFJsb,
[Abfrage MI-VS-Summe].FJGKKTO, [Abfrage MI-VS-Summe].FJGKONR, [Abfrage MI-VS-Summe].fjperiode
FROM [Abfrage MI-VS-Summe];

I found out, that with "_" I can cut the line and start a new one in vba, but this does not work for where a new line seems to be necessary....
thus I tried with vbfl an char(10), chr(13) ... but always get a message that something is missing...

DoCmd.RunSQL(INSERT INTO FL ( FLDatum, FLJAHR, FLBUART, FLBELEGNR, FLText, FLKONR, FLKTO, FLKUC, FLGKb, _
FLGKn, FLKb, FLKn, FLGKKTO, FLGKONR, FLPeriode ) & char(10) & _
SELECT [Abfrage MI-VS-Summe].FJDatum, [Abfrage MI-VS-Summe].FJJahr, [Abfrage MI-VS-Summe].FJBUART, _
[Abfrage MI-VS-Summe].FJBelegNr, [Abfrage MI-VS-Summe].FJText,[Abfrage MI-VS-Summe].FJONR, _
[Abfrage MI-VS-Summe].FJKTO, [Abfrage MI-VS-Summe].FJUC, [Abfrage MI-VS-Summe].SummevonFJHB, _
[Abfrage MI-VS-Summe].SummevonFJHB,[Abfrage MI-VS-Summe].SummevonFJsb, _
[Abfrage MI-VS-Summe].SummevonFJsb, [Abfrage MI-VS-Summe].FJGKKTO, [Abfrage MI-VS-Summe].FJGKONR, _
[Abfrage MI-VS-Summe].fjperiode & & char(10) & _
FROM [Abfrage MI-VS-Summe]

could somebody please give me a hint?
thanks in advance!!!

[Micron]

I have copied code from M$ pages where the line continuation character ( _ ) was used, only to have it fail and I could not figure out the problem - so I never use it. I use this appraoch

Dim strSql As String

strSql = "INSERT INTO FL (FLDatum, FLJAHR, FLBUART, FLBELEGNR, FLText, FLKONR, FLKTO, "
strSql = strSql & "FLKUC, FLGKb, FLGKn, FLKb, FLKn, FLGKKTO, FLGKONR, FLPeriode) "
strSql = strSql & "SELECT [Abfrage MI-VS-Summe].FJDatum, [Abfrage MI-VS-Summe].FJJahr, "
strSql = strSql & "[Abfrage MI-VS-Summe].FJBUART, [Abfrage MI-VS-Summe].FJBelegNr, "
strSql = strSql & "[Abfrage MI-VS-Summe].FJText, [Abfrage MI-VS-Summe].FJONR, [Abfrage MI-VS-Summe].FJKTO, "
strSql = strSql & "[Abfrage MI-VS-Summe].FJUC, [Abfrage MI-VS-Summe].SummevonFJHB, "
strSql = strSql & "[Abfrage MI-VS-Summe].SummevonFJHB, [Abfrage MI-VS-Summe].SummevonFJsb, "
strSql = strSql & "[Abfrage MI-VS-Summe].SummevonFJsb, [Abfrage MI-VS-Summe].FJGKKTO, "
strSql = strSql & "[Abfrage MI-VS-Summe].FJGKONR, [Abfrage MI-VS-Summe].fjperiode "
strSql = strSql & "FROM [Abfrage MI-VS-Summe];"

I put my spaces at the end of the line, some prefer the start. Key is to adopt one approach and stick with it. As you can see, I also create the variable(s) that will contain the string. I usually use the Execute method of the CurrentDb object to execute the sql. You didn't ask how to run the sql so I don't know if you got that figured out.

[Welshgasman]

You can't just add & to the sql?
You would need to make it a string as Micron has done. Then you can append a line feed or whatever.
I put my space at the start as easier to see for me?

[isladogs]

You may find my vba/sql conversion utility useful http://www.mendipdatasystems.co.uk/s...ain/4594398120

[brigitteAT]

GREAT!!!! thank you so much! it does what I want it to do!
hopefully that will also work with my next steps ... if not, be sure I'll be back ... :-):-):-)

I'working completely working by Trial and Error, thus I'm very grateful to get so much help here in this community!!!

[Micron]

I didn't see the DoCmd.RunSql in there - I took it as the sql itself. So it would never compile because there are no quotes. Concatenating vb line characters only made it worse. There is also a missing closing parenthesis. What might have worked

DoCmd.RunSQL("INSERT INTO FL (FLDatum, FLJAHR, FLBUART, FLBELEGNR, FLText, FLKONR, FLKTO, FLKUC, FLGKb, " _
& "FLGKn, FLKb, FLKn, FLGKKTO, FLGKONR, FLPeriode) SELECT [Abfrage MI-VS-Summe].FJDatum, " _
& "[Abfrage MI-VS-Summe].FJJahr, [Abfrage MI-VS-Summe].FJBUART, [Abfrage MI-VS-Summe].FJBelegNr, " _
& "[Abfrage MI-VS-Summe].FJText,[Abfrage MI-VS-Summe].FJONR, [Abfrage MI-VS-Summe].FJKTO, " _
& "[Abfrage MI-VS-Summe].FJUC, [Abfrage MI-VS-Summe].SummevonFJHB, [Abfrage MI-VS-Summe].SummevonFJHB, " _
& "[Abfrage MI-VS-Summe].SummevonFJsb, [Abfrage MI-VS-Summe].SummevonFJsb, [Abfrage MI-VS-Summe].FJGKKTO, " _
& "[Abfrage MI-VS-Summe].FJGKONR, [Abfrage MI-VS-Summe].fjperiode FROM [Abfrage MI-VS-Summe])"

[brigitteAT]

my first query now works with the method of "Micron" :-)
I'm going to try your method with my next query

[brigitteAT]

my first query now works with the method of "Micron" :-)
I'm now trying the method of Isladogs with my next query, but can't yet test it, as it doesn't accept characters which I defined in the Query

SELECT ..... , Abfragekriterien.Monat AS FJBelegNr, "VS " & [Monat] & "/21 " & [poskurz] AS FJText, .......

[Micron]

Your issue is likely incorrect concatenation and/or lack of proper quoting. The complete attempt would be more revealing/useful than what you posted just now, but if you do, please use code tags (# on forum toolbar) to encapsulate your code as I did. Makes it much easier to read. Copy, click # and paste.

[isladogs]

my first query now works with the method of "Micron" :-)
I'm now trying the method of Isladogs with my next query, but can't yet test it, as it doesn't accept characters which I defined in the Query
SELECT ..... , Abfragekriterien.Monat AS FJBelegNr, "VS " & [Monat] & "/21 " & [poskurz] AS FJText, .......

Replace the double quotes with single quotes.

SELECT ..... , Abfragekriterien.Monat AS FJBelegNr, 'VS ' & [Monat] & '/21 ' & [poskurz] AS FJText, .......

Your query should still work fine and the conversion should also work without issue

[brigitteAT]

thank you so much Isladogs! your Method looks easier in the end, but I found out, that for my purpose it is better to use the Method of Micron, as for my trial and error method, having the docmd. in one line only, I can test the whole procedure easier by just excluding one line of comand which do already work !

[Welshgasman]

Again, you are just concatenating to non strings?

"SELECT ..... , Abfragekriterien.Monat AS FJBelegNr, " &  "VS " & [Monat] & "/21 " & [poskurz]  & " AS FJText,"

[isladogs]

thank you so much Isladogs! your Method looks easier in the end, but I found out, that for my purpose it is better to use the Method of Micron, as for my trial and error method, having the docmd. in one line only, I can test the whole procedure easier by just excluding one line of comand which do already work !

Sorry but you've lost me....
Converting queries to SQL takes practice to get right but, once you have a method that works for you, it becomes straightforward to do.
My utility is mainly useful whilst in the learning stage...
However you approach the conversion, you cannot use double quotes in the middle of a SQL statement as Access will treat that as an endpoint.

[brigitteAT]

Thank you Welshgasman!!! Isladogs has already given me the solution and I marked the thread as solved... doesn't that work!?!?
sorry but I don't understand what you mean by
"conctenating to non strings" ?!?!

[Welshgasman]

Thank you Welshgasman!!! Isladogs has already given me the solution and I marked the thread as solved... doesn't that work!?!?
sorry but I don't understand what you mean by
"conctenating to non strings" ?!?!

By that I mean you have not surrounded a portion of the sql statement in quotes

Select whatever from whatever table & " this is my extra text that I want added"

The Select portion is not surrounded by quotes.