Access Text of Parameter

I have a report that uses parameters from a query. The query returns two
columns. One for the paramerter value and one for the parameter title. I want
the header of the report to access the same fild that I have set as the Label
Field in my report parameter dropdown.
I can add the actual parameter to the header using:
=Parameters!Property.Value
But I don't want the actual parameter, i want the alias from the second
colum of the parameter query.
I tried
=Parameters!Property.Text
but it didn't work.
Any ideas?
Thanks!try =Parameters!Property.Label
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Langland [MSFT]" <EricLanglandMSFT@.discussions.microsoft.com> wrote in
message news:C60A3AB7-7C07-418B-B2F7-ECA395614511@.microsoft.com...
>I have a report that uses parameters from a query. The query returns two
> columns. One for the paramerter value and one for the parameter title. I
> want
> the header of the report to access the same fild that I have set as the
> Label
> Field in my report parameter dropdown.
> I can add the actual parameter to the header using:
> =Parameters!Property.Value
> But I don't want the actual parameter, i want the alias from the second
> colum of the parameter query.
> I tried
> =Parameters!Property.Text
> but it didn't work.
> Any ideas?
> Thanks!|||That did it.
Many thanks Lev!
"Lev Semenets [MSFT]" wrote:
> try =Parameters!Property.Label
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Eric Langland [MSFT]" <EricLanglandMSFT@.discussions.microsoft.com> wrote in
> message news:C60A3AB7-7C07-418B-B2F7-ECA395614511@.microsoft.com...
> >I have a report that uses parameters from a query. The query returns two
> > columns. One for the paramerter value and one for the parameter title. I
> > want
> > the header of the report to access the same fild that I have set as the
> > Label
> > Field in my report parameter dropdown.
> >
> > I can add the actual parameter to the header using:
> >
> > =Parameters!Property.Value
> >
> > But I don't want the actual parameter, i want the alias from the second
> > colum of the parameter query.
> >
> > I tried
> > =Parameters!Property.Text
> > but it didn't work.
> >
> > Any ideas?
> >
> > Thanks!
>
>

Access Reports via URL

Hi,
I figured, that I can call reports directly via URL and pass the
appropriate parameters in the URL when adding '&mypara=value'.
So far so good. But I could not figure out so far, how I can pass
parameters that allow "multiselections". Using the above notation
doesn't seem to work ... :(
--
Henning Eiben
busitec GmbH
Consultant
e-mail: eiben@.busitec.de
+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax
Rudolf-Diesel-Straße 59
48157 Münster
www.busitec.de
Sitz der Gesellschaft: Münster
HR B 55 75 - Amtsgericht Münster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann
--
... There are 10 kinds of people. Those who know binary code, and those
who don't.On Aug 24, 2:05 am, Henning Eiben <ei...@.busitec.de> wrote:
> Hi,
> I figured, that I can call reports directly via URL and pass the
> appropriate parameters in the URL when adding '&mypara=3Dvalue'.
> So far so good. But I could not figure out so far, how I can pass
> parameters that allow "multiselections". Using the above notation
> doesn't seem to work ... :(
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: ei...@.busitec.de
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Stra=DFe 59
> 48157 M=FCnsterwww.busitec.de
> Sitz der Gesellschaft: M=FCnster
> HR B 55 75 - Amtsgericht M=FCnster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Gesch=E4ftsf=FChrer: Simon B=F6wer, Henning Eiben, Stefan K=FChn, Martin =Saalmann
> --
> ... There are 10 kinds of people. Those who know binary code, and those
> who don't.
This link might help.
http://www.esa-server.net/index.php?option=3Dcom_content&task=3Dview&id=3D1=
14&Itemid=3D103&lang=3Den
Regards,
Enrique Martinez
Sr. Software Consultant|||http://ServerName/reportserver?/FolderName/ReportName&Param1=value1&Param2=value2
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Henning Eiben" <eiben@.busitec.de> wrote in message
news:%23VLf70h5HHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I figured, that I can call reports directly via URL and pass the
> appropriate parameters in the URL when adding '&mypara=value'.
> So far so good. But I could not figure out so far, how I can pass
> parameters that allow "multiselections". Using the above notation
> doesn't seem to work ... :(
> --
> Henning Eiben
> busitec GmbH
> Consultant
> e-mail: eiben@.busitec.de
>
> +49 (251) 13335-0 Tel
> +49 (251) 13335-35 Fax
> Rudolf-Diesel-Straße 59
> 48157 Münster
> www.busitec.de
> Sitz der Gesellschaft: Münster
> HR B 55 75 - Amtsgericht Münster
> USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
> Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann
>
> --
> ... There are 10 kinds of people. Those who know binary code, and those
> who don't.

Access parameters within dynamic SQL

I am exploring the use of dynamic SQL within a stored procedure and have run
in to a problem. The dynamic SQL has no visibility of variables declared
outside the dynamic SQL. Try this snippet which causes an error:
declare @.branch int
set @.branch = 10
exec ( 'select @.branch_no' )
Is there any way to make these variables visible to the dynamic sql without
concatenation?
The reason why this will be a problem is that I will be use the openxml
command within the dynamic sql. I will be using very large XML strings so I
am pretty sure that I will have problems concatenating XML strings with
dynamic sql statements.
Any ideas?
McGy
[url]http://mcgy.blogspot.com[/url]> The reason why this will be a problem is that I will be use the openxml
> command within the dynamic sql. I will be using very large XML strings so
> I
> am pretty sure that I will have problems concatenating XML strings with
> dynamic sql statements.
As long as each string is <= 8000 characters (or 4000 characters with
Unicode), you can say EXEC(@.sql1 + @.sql2 + @.sql3);
A|||Lookup the topic sp_ExecuteSQL in SQL Server Books Online. There is an
example which explains how to pass & return values from such strings.
--
Anith|||Great that was really useful. I have combined a couple of examples (openxml
and sp_executesql) from books online in the snippet below to show how XML
can be passed in as a parameter to dynamic sql:
DECLARE @.SQLString NVARCHAR(500)
/* Build the SQL string */
SET @.SQLString =
N'
DECLARE @.idoc int
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, ''/ROOT/Customer'',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
EXEC sp_xml_removedocument @.idoc'
/* Execute the string */
EXECUTE sp_executesql @.SQLString, N'@.doc text',
@.doc = '<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
McGy
[url]http://mcgy.blogspot.com[/url]
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#d272ckPGHA.740@.TK2MSFTNGP12.phx.gbl...
> Lookup the topic sp_ExecuteSQL in SQL Server Books Online. There is an
> example which explains how to pass & return values from such strings.
> --
> Anith
>|||>> am exploring the use of dynamic SQL within a stored procedure and have ru
n
in to a problem. <<
As well you should!! This is an awful way to even think of writing
code of any kind. Remember coupling, cohesion and all that stuff in
your fist Software Engineering course?
So you want on-the-fly, mixed, proprietary languages so you can
manipulate XML with T-SQL? This whole thing sounds like a pile of
kludges, but without better specs we can only guess at a relatioanl
solution.|||The reason for dynamic SQL is that I need to parameterize the database name
in the queries. We have a database with 20 odd tables with exactly the same
structure - so rather than duplicating the stored procedure 20 odd times I
am looking at writing it once with dynamic SQL.
The reason for XML is so that I can send large batches of data at a time to
the stored procedure. Which is very efficient.
I am not sure that I will use this technique but it is certainly one of
several I am considering. Its not a position I relish being in but that's
the way the database is so more likely than not I will have to work with its
shortcomings.
See another post by my titled "Parameterize table name without constructing
dynamic query?"
McGy
[url]http://mcgy.blogspot.com[/url]
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141350534.964106.45490@.t39g2000cwt.googlegroups.com...
> in to a problem. <<
> As well you should!! This is an awful way to even think of writing
> code of any kind. Remember coupling, cohesion and all that stuff in
> your fist Software Engineering course?
>
> So you want on-the-fly, mixed, proprietary languages so you can
> manipulate XML with T-SQL? This whole thing sounds like a pile of
> kludges, but without better specs we can only guess at a relatioanl
> solution.
>

ACCESS PARAMETERS USING IN COMMAND

I have a query like this in my access
PARAMETERS ID Long;
SELECT *
FROM GROUPS
WHERE (((GROUPS.id) In ([ID])));
after I run this query i dialog box appear and it wants me to enter ID
value. I want to enter more than one ID value for this query. I use , as a
delimeter character.
It asks
ID
I enter
1,2,3
it returns only one record which has first (1) ID number.
How can I achieve this query ?
I dont want to use more than one variable like this. Because I dont have an
exact variable number. I can enter 1 or 4 or 6 parameter at a time .
SELECT *
FROM GROUPS
WHERE (((GROUPS.id) In ([@.ID1,@.ID2,@.ID3])));Savas
See Dejan's example
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1
declare @.inList varchar(50)
set @.inList='10428,10429'
select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.InList)) As t
ON od.orderid = t.Item
"Savas Ates" <in da club> wrote in message
news:%23eBsFBRPGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I have a query like this in my access
> PARAMETERS ID Long;
> SELECT *
> FROM GROUPS
> WHERE (((GROUPS.id) In ([ID])));
>
> after I run this query i dialog box appear and it wants me to enter ID
> value. I want to enter more than one ID value for this query. I use , as a
> delimeter character.
> It asks
> ID
> I enter
> 1,2,3
> it returns only one record which has first (1) ID number.
> How can I achieve this query ?
> I dont want to use more than one variable like this. Because I dont have
> an exact variable number. I can enter 1 or 4 or 6 parameter at a time .
> SELECT *
> FROM GROUPS
> WHERE (((GROUPS.id) In ([@.ID1,@.ID2,@.ID3])));
>
>
>|||It works well in Sql Query Analyser but how can i implement it to my access
? I tried to execute it via asp it returned parameter error. I tried to save
it a query in my access db it returned error again? how can i solce it ?
////////////////////////////////////////////////////////////////////////////
/////////////////////////////////
DECLARE @.inList VARCHAR(1000)
SELECT @.inList='1,2,3'
select * from [groups]
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.inList)) As t
ON groups.id = t.Item
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
END
RETURN
END
GO|||What is the error?
CREATE PROCEDURE array_method_1
@.array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nsql nvarchar(4000)
SET @.nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @.array + ')'
PRINT @.nsql
EXEC sp_executesql @.nsql
END
GO
EXEC array_method_1
@.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
"Savas Ates" <in da club> wrote in message
news:e0lRJiRPGHA.3360@.TK2MSFTNGP09.phx.gbl...
> It works well in Sql Query Analyser but how can i implement it to my
> access ? I tried to execute it via asp it returned parameter error. I
> tried to save it a query in my access db it returned error again? how can
> i solce it ?
> //////////////////////////////////////////////////////////////////////////
///////////////////////////////////
> DECLARE @.inList VARCHAR(1000)
> SELECT @.inList='1,2,3'
> select * from [groups]
> INNER JOIN
> (SELECT Item
> FROM dbo.TsqlSplit(@.inList)) As t
> ON groups.id = t.Item
>
> IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
> DROP FUNCTION dbo.TsqlSplit
> GO
> CREATE FUNCTION dbo.TsqlSplit
> (@.List As varchar(8000))
> RETURNS @.Items table (Item varchar(8000) Not Null)
> AS
> BEGIN
> DECLARE @.Item As varchar(8000), @.Pos As int
> WHILE DATALENGTH(@.List)>0
> BEGIN
> SET @.Pos=CHARINDEX(',',@.List)
> IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
> SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
> IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
> SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
> END
> RETURN
> END
> GO
>|||You wrote TSQL statement which i can use it in my MS-SQL server.
I cant write it in MS ACcess and save it as an query
How can i do it ? I think it is not possible to write
SET NOCOUNT ON
PRINT @.nsql
EXEC sp_executesql @.nsql
command in An Ms ACCEss query ?
If Im wrong can u explain how to it ?
"Uri Dimant" <urid@.iscar.co.il>, haber iletisinde unlar
yazd:ud9i0rRPGHA.344@.TK2MSFTNGP11.phx.gbl...
> What is the error?
> CREATE PROCEDURE array_method_1
> @.array nvarchar(4000)
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.nsql nvarchar(4000)
> SET @.nsql = '
> SELECT *
> FROM sysobjects
> WHERE name IN ( ' + @.array + ')'
> PRINT @.nsql
> EXEC sp_executesql @.nsql
> END
> GO
>
> EXEC array_method_1
> @.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
>
>
> "Savas Ates" <in da club> wrote in message
> news:e0lRJiRPGHA.3360@.TK2MSFTNGP09.phx.gbl...
>|||Savas Ates wrote:
> You wrote TSQL statement which i can use it in my MS-SQL server.
> I cant write it in MS ACcess and save it as an query
> How can i do it ? I think it is not possible to write
> SET NOCOUNT ON
> PRINT @.nsql
> EXEC sp_executesql @.nsql
> command in An Ms ACCEss query ?
> If Im wrong can u explain how to it ?
>
Why did you crosspost this to a SQL Server newsgroup if you did not want
T-SQL answers?
Here is my canned answer I used to use for this type of question when I was
frequenting an Access mailing list:
There are two solutions for this problem listed in the following KB article
(Q210530 - ACC2000: How to Create a Parameter In() Statement), found by
searching for the keywords "parameter list query" (no quotes) at
http://support.microsoft.com.
http://support.microsoft.com/suppor...s/Q210/5/30.ASP
The first solution uses Instr() to test the field values against the list in
the parameter. The second involves dynamically creating a SQL statement in
code.
Thanks to Paul Overway, here is a third solution, using the Eval function:
WHERE (((Eval([Table]![Field] & " In(" & [Forms]![Formname]![textboxname] &
")"))=True))
or, using a prompted parameter:
WHERE (((Eval([Table]![Field] & " In(" & [Enter List] & ")"))=True))
Thanks to Jeffrey A. Williams, here's a 4th solution:
If you don't mind adding a table to your database, and you're comfortable
dealing with possible multi-user issues, this will perform better than
either of the solutions that involve running a function (Instr or Eval) on
every row of your table:
Create a new table with two fields:
tblCriteria:
Criteria text
Selected boolean (yes/no)
Populate the table with your values and select a couple of items. Now you
can use this table in your query as such:
Select * from table1
inner join tblcriteria
on table1.[your criteria field] = tblcriteria.criteria
where tblcriteria.selected = true
You can easily setup a form (or subform) that is bound to tblCriteria and
allow the users the
ability of selecting which values they want.
Thanks to Michael Walsh, here's yet another way:
SELECT Table3.ConName, Table3.State, Table3.Zip
FROM Table3
WHERE "," & [list] & "," LIKE "*," & [ConName] & ",*"
with [param] some string like: '1,4,5,7'
note that there is no space after the comas.
It works simply. If AccountID is 45, clearly ',1,4,5,7,' LIKE
'*,45,*' returns false.
If AccountID is 4, on the other hand, ',1,4,5,7,' LIKE '*,4,*'
returns true.
So, you have, in effect, an IN( ) where the list is a parameter.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"