-
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
-
Could it be because your two parameters have the same name?
I'll give you an example with datacontrol, may be it will help you or give you some ideas:
Code:
Dim str As String
Dim qdf As QueryDef
str = "PARAMETERS [Beginning OrderDate] DateTime, " _
& "[Ending OrderDate] DateTime; SELECT * FROM Orders " & _
"WHERE (OrderDate Between[Beginning OrderDate] " _
& "And [Ending OrderDate]);"
Set qdf = Data1.Database.CreateQueryDef("", str)
qdf.Parameters![Beginning OrderDate] = CDate(Text1)
qdf.Parameters![Ending OrderDate] = CDate(Text2)
Set Data1.Recordset = qdf.OpenRecordset
Data1.Recordset.MoveLast
Data1.Recordset.Close
