passing a value to a sql statement

[ByzantianX]

I have a problem with passing a variable as the parameter in sql statement which is the row source of the chart in the report.If I use stored procedure it can't recognize the variable. If I use the sql statement I'm getting a message that ADO can not recognize a function (that I bound to the global variable). What should I do?

 

[Amiel]

Show us the code...

Amiel
amielzz@netscape.net

 

[ByzantianX]

a code in module looks like this:
global dtedepart as date
public function fndepart()
fndepart=dtedepart
end function

a code in form looks like this:
private sub cboDepart_AfterUpdate
dtedepart=me.cboDepart
end sub

row source property of chart(microsoft graph2000) in report looks like this:
SELECT Name, SUM(Costs) AS TotalCosts, COUNT(Destination) AS TotalTravels FROM ViewTravels WHERE (depart >= fndepart()) GROUP BY Name

If I try to bind the chart row to the stored procedure, I always get a prompt for @depart variable although I tried several ways to pass it (declaring the ado parameter and binding the value of depart variable to the value property of the ado parameter, or just passing to the input parameters property of the report).

I made this application in local and everything worked just fine with basically the same SELECT statement as the row source for the chart. Thanks in advance

 

[Amiel]

Try this :

"SELECT Name, SUM(Costs) AS TotalCosts, COUNT(Destination) AS TotalTravels FROM ViewTravels WHERE (depart >=[red] #" & fndepart() & "#[/red]) GROUP BY Name"

... if you are using SQL server, loose the pound signs which are needed by the JET Engine to distinguish DATE data types (ahh, you were aware that the global was a date...), and use:

WHERE (depart >= '" & fndepart() & "') GROUP BY Name"

... you needed to embed the value returned by the function fndepart() which is resolved prior to passing the string to ADO and your data source.

The query that you provided passes the literal string "fndepart()" instead.


Amiel
amielzz@netscape.net