I have a web form linked with a fox pro database. When creating the record set I am having a problem when adding a date variable to the statement in the sql where condition.

When i run the sql as below it works:

select fields
from tables
where time.true_date =CTOD('09/01/2005')

but when i create a variable to append to the sql an error is raised when running it. See sql below:

select fields
from tables
where time.true_date =CTOD(var_date)

error is : Microsoft ODBC visual fox pro driver function arguement value, type or count is invalid.

can you tell me wat is the exact syntax to append a date variable to the sql statment above for a fox pro database

... CTOD(" & var_date & ")"

Hi,,

The code does not work... the following error occur:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Visual FoxPro Driver]Function argument value, type, or count is invalid.
/jantest/rep_result.asp, line 32


Here is the code in asp:
--------------------------------------------------------
<%
Dim rs_result__var_branch
rs_result__var_branch = "01"
If (Request.form("sel_branch") <> "") Then
rs_result__var_branch = Request.form("sel_branch")
End If
%>
<%
Dim rs_result__var_company
rs_result__var_company = "01"
If (Request.form("sel_location") <> "") Then
rs_result__var_company = Request.form("sel_location")
End If
Dim rs_result__var_date
rs_result__var_date = "09/01/2005"
If (Request.form("txt_date") <> "") Then
rs_result__var_date = Request.form("txt_date")
End If
%>
<%
Dim rs_result
Dim rs_result_numRows
Set rs_result = Server.CreateObject("ADODB.Recordset")
rs_result.ActiveConnection = MM_ConJanweb_STRING
rs_result.Source = "SELECT emp.employeeid, lastname, firstname, in, out, dept.desc,time.true_date FROM branch, emp, time, dept, company WHERE emp.employeeid = time.employeeid and emp.branch = branch.branch and emp.dept = dept.dept and emp.company = company.company and emp.branch = " + Replace(rs_result__var_branch, "'", "''") + " and emp.company = " + Replace(rs_result__var_company, "'", "''") + " and time.true_date = ctod("+ rs_result__var_date + ") ORDER BY true_date"
rs_result.CursorType = 0
rs_result.CursorLocation = 2

--------------------------------------------------------

The code for time.true_date should be as follows when before executing the sql statement:

and time.true_date = ctod('09/01/2005') -- mm/dd/yyyy

response.write out the SQL string before sending the call to the DB to see if it is what you expect.