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

"end of statement expected error in query"

atksamy
P: 91
HI,

I am using the following query in vba and i get a compile error end of statement expected.
Expand|Select|Wrap|Line Numbers
  1. sqltext = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
  2.              "Format([CountOfuart]/DCount("utyp","01_umwelt","utyp=" & [utyp]),"Percent") AS [%UTYP]," & _
  3.              "Format([CountOfuart]/DCount("uart","01_umwelt","uart=" & [uart]),"Percent") AS [%UART]" & _
  4.              "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
Dec 17 '08 #1
Share this Question
Share on Google+
8 Replies


Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. "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:

Expand|Select|Wrap|Line Numbers
  1. "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
Dec 17 '08 #2

atksamy
P: 91
Well i tried as you said and got something like

Expand|Select|Wrap|Line Numbers
  1. sqltext = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
  2.            "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]," & _
  3.            "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]," & _
  4.            "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
Dec 17 '08 #3

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. 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
Dec 17 '08 #4

atksamy
P: 91
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
Dec 17 '08 #5

Expert Mod 2.5K+
P: 2,545
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):

Expand|Select|Wrap|Line Numbers
  1. SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart,
  2. Format([CountOfuart]/DCount("utyp","01_umwelt","utyp="utyp),"Percent") AS [%UTYP],
  3. Format([CountOfuart]/DCount("uart","01_umwelt","uart="utyp),"Percent") AS [RT], 
  4. INTO UTYP_UART FROM 01_umwelt 
  5. 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.

Expand|Select|Wrap|Line Numbers
  1. sqlText = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
  2.            "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]," & _
  3.            "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] " & _
  4.            "INTO UTYP_UART FROM 01_umwelt GROUP BY  [01_umwelt].utyp,  [01_umwelt].uart "
Expand|Select|Wrap|Line Numbers
  1. sqlText = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
  2.            "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]," & _
  3.            "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] " & _
  4.            "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):

Expand|Select|Wrap|Line Numbers
  1. SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart,
  2. Format([CountOfuart]/DCount("utyp","01_umwelt","utyp='utyp'"),"Percent") AS [%UTYP],
  3. Format([CountOfuart]/DCount("uart","01_umwelt","uart='utyp'"),"Percent") AS [RT]
  4. 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
Dec 17 '08 #6

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Const X = """"
  2. sqlText = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
  3.            "Format([CountOfuart]/DCount(" & X & "utyp" & X & "," & X & "01_umwelt" & X & "," & X & "utyp='" & "utyp" & "'" & X & ")," & X & "Percent" & X & ") AS [%UTYP]," & _
  4.            "Format([CountOfuart]/DCount(" & X & "uart" & X & "," & X & "01_umwelt" & X & "," & X & "uart='" & "utyp" & "'" & X & ")," & X & "Percent" & X & ") AS [%UART]," & _
  5.            "INTO UTYP_UART FROM 01_umwelt GROUP BY  [01_umwelt].utyp,  [01_umwelt].uart "
-Stewart
Dec 17 '08 #7

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. sqlText = "SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart," & _
  2.            "Format([CountOfuart]/DCount(@utyp@, @01_umwelt@, @utyp='" & [utyp] & "'@),@Percent@) AS [%UTYP], " & _
  3.            "Format([CountOfuart]/DCount(@uart@, @01_umwelt@, @uart='" & [utyp] & "'@),@Percent@) AS [RT] " & _
  4.            "INTO UTYP_UART FROM 01_umwelt GROUP BY  [01_umwelt].utyp,  [01_umwelt].uart "
  5. sqlText = Replace(sqlText, "@", Chr(34))
  6. Debug.Print sqlText
which results in sqltext containing

Expand|Select|Wrap|Line Numbers
  1. SELECT [01_umwelt].utyp, [01_umwelt].uart, COUNT([01_umwelt].uart) AS CountOfuart,
  2. Format([CountOfuart]/DCount("utyp", "01_umwelt", "utyp='utyp'"),"Percent") AS [%UTYP], 
  3. Format([CountOfuart]/DCount("uart", "01_umwelt", "uart='utyp'"),"Percent") AS [RT] 
  4. 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
Dec 17 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 23 '08 #9

Post your reply

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