jbuck
May 20th, 2000, 07:21 AM
Hi Everyone,
I am trying to pass 2 dates to a parameter query using a query developed in Access 97. I have no problem with passing one parameter but I can't figure out how to pass multiple ones. I'm wanting to pass a start date and end date to the query and have it return me a recordset that I can loop through and add up some costs. The query works fine if you run it from Access. The following code works fine with one parameter. Help I a drift in a sea of information!
Thanks for any help!
Jim
Dim cmdTenAvg As ADODB.Command
Dim prmTenAvg As ADODB.Parameter
Dim rstTenAvg As ADODB.Recordset
Dim LastDate As Date
On Error GoTo Proc_Err
' Build The Access Commmand
Set cmdTenAvg = New ADODB.Command
With cmdTenAvg
.ActiveConnection = conn
.CommandText = "qryCostGood_Prm2"
.CommandType = adCmdTable
End With
' Look For Date 15 Days ago
LastDate = Today - 15
' Build The Parameter
Set prmTenAvg = New ADODB.Parameter
With prmTenAvg
.Name = "Date"
.Value = Today
.Type = adDate
.Direction = adParamInput
'This Attempt is a Failure!!!
.Name = "Date"
.Value = LastDate
.Type = adDate
.Direction = adParamInput
End With
' Append The Parameter
cmdTenAvg.Parameters.Append prmTenAvg
' Execute It
Set rstTenAvg = cmdTenAvg.Execute
'
' Loop Through The All dates Until You Get An Ten Day Average.
'
Do Until rstTenAvg.EOF I Only Get One Record!
Debug.Print rstTenAvg!Date
Debug.Print rstTenAvg!Cost
rstTenAvg.MoveNext
Loop
CalcLVLTenDayAvg = True
Proc_Exit:
Exit Function
'
Proc_Err:
CalcLVLTenDayAvg = False
Err.Raise Err.Number, "DLL", Err.Description
' Resume Proc_Exit
I am trying to pass 2 dates to a parameter query using a query developed in Access 97. I have no problem with passing one parameter but I can't figure out how to pass multiple ones. I'm wanting to pass a start date and end date to the query and have it return me a recordset that I can loop through and add up some costs. The query works fine if you run it from Access. The following code works fine with one parameter. Help I a drift in a sea of information!
Thanks for any help!
Jim
Dim cmdTenAvg As ADODB.Command
Dim prmTenAvg As ADODB.Parameter
Dim rstTenAvg As ADODB.Recordset
Dim LastDate As Date
On Error GoTo Proc_Err
' Build The Access Commmand
Set cmdTenAvg = New ADODB.Command
With cmdTenAvg
.ActiveConnection = conn
.CommandText = "qryCostGood_Prm2"
.CommandType = adCmdTable
End With
' Look For Date 15 Days ago
LastDate = Today - 15
' Build The Parameter
Set prmTenAvg = New ADODB.Parameter
With prmTenAvg
.Name = "Date"
.Value = Today
.Type = adDate
.Direction = adParamInput
'This Attempt is a Failure!!!
.Name = "Date"
.Value = LastDate
.Type = adDate
.Direction = adParamInput
End With
' Append The Parameter
cmdTenAvg.Parameters.Append prmTenAvg
' Execute It
Set rstTenAvg = cmdTenAvg.Execute
'
' Loop Through The All dates Until You Get An Ten Day Average.
'
Do Until rstTenAvg.EOF I Only Get One Record!
Debug.Print rstTenAvg!Date
Debug.Print rstTenAvg!Cost
rstTenAvg.MoveNext
Loop
CalcLVLTenDayAvg = True
Proc_Exit:
Exit Function
'
Proc_Err:
CalcLVLTenDayAvg = False
Err.Raise Err.Number, "DLL", Err.Description
' Resume Proc_Exit