Stuck with a query in VB

[richwi]

"SELECT Order.[Order ID], Sum(TurkeyUsed.Cost) AS SumOfCost " _
& "INTO Ordercosttemp " _
& "FROM TurkeyUsed, [Order] " _
& "GROUP BY Order.[Order ID] " _
& "HAVING (((Order.[Order ID])=" & temporderidvar & "));

I'm want to run this query using VB in Access....can anyone show me the exact code of how to do this so the query executes successfully...sorry but i haven't got a clue with VB.

Cheers

 

[ecrespol]

I'm not an expert on VB, but I think that the following will work...

Set dbs = CurrentDb
dbs.Execute "Your SQL"
dbs.Close

 

[modest]

Dim strSQL As String

strSQL = "Your SQL Statement"

Docmd.RunSQL strSQL

 

[mdcory]

A just in case comment, you also need a qoutation mark at teh end of that statement after the ;

 

[modest]

i hope that wasn't the mistake

 

[WayneRyan]

Rich,

Your SQL statement is a SELECT statement. VBA will have no idea where
to put the results. Select statements can provide data to listboxes,
combo boxes, forms, forms, etc.

But NOT to the DoCmd.RunSQL. It is for action queries only, such as
"Delete From ...", "Insert Into ...", etc.

Wayne

 

[modest]

Your SQL statement is a SELECT statement. VBA will have no idea where to put the results. Select statements can provide data to listboxes, combo boxes, forms, forms, etc.

Wayne, it is not a SELECT statement.

But NOT to the DoCmd.RunSQL. It is for action queries only, such as
"Delete From ...", "Insert Into ...", etc.

Docmd.RunSQL definitely can handle his query. SELECT INTO is a create table query. It performs as follows:

SELECT <field1>,<field2>,etc INTO <TableName You Want To Create>
FROM <TableName that the fields belong to>

 

[WayneRyan]

Modest,

Good catch!

I read it too quickly. All I saw was the Select.

I wonder if Rich's problem is solved.

Wayne

 

[modest]

When I first looked at it, I thought the same thing It's really not a big deal, if one way doesn't work, I normally try another

Cheers