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("utyp","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
8  4323 
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
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
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
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
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
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
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: newbie_mw |
last post by:
Seems my post was buried in more cries for help :-) I will try again.
It's probably a very novice question so please take a look! Thanks!...
|
by: Phil Powell |
last post by:
Here is the function ArraySearch:
'--------------------------------------------------------------------------------------
'ArraySearch will return an integer value indicating the first...
|
by: Najm Hashmi |
last post by:
Hi all ,
I am trying to create a store procedure and I get the following error:
SQL0104N An unexpected token "END-OF-STATEMENT" was found
following "END". Expected tokens may include: "JOIN...
|
by: NeilH |
last post by:
Hello All
I was wondering if someone could offer a rather inexperienced person
some advice.
Im trying to get my asp page to look at an access data
I created the following query in access...
|
by: Aaron G via AccessMonster.com |
last post by:
Wanted to share a solution to something which I didn't find on the net:
EVERY form in my Microsoft Access 2002 database gave an error any time any
code was to be called: form OnOpen, button...
| | |
by: Tran Hong Quang |
last post by:
Hi,
I'm using Windows 2000, English version, Microsoft Visual Studio.Net 2003.
I compile a project developed for Japanese client, on which there are some
Japanese messages. I have error "End of...
|
by: naughtybynature |
last post by:
<html>
<head>
<title>Search Questions</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?php
$query = '';
|
by: erictheone |
last post by:
Ok so what I'm trying to do is create a trans location cipher. For those among us that don't know alot about cryptography it is a method for jumbling up letters to disguise linguistic...
|
by: jahanas |
last post by:
Private Sub txtBox1.txtLife_Enter(ByVal frmQuote) Handles txtLife
Me.txtQuote.Text = "I like life, it's something to do."
End Sub
Private Sub txtBox2.txtFuture_Enter(ByVal...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |
