query troubles

I used a query in VB with an access form as the front end to create a report...I have modified it to try to make it work in SQL Server...it is returning too many fields though currently....

Select dev.KinderNumber,dev.Client,dev.MFG,dev.FAMILY,dev .Intro_Date,dev.FIXTURE,dev.Dimensions_HWD,
dev.MFG_Price,dev.Item_Number,dp.picname from Development
as dev, DevelopmentPictures as dp
RIGHT JOIN Development ON dp.[KinderNum] = Development.[KinderNumber]
WHERE dp.[Revision_Letter]
IN(select max([Revision_Letter]) from DevelopmentPictures where DevelopmentPictures.KinderNum = dp.KinderNum);

this returns about 57000 rows I usually have about 350-400..

also : IN(select max([Revision_Letter]) from DevelopmentPictures where DevelopmentPictures.KinderNum = KinderNum);

if I take out the dp from dp.kindernum it only retunrs the row headings??

Please help!

thanks in advance,

JohnWhat does Development as dev join to? May be a cross join there...|||Since you are comparing the results from two different databases, VB and SQL Server, I'd suggest first running a quick check on the tables to see if you've got the same number of rows in both.|||jfouse

You didn't say what your query should return so this is a bit of a guess.

Select dev.KinderNumber
, dev.Client
, dev.MFG
, dev.FAMILY
, dev.Intro_Date
, dev.FIXTURE
, dev.Dimensions_HWD
, dev.MFG_Price
, dev.Item_Number
, dp.picname
from Development as dev
left join DevelopmentPictures as dp on dev.KinderNumber = dp.KinderNum
where dp.Revision_Letter = (select max(Revision_Letter)
from DevelopmentPictures
where DevelopmentPictures.KinderNum = dp.KinderNum)sql

Query String Programmatically

Is any way to Modified The Query String Programmatically for a Report ?
I want to be able to Change the WHERE Clause without using Parameter..
e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report I
want something like WHERE DeliveryDate < '2005-04-04'.
Thank. Any Idea I would appreciated.
--
Message posted via http://www.sqlmonster.comGot from BOL.
Passing a Report Parameter on a URL
You can pass report parameters to a report by including them in a URL. These
URL parameters are not prefixed, because they are passed directly to the
report processing engine. For more about report parameters, see Running a
Parameterized Report.
Example
The following example uses the report parameter EmployeeID to render the
specified report:
http://server/reportserver?/Sales/Northwest/Employee Sales
Report&rs:Command=Render&EmployeeID=1234See Also
The operator cant be changed unless you use dynamic SQL.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Edgar Mantilla via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:000edb152d5c494b8854daa6aaf009ff@.SQLMonster.com...
> Is any way to Modified The Query String Programmatically for a Report ?
> I want to be able to Change the WHERE Clause without using Parameter..
> e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report
> I
> want something like WHERE DeliveryDate < '2005-04-04'.
> Thank. Any Idea I would appreciated.
> --
> Message posted via http://www.sqlmonster.com|||Thank you,
I am working in a windows form that selects the report from the Report
Server and send it as .PDF to a specific location, Passing a Report
Parameter on a URL, it works well, but I want to have more flexibility in
building my query string, so I worked with a RDL Generator (Form BOL
Walkthrough ? Generating RDL Using the .NET Framework), which work fine
when you are creating a report from scratch.
Is any way to change the CommandTex or Query string of an existing report
programmatically?
--
Message posted via http://www.sqlmonster.com|||Edgar,
AFAIK you can't change the query text at report run time. There are
solutions to this type of complex querying, and let me say up front that I
recommend you put your query in a stored procedure instead of trying to put
this all into the report itself.
As Jens said, you can use dynamic SQL. This involves building your query as
a string and then calling EXEC (string). If you only have a limited number
of choices (e.g. only <, =, or >) then you can simply use IF statements and
hard code the queries for each branch.
Note: if you go the dynamic SQL route, beware of SQL injection attacks.
That's a whole subject, and too much to discuss here in one post.
Ted
"Edgar Mantilla via SQLMonster.com" wrote:
> Thank you,
> I am working in a windows form that selects the report from the Report
> Server and send it as .PDF to a specific location, Passing a Report
> Parameter on a URL, it works well, but I want to have more flexibility in
> building my query string, so I worked with a RDL Generator (Form BOL
> Walkthrough â' Generating RDL Using the .NET Framework), which work fine
> when you are creating a report from scratch.
> Is any way to change the CommandTex or Query string of an existing report
> programmatically?
> --
> Message posted via http://www.sqlmonster.com
>|||Hi, in the Generic Query Designer you can use some like this:
="SELECT " & Parameters!Field1.Value
" FROM " &
Parameters!Table.Value
"Edgar Mantilla via SQLMonster.com" wrote:
> Is any way to Modified The Query String Programmatically for a Report ?
> I want to be able to Change the WHERE Clause without using Parameter..
> e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report I
> want something like WHERE DeliveryDate < '2005-04-04'.
> Thank. Any Idea I would appreciated.
> --
> Message posted via http://www.sqlmonster.com
>