Re: Passing a Date parameter to a Parametrized query


I just tested this using the Orders table in the Northwind database, it
definitely works. If this still doesn't enable you to resolve the problem,
please post all of the relevant code, including the declaration and
initialization of any variables used. It might be worth double-checking the
query, too - are you sure the SQL you posted earlier is the exact and
complete SQL for the query in question?

PARAMETERS [dDate] DateTime;
UPDATE Orders SET Orders.OrderDate = [dDate];

Public Sub TestParam()

Dim command As ADODB.command
Set command = New ADODB.command
With command
.CommandText = "qryTest"
.CommandType = adCmdStoredProc
Set .ActiveConnection = CurrentProject.Connection
.Parameters.Append .CreateParameter("[dDate]", adDate, adParamInput,
, Date)
.Execute
End With

End Sub

--
Brendan Reynolds

"tuvi" <tuvi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FFF8146A-5E1A-4DB4-8E24-27A9C5F0978D@xxxxxxxxxxxxxxxx
>I usually just create a Date data type dDate then pass to the query using
>
> cmdObj.Execute Parameters:=dDate
>
> At least the above works for other Data types...
>
> Anyway, I tried both yours and Brendan's way of creating a Parameter
> variable... and still not working....
> The message still saying data type mismatched... in criteria expression
>
>
> "Ken Snell [MVP]" wrote:
>
>> Oopss.. typos:
>>
>> Dim par As ADODB.Parameter
>> cmdObj.CommandText = "qryUpdateDepositTemp"
>> cmdObj.CommandType = adCmdStoredProc
>> cmdObj.ActiveConnection = CurrentProject.Connection
>>
>> Set par = New ADODB.Parameter
>> par.Type = adDBDate
>> par.Size = 8
>> par.Direction = adParamInput
>> par.Value = dDate
>> cmdObj.Parameters.Append par
>>
>> cmdObj.Execute
>> Set par = Nothing
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>> "Ken Snell [MVP]" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
>> news:eYt52jP0FHA.3000@xxxxxxxxxxxxxxxxxxxxxxx
>> > Try this:
>> >
>> > Dim par As ADODB.Parameter
>> > cmdObj.CommandText = "qryUpdateDepositTemp"
>> > cmdObj.CommandType = adCmdStoredProc
>> > cmdObj.ActiveConnection = CurrentProject.Connection
>> >
>> > Set par = New ADODB.Parameter
>> > par.Type = adDateTime
>> > par.Size = 8
>> > par.Direction = adParamInput
>> > par.Value = dDate
>> > cmdObj.Parameters.Append par
>> >
>> > cmdObj.Execute
>> >
>> > --
>> >
>> > Ken Snell
>> > <MS ACCESS MVP>
>> >
>> >
>> > "tuvi" <tuvi@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> > news:579A5487-B813-4F38-BCDB-43FB8CF061D2@xxxxxxxxxxxxxxxx
>> >>I have the following query:
>> >>
>> >> PARAMETERS [dDate] DateTime;
>> >> UPDATE DepositTemp SET DepositTemp.DepositDate = [dDate];
>> >>
>> >> And here is my code:
>> >>
>> >> cmdObj.CommandText = "qryUpdateDepositTemp"
>> >> cmdObj.CommandType = adCmdStoredProc
>> >> cmdObj.ActiveConnection = CurrentProject.Connection
>> >> cmdObj.Execute Parameters:=dDate
>> >>
>> >> It always return error saying that wrong type of data!!!!
>> >>
>> >> I try the same above procedure for updating a different data type such
>> >> as
>> >> a
>> >> Long Number and it works fine.... it just not works with a Date data
>> >> type.
>> >>
>> >> Please help...
>> >>
>> >> I have Access 2003
>> >
>> >
>>
>>
>>


.

Relevant Pages