VBA SQL String variable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

djf@uk.com - 07 Jul 2007 23:51 GMT

Hi
I need some assistance with the syntax so that I can use the following SQL
in a String variable say” mycount” the returned data would be a number
SELECT RED.GreenID, Max(RED.LV_SV_Total) AS MaxOfLV_SV_Total
FROM (Black INNER JOIN Green ON Black.BlackID=Green.BlackId) INNER JOIN RED
ON Green.GreenId=RED.GreenID
GROUP BY RED.GreenID;

Reply to this Message

Chris2 - 08 Jul 2007 07:02 GMT

> Hi
> I need some assistance with the syntax so that I can use the following SQL
[quoted text clipped - 3 lines]
> ON Green.GreenId=RED.GreenID
> GROUP BY RED.GreenID;

djf,

Here's the query above, straightened up a bit.

SELECT RED.GreenID
,Max(RED.LV_SV_Total) AS MaxOfLV_SV_Total
FROM (Black
INNER JOIN
Green
ON Black.BlackID = Green.BlackId)
INNER JOIN RED
ON Green.GreenId = RED.GreenID
GROUP BY RED.GreenID;

Public Sub SQLStringAssemble()

Dim strSQL

strSQL = "SELECT RED.GreenID "
strSQL = strSQL & ",Max(RED.LV_SV_Total) AS MaxOfLV_SV_Total "
strSQL = strSQL & "FROM (Black INNER JOIN Green "
strSQL = strSQL & "ON Black.BlackID = Green.BlackId) "
strSQL = strSQL & "INNER JOIN RED "
strSQL = strSQL & "ON Green.GreenId = RED.GreenID "
strSQL = strSQL & "GROUP BY RED.GreenID; "

Debug.Print strSQL

End Sub

When I run this and then copy and paste the SQL into a query, it saves successfully.

Sincerely,

Chris O.

Reply to this Message

djf@uk.com - 08 Jul 2007 16:14 GMT

Hi
Chris O
Thank you for taking the time to reply.However when I test the result of
your idea via a message box.
I just see all the code instead of the final result from the SQL statement.

Reply to this Message

Chris2 - 08 Jul 2007 17:34 GMT

> Hi
> Chris O
> Thank you for taking the time to reply.However when I test the result of
> your idea via a message box.
> I just see all the code instead of the final result from the SQL statement.

djf,

If you want to use the SQL in the string variable to create a recordset, then you may use
the following as an example that you may adapt using your SQL.

Public Sub QuickTest()

Dim db As DAO.Database ' Create DAO database variable.
Dim rs As DAO.Recordset ' Create DAO recordset variable.
Dim strSQL As String ' Create string variable.

' Load string variable with SQL code.
strSQL = "SELECT Y1.Column1 FROM YourTable1 AS Y1"

'Give DAO database variable a reference to the current database.
Set db = CurrentDb()

'Open up a recordset based on the SQL and hand a reference to the
'DAO recordset variable.
Set rs = db.OpenRecordset(strSQL)

'Set up the recordset object to have a new record inserted.
rs.AddNew
'Put a value in a column of the recordset.
rs.Fields("Column1").Value = "Whatever"
'Order the insert to be committed.
rs.Update
'Do it all over again to insert a second row.
rs.AddNew
rs.Fields("Column1").Value = "Whatever1"
rs.Update

'Close the recordset.
rs.Close
'Destroy the DAO recordset variable.
Set rs = Nothing
'Close the database.
db.Close
'Destroy the DAO database variable.
Set db = Nothing

End Sub

If inserting new records is not your goal, you use the MoveFirst, MoveNext, MoveLast,
MovePrevious (where available), Edit, and EOF methods of the DAO recordset object to move
through it in order to read and update individual rows.

Sincerely,

Chris O.

Reply to this Message