"IanP" <IanP@discussions.microsoft.com> wrote in message
news:B9084E03-9444-46A9-BD82-15594BA846AA@microsoft.com...
> Reporting Services "All" Parameter Query design problem in Visual Studio
> 2003
>
> I am trying to obtain data from an Access 2002 database, either a single
> specific item or "All" records using a parameter query
>
> The selection is working against an Autonumber Int field and does not
> return
> data for the "ALL" part of the query although this structure works in SQL
> Server. What needs to be amended to obtain the data from Access?
>
> Have discovered that the ODBC link does not like named parameters so am
> assuming that it also does not like something else which is why the -1
> setting to generate a select all records is not woring
>
> Many Thanks
>
> Ian
>
> DROP DOWN SELECTION BOX QUERY - this works and generates a list of records
> with an all option
>
> SELECT ContractorId, Contractor
>
> FROM tblContractor
>
> UNION
>
> SELECT - 1, '< ALL > '
>
> FROM tblcontractor
>
> ORDER BY Contractor
>
>
>
>
>
>
>
>
>
> DATA QUERY Not working on the "All" option
>
> SELECT tblContractor.*, tblContractorAdrs.ContractorAddressLine1,
> tblContractorAdrs.ContractorAddressLine2,
> tblContractorAdrs.ContractorAddressLine3,
>
> tblContractorAdrs.ContractorAddressLine4,
> tblContractorAdrs.ContractorAddressLine5,
> tblContractorAdrs.ContractorPostCode,
>
> tblContractor.SapVendID AS Expr1,
> tblContractor.ContractorId AS ConId
>
> FROM (tblContractor INNER JOIN
>
> tblContractorAdrs ON tblContractor.SapVendID =
> tblContractorAdrs.SapVendID)
>
> WHERE (tblContractor.ContractorId = ?) OR
>
> (tblContractor.ContractorId = - 1)
>

"Bruce L-C [MVP]" wrote:

> Try this (you might need to go to the generic query designer, the button to
> do so is to the right of the ...).
>
> select * from sometable where somefield = ? or ? = 'All'
>
> Note that you cannot used named parameters, you have to use a ? for ODBC
> parameters.
>
> RS will create two report parameters. You will want to remove one of them
> and then map both query parameters to the same parameter. Click on the ...,
> parameters tab and change the second one to be mapped to the same report
> parameter as the first.
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "IanP" <IanP@discussions.microsoft.com> wrote in message
> news:B9084E03-9444-46A9-BD82-15594BA846AA@microsoft.com...
> > Reporting Services "All" Parameter Query design problem in Visual Studio
> > 2003
> >
> > I am trying to obtain data from an Access 2002 database, either a single
> > specific item or "All" records using a parameter query
> >
> > The selection is working against an Autonumber Int field and does not
> > return
> > data for the "ALL" part of the query although this structure works in SQL
> > Server. What needs to be amended to obtain the data from Access?
> >
> > Have discovered that the ODBC link does not like named parameters so am
> > assuming that it also does not like something else which is why the -1
> > setting to generate a select all records is not woring
> >
> > Many Thanks
> >
> > Ian
> >
> > DROP DOWN SELECTION BOX QUERY - this works and generates a list of records
> > with an all option
> >
> > SELECT ContractorId, Contractor
> >
> > FROM tblContractor
> >
> > UNION
> >
> > SELECT - 1, '< ALL > '
> >
> > FROM tblcontractor
> >
> > ORDER BY Contractor
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > DATA QUERY Not working on the "All" option
> >
> > SELECT tblContractor.*, tblContractorAdrs.ContractorAddressLine1,
> > tblContractorAdrs.ContractorAddressLine2,
> > tblContractorAdrs.ContractorAddressLine3,
> >
> > tblContractorAdrs.ContractorAddressLine4,
> > tblContractorAdrs.ContractorAddressLine5,
> > tblContractorAdrs.ContractorPostCode,
> >
> > tblContractor.SapVendID AS Expr1,
> > tblContractor.ContractorId AS ConId
> >
> > FROM (tblContractor INNER JOIN
> >
> > tblContractorAdrs ON tblContractor.SapVendID =
> > tblContractorAdrs.SapVendID)
> >
> > WHERE (tblContractor.ContractorId = ?) OR
> >
> > (tblContractor.ContractorId = - 1)
> >
>
>
> .