Group By SQL statement

  • Thread starter Thread starter Lee Roper
  • Start date Start date
L

Lee Roper

I'm trying to create a SQL statement in my VBA code that
will select a field called Claimno from my Data table and
group all the records by the Claimno field. It works fine
as a query I build in Design View, but when I build it as
a SQL statement, it tells me there is an error in the FROM
clause. Here's the code I wrote:

Dim claimnoSQL as string
claimnoSQL = "SELECT Claimno FROM Data" & _
"GROUP BY Claimno"

I've tried several different variations, but I can't get
it to work. Can someone tell me what I'm doing wrong?
Thanks,
Lee
 
Lee Roper said:
I'm trying to create a SQL statement in my VBA code that
will select a field called Claimno from my Data table and
group all the records by the Claimno field. It works fine
as a query I build in Design View, but when I build it as
a SQL statement, it tells me there is an error in the FROM
clause. Here's the code I wrote:

Dim claimnoSQL as string
claimnoSQL = "SELECT Claimno FROM Data" & _
"GROUP BY Claimno"

I've tried several different variations, but I can't get
it to work. Can someone tell me what I'm doing wrong?
Thanks,
Lee
Click to expand...

If you look at the content of claimnoSQL after you've built it, you'll
see that it looks like this:

SELECT Claimno FROM DataGROUP BY Claimno

There's no space between "Data" and "GROUP BY". Change your assignment
to something like this:

claimnoSQL = _
"SELECT Claimno FROM Data " & _
"GROUP BY Claimno"
 
Thanks. That took care of the problem regarding the FROM
clause, but now it's giving me a new error. It's
saying "A RunSQL action requires an argument consisting of
an SQL statement." I don't understand why it is not
recognizing this code as a SQL statement.
 
Thanks. That took care of the problem regarding the FROM
clause, but now it's giving me a new error. It's
saying "A RunSQL action requires an argument consisting of
an SQL statement." I don't understand why it is not
recognizing this code as a SQL statement.
Click to expand...

That message is rather misleading. What it really means is that the
RunSQL action (or method) requires that the SQL statement be an action
query -- for example, an update, append, or make-table query -- not a
SELECT statetment.

How are you trying to use this SQL statement you've built? If you want
to open it as a datasheet, you'll have to save it as a stored query
first. If you want to extract data from it to be used in code, you'll
have to open a recpordset on it.
 
Back
Top