Query report CR11 and VB6 Problem

[khandu]

I am generating a report where it is showing all items which are in stock or out of stock

Now I am taking that choice from a cmbstatus list.. so the value is 0 is in stock and 1 if out of stock

now the sql query to run in the report is

select * from item where item.status = 1/0

( depending what the person chooses )

how do i do this.. i did create a parameter field "stat" but i am unable to generate this formula.. I am passing the value via

crxReport.ParameterFields.GetItemByName("stat").AddCurrentValue st

where st = the value chooses by user i.e. 0 or 1

i gave in the selection formula

{item.status} = {?stat}

But it gives error

A Number is required here

what to do ????

once this problem is solved i have another multitable query problem which i will give later

[Mark Gambo]

Why not just pass a SQL String to the report instead:

Private Sub Form_Load()
Dim rs As ADODB.Recordset
Dim strSQL As String
 
    strSQL = "select * from item where item.status = '" & cmbstatus & "';"
    rs.Open strSQL, conn, . . . 
            
            With Report
                .Database.SetDataSource rs
                .DiscardSavedData
            End With
    
Set Report = Nothing
 
rs.Close
Set rs = Nothing
 
End Sub

[khandu]

can u elaborate

it gives datatype conversion error on line

rs.Open strSQL, conn, . . .

[Besoup]

Change the data type of your parameter field.

[khandu]

its string

and i dont think i m passing a parameter to it.. as its not mentioned on code

[Besoup]

Is item.status a number? if so you need to make sure you are comparing it to a number and not a string.

[khandu]

both are integer

[khandu]

this is the code

If reportname = "items" Then
    reportname = App.Path & "\reports\items.rpt"
    Set crxReport = crxApp.OpenReport(reportname)
    Dim strSQL As String
    strSQL = "select * from item_details where item_details.status = '" & opt & "';"
    rs.OpenRecordset strSQL
    
    With crreport
                .Database.SetDataSource rs
                .DiscardSavedData
            End With

[Besoup]

I was originally refering to your first post. What is the error with that code?

[khandu]

oh yeah .. fixed.. 1 was string and 1 was number.. now working..

Now another 2 queries

1) When the parameter value of "stat" is 0 .. i want to show a text like "shipped" somewhere.. how do i do this connection in the report ??

2) There is another 2 queries.. if the parameter value is 0 then the following query should be run

select order_details.order_id FROM order_details INNER JOIN shipping ON order_details.order_id = shipping.order_id

else the following should be run ( if parameter "stat" = 1 )

SELECT order_details.order_id FROM order_details WHERE order_details.order_id NOT IN (SELECT shipping.order_id FROM shipping )

how do i do this codeing in the report in to run seperate queries depending upon the parameter value??

thanks

[Besoup]

If you are passing stat to the report you can make a formula field to display different things depending on the parameter.

Just make a new formula and add a if statement to it.

[khandu]

i tired and i put this in selection formula editor

if {?option} = 0 then select order_details.order_id FROM order_details INNER JOIN shipping ON order_details.order_id = shipping.order_id

gives error

a number, currency amount, boolean,date,time or string is expected here

on the line select order_details.order_id

what to do ??

[khandu]

please shed some light here..

help on the above post..

[khandu]

also one more problem

I increased the width of the details field in report.. and generated one so that result goes in 2-3 pages.. but it just shows 1st page.. whats wrong??

[Besoup]

oh yeah .. fixed.. 1 was string and 1 was number.. now working..

Now another 2 queries

1) When the parameter value of "stat" is 0 .. i want to show a text like "shipped" somewhere.. how do i do this connection in the report ??

2) There is another 2 queries.. if the parameter value is 0 then the following query should be run

select order_details.order_id FROM order_details INNER JOIN shipping ON order_details.order_id = shipping.order_id

else the following should be run ( if parameter "stat" = 1 )

SELECT order_details.order_id FROM order_details WHERE order_details.order_id NOT IN (SELECT shipping.order_id FROM shipping )

how do i do this codeing in the report in to run seperate queries depending upon the parameter value??

thanks

To answer #1 all you have to do is make a formula field with an if statement in it.

If {?Parameter} = 0 Then
"Shipped"
else
"Not Shipped

then place the formula field on the report somewhere.

not sure how to answer #2

[khandu]

thanks..

1) resolved ... i was tryin to pass a value to a txtbox.. heh.. so simple ur method

some1 please help in 2nd and 3rd problem

2) as mentioned above : multitable query run depending upon the parameter value

3) I increased the width of the details field in report.. and generated one so that result goes in 2-3 pages.. but it just shows 1st page.. whats wrong??

[khandu]

*bump*

[khandu]

Hey some1 suggested

dim strTheQuery
If stat = 0 Then
    strTheQuery = select order_details.order_id FROM order_details INNER JOIN shipping ON order_details.order_id = shipping.order_id
Else
    strTheQuery = SELECT order_details.order_id FROM order_details WHERE order_details.order_id NOT IN (SELECT shipping.order_id FROM shipping )
End If

So what to do with strthequery and how to pass it?? as a parameter???? or what

[khandu]

bump..

some1 please help with the problem No 2) and 3) which is mentioned 2 posts above..