Thursday, March 22, 2012

((cdate("1/1/2001")+30) as task_due_date (Not working)

I have the below function written in VB, and everything works fine
EXCEPT that the sql code is not executing correctly. The date field is
in the table always shows "12/1/1899 11:59:17 PM" no other date. I'm
should have the date of the input into the function + or - the integer
in the task_due_days field.

for example (cdate("1/1/2002")+30) as task_due_date

What am I doing wrong?

Function SetTasks(trans_id As Long, trans_type As Integer, event_date
As Date)

Dim task As String

task = "Insert into tbl_tasks
(trans_id,task_name,Task_due_date,comments) SELECT (" & trans_id & ")
as trans_id,task_name,(cdate(" & event_date & ")+[task_due_days]) as
task_due_date,comments FROM tbl_task_parameter WHERE trans_type=" &
trans_type

Debug.Print task

DoCmd.RunSQL (task)

End Function

the actual SQL code is....

Insert into tbl_tasks (trans_id,task_name,Task_due_date,comments)
SELECT (192) as trans_id,task_name,(cdate(1/1/2001)+[task_due_days]) as
task_due_date,comments FROM tbl_task_parameter WHERE trans_type=1

ANY HELP IS GREATLY APPRECIATED!(stoppal@.hotmail.com) writes:
> the actual SQL code is....
> Insert into tbl_tasks (trans_id,task_name,Task_due_date,comments)
> SELECT (192) as trans_id,task_name,(cdate(1/1/2001)+[task_due_days]) as
> task_due_date,comments FROM tbl_task_parameter WHERE trans_type=1

Apparently you are not using SQL Server, as there is no cdate function
in SQL Serever.

I can tell what the problem is though: 1/1/2001 = 0 with integer division,
and with floating-point division you get 0.0005. Since you got
11:59:17, I guess that in whatever you are using, you have floating-
point division. (In SQL Server you would get integer division here.)

So you need to delimit the date string. In SQL Server that would be
'1/1/2001'. But it looks a bit likely you are using Access, in which
case maybe ## is better. But you better ask in comp.databases.ms-access
it you are uncertain.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

No comments:

Post a Comment