Call report based on UDF with parameters

[masteraccess2008]

I create in Access report based on user defined function(UDF) in SQL Server which returns table.

CREATE FUNCTION MyFunc_VP (@VP varchar(12))
RETURNS TABLE
AS
RETURN (SELECT......

In Access, I am calling a report with DoCmd.OpenRepor t method.

DOCMD.OpenRepor t stDocName, acPreview

How to call report passing a parameter from form (Forms!FormName !TextBox)
(solution with where clause doesn't match my problem).

[JConsulting]

have you tried using the report's filter option?

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

wherecondition = A string expression that's a valid SQL WHERE clause without the word WHERE.

DOCMD.OpenReport stDocName, acPreview,,"[somefield] = " & Forms!FormName!TextBox

[masteraccess2008]

have you tried using the report's filter option?

DoCmd.OpenRepor t reportname[, view][, filtername][, wherecondition]

wherecondition = A string expression that's a valid SQL WHERE clause without the word WHERE.



DOCMD.OpenRepor t stDocName, acPreview,,"[somefield] = " & Forms!FormName! TextBox

Thank you, but..
in Access Report i use user defined function(UDF) instead of query. Parameters in UDF has no relation with where part of SQL statement, so your solution doens't work for me. Is the any other way to pass parameters calling report?

[NeoPa]

Can you show what you are using.
I can't understand why the Filter would not work, even for a T-SQL UDF.

You have tried it haven't you?

[masteraccess2008]

Can you show what you are using.
I can't understand why the Filter would not work, even for a T-SQL UDF.

You have tried it haven't you?

My steps were as follows:
1. Create in SQL Server 2000 UDF:
CREATE FUNCTION TEMP_VP (@VP varchar(12))
RETURNS TABLE
AS
RETURN.......
2. In Access open new project using existing data
3. Create Report named "MyReport" based on function TEMP_VP
When i Start this report program ask me: Enter Parameter Value / VP
4. Create new form with text field "FormField" and call report from this form:
Dim stDocName As String
Dim WhereC As String
stDocName = "MyReport"
WhereC = "[VP] = " + Form!FormField
DoCmd.OpenRepor t stDocName, acPreview, , WhereC
5. When i call report, program still ask me Enter Parameter Value/VP and reports "Invalid Column Name VP".

How to solve this problem?

[NeoPa]

When you run TEMP_VP as a simple Access query, what columns (exactly) are returned?

What is some example data for [VP]?

[masteraccess2008]

When you run TEMP_VP as a simple Access query, what columns (exactly) are returned?

What is some example data for [VP]?

TEMP_VP is really very simple query:

CREATE FUNCTION TEMP_VP (@VP varchar(12))
RETURNS TABLE
AS
RETURN (SELECT MAX(KODA_VP) AS KODA_vp,MIN(PRICE) AS MINp,MAx(PRICE) AS Maxp FROM SJ_PRICES WHERE KODA_VP=@vp)

example of data for VP: "GOOG"

This function is prepared only for this forum, in real solution function is much more complex..

[NeoPa]

I'd really much rather you answered my question than giving me some SQL to look through - especially T-SQL.

However, from looking at your code I can see two major problems with your test of the filter (WhereC = "[VP] = " + Form!FormField) :

1. [VP] is not a field that is returned in your function (SQL).
   You have fields called [KODA_vp], [MINp] & [Maxp] only.
2. [VP] is a text field so the filter would need to include single quotes around the value.
   

   WhereC = "[VP] = '" + Form!FormField & "'"

I would suggest a filter is still the best option for you. I'd be happy to help further if you have any questions.

[masteraccess2008]

I'd really much rather you answered my question than giving me some SQL to look through - especially T-SQL.

However, from looking at your code I can see two major problems with your test of the filter (WhereC = "[VP] = " + Form!FormField) :

1. [VP] is not a field that is returned in your function (SQL).
   You have fields called [KODA_vp], [MINp] & [Maxp] only.
2. [VP] is a text field so the filter would need to include single quotes around the value.
   

   WhereC = "[VP] = '" + Form!FormField & "'"

I would suggest a filter is still the best option for you. I'd be happy to help further if you have any questions.

Answers to your questions:
Query returns: KRKG 6.21 126.58
Value of Parameter: 'GOOG'

I ran code just like you suggest:

 Dim WhereC As String
    stDocName = "MyReport"
    WhereC = "[VP] = '" + Form!FormField & "'"
    DoCmd.OpenReport stDocName, acPreview, WhereC

When I run this code (Form!FormField is of course filled in) program stil ask me "Enter Parameter Value".
I'm very unhappy about this.

[NeoPa]

You have fixed the problem numbered #2 in my post but #1 is still an issue for you. You are trying to select on a field ([VP]) which doesn't exist as far as I can tell.

When you run TEMP_VP as a simple Access query, what columns (exactly) are returned?

What is some example data for [VP]?

This is a quote of my post (#6) where I asked the questions. I am actually interested in the column names rather than any example data in them.

The second question you've already answered quite adequately.
The first I think I know from your posted SQL but it would be interesting to have this confirmed.