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  4354 
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!
-----------------------------------------------------------------
I created a sign-up sheet (reg.html) where people fill in their first
name, last name, email, etc. The data are then sent to a PHP script
(reg.php). The data are then inserted into a table (reg) in MS SQL
server. I have declared the...
|
by: Phil Powell |
last post by:
Here is the function ArraySearch:
'--------------------------------------------------------------------------------------
'ArraySearch will return an integer value indicating the first
occurrence of a string
'within an array. for now it only does a case-sensitive
first-occurrence search.
'
'Created by Phil Powell on 6/28/2002
'--------------------------------------------------------------------------------------
|
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
<joined_table>
Explanation:
A syntax error in the SQL statement was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
|
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 and it worked running it in
access.
However when i paste it into the asp page i get an "Expected end of
|
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 OnClick, etc.
Error was: <b>The expression On Open you entered as the event property
setting produced the following error: Expecteed: end of statement.</b>
The problem ultimately was the letter "s" had gotten at the beginning of my
module code so...
| | |
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 statement expected." at the line of
Japanese message.
How to make Visual Studio.Net 2003 understand that Japanese message?
Thanks
Tran Hong Quang
|
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 patterns(words). What it does is takes a string as a parameter, determines length of string, tests if the length is a perfect square, if it is then it makes a 2-d array with its length and height equal to the lengths root. If it isn't then it cuts it down to...
|
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 frmQuote) Handles txtFuture
Me.txtQuote.Text = "The future isn't what it used to be."
End Sub
Private Sub txtBox3.txtTruth_Enter(ByVal frmQuote) Handles txtTruth
Me.txtQuote.Text = "Tell the truth and run."
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
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 we have to send another system
| | |
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 can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
