"end of statement expected error in query"

[atksamy]

HI,

I am using the following query in vba and i get a compile error end of statement expected.

sqltext = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
             "Format([CountOfuart]/DCount("[B][I][U]utyp[/U][/I][/B]","01_umwelt","utyp=" & [utyp]),"Percent") AS [%UTYP]," & _
             "Format([CountOfuart]/DCount("uart","01_umwelt","uart=" & [uart]),"Percent") AS [%UART]" & _
             "INTO UTYP_UART FROM 01_umwelt GROUP BY  [01_umwelt].utyp,  [01_umwelt].uart"

I get it at utyp which i have marked in bold and italic

Thanks
atksamy

[Stewart Ross]

Hi. Problem is the use of the double quotes in the DCount and Format statements, as these are also the begin/end characters of the sqltext string.

To use a double quote character within a string in VBA you need to use three in sequence:

"This is a double quote """ within the string"

This can get really difficult to read (and potentially impossible to write correctly in your SQL string).

Alternatively, you can use Chr(34) to replace each pre-and post double quote in the DCount, but getting the sequence of substrings correct is messy:

"Format([CountOfuart]/DCount(" & Chr(34) & "utyp" & Chr(34) & "," & Chr(34) & "01_umwelt" & Chr(34) & "," & Chr(34) & "utyp=" & Chr(34) & [utyp]& "), Chr(34) & "Percent" & Chr(34) & ") AS [%UTYP]," & _

If it is possible to reformulate your SQL to do away with the Format and DCounts it will be so much easier to read, as well as performing much better without the DCounts.

-Stewart

[atksamy]

Well i tried as you said and got something like

sqltext = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
           "Format([CountOfuart]/DCount(" & Chr(34) & "utyp" & Chr(34) & "," & Chr(34) & "01_umwelt" & Chr(34) & "," & Chr(34) & "utyp=" & Chr(34) & [utyp] & ")," & Chr(34) & "Percent" & Chr(34) & ") AS [%UTYP]," & _
           "Format([CountOfuart]/DCount(" & Chr(34) & "uart" & Chr(34) & "," & Chr(34) & "01_umwelt" & Chr(34) & "," & Chr(34) & "uart=" & Chr(34) & [utyp] & ")," & Chr(34) & "Percent" & Chr(34) & ") AS [%UART]," & _
           "INTO UTYP_UART FROM 01_umwelt GROUP BY  [01_umwelt].utyp,  [01_umwelt].uart

"

but i am getting a runtime error 13 type mismatch now

[Stewart Ross]

Hi. The run-time error is a different matter unrelated to your original problem I reckon. One possibility is that the type of one or more of the fields you are filtering in DCount does not match what it expects.

I would suggest that you add

debug.print sqltext

just after your sqltext variable is set, set a breakpoint just after it, and check that the sql string makes sense. You could copy its value from the VB immediate window and paste it into your next post if you want us to check it for you.

Again, I would advise you that if it is at all possible you should revise your underlying SQL to remove the need for the DCounts altogether. I would generate a query which already has all necessary values computed within it, and refer to the relevant total or count field from that instead of doing it the way you are currently trying. That is just my opinion, however, but I do feel it will save you a lot of work in the longer term.

-Stewart

[atksamy]

Actually the query works fine when i run it directly instead of vba.

Also if i place the debug statement after sql = ""

still the error pops out and there s nothing in immediate window

i guess the code is failing at sql statement itseld control is not goin to debug statement

[Stewart Ross]

Sorry, but I am certain you have not debugged this one as suggested. I have myself set up a test string to investigate what is going on, and it gives the following output for sqltext in the immediate window (with a substitution of the control name utyp for the contents of the field, which I don't have access to):

SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart,
Format([CountOfuart]/DCount("utyp","01_umwelt","utyp="utyp),"Percent") AS [%UTYP],
Format([CountOfuart]/DCount("uart","01_umwelt","uart="utyp),"Percent") AS [RT], 
INTO UTYP_UART FROM 01_umwelt 
GROUP BY  [01_umwelt].utyp,  [01_umwelt].uart

From this you will see that the problem lies with the filter in each DCount, which is incorrectly formed as a result of the second double quote (the Chr(34)) going in the wrong place in your string. There is also an extra comma just before the word INTO.

Corrected version is listed below. This version assumes that control [utyp] contains a number. If it contains a string you will need to have single quotes before and after each reference to that control, as shown in the second version.

sqlText = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
           "Format([CountOfuart]/DCount(" & Chr(34) & "utyp" & Chr(34) & "," & Chr(34) & "01_umwelt" & Chr(34) & "," & Chr(34) & "utyp=" & [utyp] & Chr(34) & ")," & Chr(34) & "Percent" & Chr(34) & ") AS [%UTYP]," & _
           "Format([CountOfuart]/DCount(" & Chr(34) & "uart" & Chr(34) & "," & Chr(34) & "01_umwelt" & Chr(34) & "," & Chr(34) & "uart=" & [utyp] & Chr(34) & ")," & Chr(34) & "Percent" & Chr(34) & ") AS [RT] " & _
           "INTO UTYP_UART FROM 01_umwelt GROUP BY  [01_umwelt].utyp,  [01_umwelt].uart "

sqlText = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
           "Format([CountOfuart]/DCount(" & Chr(34) & "utyp" & Chr(34) & "," & Chr(34) & "01_umwelt" & Chr(34) & "," & Chr(34) & "utyp='" & [utyp] & "'" & Chr(34) & ")," & Chr(34) & "Percent" & Chr(34) & ") AS [%UTYP]," & _
           "Format([CountOfuart]/DCount(" & Chr(34) & "uart" & Chr(34) & "," & Chr(34) & "01_umwelt" & Chr(34) & "," & Chr(34) & "uart='" & [utyp] & "'" & Chr(34) & ")," & Chr(34) & "Percent" & Chr(34) & ") AS [RT] " & _
           "INTO UTYP_UART FROM 01_umwelt GROUP BY  [01_umwelt].utyp,  [01_umwelt].uart "

Output in immediate window for value of sqltext set by second (text value of [utyp]) version (with text 'utyp' in place of control contents):

SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart,
Format([CountOfuart]/DCount("utyp","01_umwelt","utyp='utyp'"),"Percent") AS [%UTYP],
Format([CountOfuart]/DCount("uart","01_umwelt","uart='utyp'"),"Percent") AS [RT]
INTO UTYP_UART FROM 01_umwelt GROUP BY  [01_umwelt].utyp,  [01_umwelt].uart

You must by now be able to appreciate the difficulties that incorporating the DCounts into your SQL string in this way causes in setting up correctly-formed VB strings...

-Stewart

[Stewart Ross]

By the way, a slightly more elegant way to do all this is to use a constant in place of the Chr(34)'s like this:

Const X = """"
sqlText = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
           "Format([CountOfuart]/DCount(" & X & "utyp" & X & "," & X & "01_umwelt" & X & "," & X & "utyp='" & "utyp" & "'" & X & ")," & X & "Percent" & X & ") AS [%UTYP]," & _
           "Format([CountOfuart]/DCount(" & X & "uart" & X & "," & X & "01_umwelt" & X & "," & X & "uart='" & "utyp" & "'" & X & ")," & X & "Percent" & X & ") AS [%UART]," & _
           "INTO UTYP_UART FROM 01_umwelt GROUP BY  [01_umwelt].utyp,  [01_umwelt].uart "

-Stewart

[Stewart Ross]

And my final version which is better, simpler and easier to read is to substitute an uncommon character for the double quotes (an '@' symbol below) and then replace these characters with double quotes in the final string:

sqlText = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
           "Format([CountOfuart]/DCount(@utyp@, @01_umwelt@, @utyp='" & [utyp] & "'@),@Percent@) AS [%UTYP], " & _
           "Format([CountOfuart]/DCount(@uart@, @01_umwelt@, @uart='" & [utyp] & "'@),@Percent@) AS [RT] " & _
           "INTO UTYP_UART FROM 01_umwelt GROUP BY  [01_umwelt].utyp,  [01_umwelt].uart "
sqlText = Replace(sqlText, "@", Chr(34))
Debug.Print sqlText

which results in sqltext containing

SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart,
Format([CountOfuart]/DCount("utyp", "01_umwelt", "utyp='utyp'"),"Percent") AS [%UTYP], 
Format([CountOfuart]/DCount("uart", "01_umwelt", "uart='utyp'"),"Percent") AS [RT] 
INTO UTYP_UART FROM 01_umwelt GROUP BY [01_umwelt].utyp, [01_umwelt].uart

(again with text 'utyp' in place of the control reference).

Plenty of choice how to do all this now...

-Stewart

[NeoPa]

You may find Quotes (') and Double-Quotes (") - Where and When to use them of some help.

Having separate delimiter characters for strings in VBA and SQL is quite useful really.