Passing a Date parameter to a Parametrized query

G

Guest

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
 
B

Brendan Reynolds

You haven't shown the code where you declare and initialize the Parameter
object, dDate. It should look something like this ...

Dim dDate As ADODB.Parameter
Set dDate = cmdObj.CreateParameter("[dDate]", adDBDate, adParamInput, ,
Date)
 
K

Ken Snell [MVP]

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
 
K

Ken Snell [MVP]

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Brendan Reynolds

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 said:
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 said:
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 said:
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>


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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

woa... I just put the code into a new form.. and it works... even my original
codes work too... That's very strange...

Maybe I reuse the lines:

cmdObj.CommandText = "differentquery"
cmdObj.CommandType = adCmdStoredProc
cmdObj.ActiveConnection = CurrentProject.Connection
cmdObj.Execute Parameters:=differentParameter

in the same subroutine??

Brendan Reynolds said:
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 said:
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 said:
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>

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>


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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pass parameter to sp 1
SQL query giving me an error 1
Requery a parameter query 1
Parameter Query - Passing multiple parameters 2
Parametrized value does not always refresh query 1
Passing parameter to Update Query 6
Problem writing parametrized query 2
dynamic query using parameter function 2

Top