MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Insert a variable into SQL code within a macro


Posted by Belinda on July 11, 2000 2:20 AM

Hi,
I'm trying to use a variable in code that redefines the parameters of an ODBC query run from within Excel. The SQL code, however, does not recognise a named variable (which I obtain a value for by using the InputBox function).

Any suggestions?

Belinda


Posted by Susan on July 11, 0100 2:27 AM

Can you be any more Vague.

Posted by on July 11, 0100 2:46 AM

Perhaps this example may help.

Sub CreateParamQuery()
Dim db As DAO.Database, rs As DAO.Recordset, QD As DAO.QueryDef
Dim EmpLastName As String, OrderDate As Date
Dim OrderID As Long, SQL As String
Dim ws As DAO.WorkSpace

Set ws = DBEngine(0)
Set db = ws.OpenDatabase("c:\Office97\Office\Samples\Northwind.MDB")

'Prompt for the employee's last name, OrderID, and Order Date


EmpLastName = InputBox("Enter the Employee's Last Name:")
OrderID = CLng(InputBox("Enter the OrderID:"))
OrderDate = CDate(InputBox("Enter the Order Date: "))

'Build the SQL statement with Parameters
SQL = "PARAMETERS [LName] Text, [OrdID] Long, [OrdDate] " _

& "Date;SELECT * FROM Employees INNER JOIN Orders ON " _

& "Employees.EmployeeID = Orders.EmployeeID WHERE " _
& "Employees.LastName = [LName] AND Orders.OrderID" _

& ">= [OrdID] AND Orders.OrderDate>=[OrdDate];"


'Create a temporary QueryDef object
Set QD = db.CreateQueryDef("", SQL)

'Set the parameter objects in the QueryDef
QD.Parameters!LName = EmpLastName
QD.Parameters!OrdID = OrderID
QD.Parameters!OrdDate = OrderDate
'Open the RecordSet
Set rs = QD.OpenRecordSet()
Debug.Print "LastName OrderID OrderDate"
Debug.Print "================================"
Do Until rs.EOF

Debug.Print rs!LastName & " " & rs!OrderID & " " & _

rs!OrderDate

rs.MoveNext
Loop
rs.Close
db.Close
End Sub