Group By SQL statement

[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

 

[Dirk Goldgar]

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

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"

 

[Guest]

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.

 

[Dirk Goldgar]

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.

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.