Using Between Clause in ADO parameter query
Posted on 2006-11-15
I need to output a query to an Excel sheet. This query as several calculated fields and is outputed to an Excel templat, where the data populates specific fields. That part works fine. However, now the client want to be able to specify a date range (based on disposition date) using a start and end date. I've created a dialog form that allows users to enter a start and end dates. However I need to be able to pass this data a parameters to the output query. I'm not entired sure how to pass the parameters with a Between clause to the query. I would appreciate any help.
Dim strSql As String
Dim strPath As String
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim wb As New Excel.Application
Dim prmStartDate As ADODB.Parameter
Dim prmEndDate As ADODB.Parameter
'Instantiate recordset and command objects
Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command
'Sets the cmd CommandType property to utilize queries via the adCmdStoredProc
'and set the connection to the current connection. cmd.CommandText is set to the name
'of the query that will be used to create the recordset
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "qryPSoftExport"
'Creates the actual recordset based on "qryPSoftExport that will be used to populate
'the EVMCR.xls worksheet.
Set rst = cmd.Execute(, Array(Forms!frmDatePSoftDialog!txtStartDate.Value), adCmdStoredProc)
'The following code loads the excel sheet
strPath = "\\datapfsv06\tcs02\TCS Finance & Admin\FPO\Supplier Information\EVMCRs\EVMCRtest.xls"
'Opens the EVMCR worksheet at the location specified by the variable strPath
wb.Visible = True
'Copies the contents of the recordset rs and populates the spreadsheet “EVMCR.xls”
'Saves the newly populated workbook and destroys all object variables
'by setting them equal to “Nothing”
'Kill wb.DefaultFilePath & Application.Path
Kill wb.DefaultFilePath & wb.Application.PathSeparator & "RESUME.XLW"
Set wb = Nothing
Set rst = Nothing
If MsgBox("Do you wish to view the exported results", vbYesNo, "View Exported Results") = vbYes Then
DoCmd.Echo True, ""
MsgBox "Spreadsheet creation has been canceled"
DoCmd.Echo True, ""