Crystal Report

What is it you want returned?

Do you want Product A but only if it is low stock of 100?

Do you want all products with a low stock?

How is low stock determined?

mlmcc

<<Do you want Product A but only if it is low stock of 100?>>
yes i want all product but only if it all low stock.

<<Do you want all products with a low stock?>>
yes  i  want all product with a low stock or out of stock ( like stock is zero )

low stock field is in "Product" table

Fields Are
Prod_Code - Prod_Name - Category - PackPcs - Low_Stock

The product table isn't in the query.

DO you need to add the stock count from the queries together to determine if the count is low?

mlmcc

yes please

and product table is not in the query

i tried so many ways but no luck
.....!!

do i need to use selectionformula and put condition ..?

You can't use the selection filter because the product table isn't in the query.

WHich field in the query has the total stock or is it a sum of the fields?

mlmcc

thats the problem mlmcc
in product table i defined low stock in total pcs, and in the query there is no total pcs

Is the TTL_PCS field the total pieces for that record or do you need to add cases to it?

I think you are going to have to join the product table to each of the queries and select the low  stock field.

In the report group by product then sum the pieces and compare to low stock.  

mlmcc

"SELECT Opening_Stock.Prd_Code, Opening_Stock.Prd_Name, Opening_Stock.Category, sum(Opening_Stock.CFC) as CFC, sum(Opening_Stock.PCS) AS PCS, Sum(Opening_Stock.Total_PCS) AS TTL_PCS, PRODUCT.LowStock
  0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'OpenStock' as Source 
From Opening_Stock INNER JOIN PRODUCT ON Opening_Stock.Prd_Code = PRODUCT.Prd_Code
Group by Opening_Stock.Prd_Code, Opening_Stock.Prd_Name, Opening_Stock.Category " 

UNION

Do the same to the other parts

Select allOpen in new window

hi mlmcc
i tried your query
please check
 

RsTemp.Open "SELECT Opening_Stock.Prd_Code, Opening_Stock.Prd_Name, Opening_Stock.Category, sum(Opening_Stock.CFC) as CFC, sum(Opening_Stock.PCS) AS PCS, Sum(Opening_Stock.Total_PCS) AS TTL_PCS, Product.LowStock, 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'OpenStock' as Source From Opening_Stock INNER JOIN Product ON Opening_Stock.Prd_Code = Product.Prod_Code Group by Opening_Stock.Prd_Code, Opening_Stock.Prd_Name, Opening_Stock.Category " & _
"Union Select In_Stock_Dtl.Prd_Code, In_Stock_Dtl.Prd_Name, In_Stock_Dtl.Category, sum(In_Stock_Dtl.CFC) AS CFC, sum(In_Stock_Dtl.PCS) as PCS, Sum(In_Stock_Dtl.Total_PCS) AS TTL_Pcs, Product.LowStock, 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'InStock' as Source FROM In_Stock_Dtl INNER JOIN Product ON In_Stock_Dtl.Prd_Code = Product.Prod_Code group by In_Stock_Dtl.Prd_Code, In_Stock_Dtl.Prd_Name, In_Stock_Dtl.Category " & _
"Union Select Rtr_Stock_Dtl.Prd_Code, Rtr_Stock_Dtl.Prd_Name, Rtr_Stock_Dtl.Category, sum(Rtr_Stock_Dtl.CFC), sum(Rtr_Stock_Dtl.PCS), Sum(Rtr_Stock_Dtl.Total_PCS), Product.LowStock, 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'RtrStock' as Source From Rtr_Stock_Dtl INNER JOIN Product ON Rtr_Stock_Dtl.Prd_Code = Product.Prod_Code where Rtr_Stock_Dtl.Saleable = 'Saleable' group by Rtr_Stock_Dtl.Prd_Code, Rtr_Stock_Dtl.Prd_Name, Rtr_Stock_Dtl.Category " & _
"Union Select Out_Stock_Dtl.Prd_Code, Out_Stock_Dtl.Prd_Name, Out_Stock_Dtl.Category, sum(Out_Stock_Dtl.CFC), sum(Out_Stock_Dtl.PCS), Sum(Out_Stock_Dtl.Total_PCS), Product.LowStock, Sum(Out_Stock_Dtl.Rtr_CFC) as D_Rtr_CFC, Sum(Out_Stock_Dtl.Rtr_PCS) as D_Rtr_PCS, 'OutStock' as Source FROM Out_Stock_Dtl INNER JOIN Product ON Out_Stock_Dtl.Prd_Code = Product.Prod_Code group by Out_Stock_Dtl.Prd_Code, Out_Stock_Dtl.Prd_Name, Out_Stock_Dtl.Category ", con, adOpenKeyset, adLockOptimistic

Select allOpen in new window

Run Time Error
No value given for one or more required parameters.
What am i doing wrong..?

Are you passing a datset to the report or does the report have a database?

mlmcc

Report have a database please check my whole code
 

''''''please test this low stock
If Text1(0).Text = "Low_Stock" Then

        con.Execute "delete * from Temp_Stock"
        Set RsTemp = Nothing
        
RsTemp.Open "SELECT Opening_Stock.Prd_Code, Opening_Stock.Prd_Name, Opening_Stock.Category, sum(Opening_Stock.CFC) as CFC, sum(Opening_Stock.PCS) AS PCS, Sum(Opening_Stock.Total_PCS) AS TTL_PCS, Product.LowStock, 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'OpenStock' as Source From Opening_Stock INNER JOIN Product ON Opening_Stock.Prd_Code = Product.Prod_Code Group by Opening_Stock.Prd_Code, Opening_Stock.Prd_Name, Opening_Stock.Category " & _
"Union Select In_Stock_Dtl.Prd_Code, In_Stock_Dtl.Prd_Name, In_Stock_Dtl.Category, sum(In_Stock_Dtl.CFC) AS CFC, sum(In_Stock_Dtl.PCS) as PCS, Sum(In_Stock_Dtl.Total_PCS) AS TTL_Pcs, Product.LowStock, 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'InStock' as Source FROM In_Stock_Dtl INNER JOIN Product ON In_Stock_Dtl.Prd_Code = Product.Prod_Code group by In_Stock_Dtl.Prd_Code, In_Stock_Dtl.Prd_Name, In_Stock_Dtl.Category " & _
"Union Select Rtr_Stock_Dtl.Prd_Code, Rtr_Stock_Dtl.Prd_Name, Rtr_Stock_Dtl.Category, sum(Rtr_Stock_Dtl.CFC), sum(Rtr_Stock_Dtl.PCS), Sum(Rtr_Stock_Dtl.Total_PCS), Product.LowStock, 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'RtrStock' as Source From Rtr_Stock_Dtl INNER JOIN Product ON Rtr_Stock_Dtl.Prd_Code = Product.Prod_Code where Rtr_Stock_Dtl.Saleable = 'Saleable' group by Rtr_Stock_Dtl.Prd_Code, Rtr_Stock_Dtl.Prd_Name, Rtr_Stock_Dtl.Category " & _
"Union Select Out_Stock_Dtl.Prd_Code, Out_Stock_Dtl.Prd_Name, Out_Stock_Dtl.Category, sum(Out_Stock_Dtl.CFC), sum(Out_Stock_Dtl.PCS), Sum(Out_Stock_Dtl.Total_PCS), Product.LowStock, Sum(Out_Stock_Dtl.Rtr_CFC) as D_Rtr_CFC, Sum(Out_Stock_Dtl.Rtr_PCS) as D_Rtr_PCS, 'OutStock' as Source FROM Out_Stock_Dtl INNER JOIN Product ON Out_Stock_Dtl.Prd_Code = Product.Prod_Code group by Out_Stock_Dtl.Prd_Code, Out_Stock_Dtl.Prd_Name, Out_Stock_Dtl.Category ", con, adOpenKeyset, adLockOptimistic

'        If RsTemp.RecordCount > 0 Then
'            Set DataGrid1.DataSource = RsTemp
'                DataGrid1.Columns(0).Width = 1100
'                DataGrid1.Columns(1).Width = 2000
'                DataGrid1.Columns(2).Width = 1400
'                DataGrid1.Columns(3).Width = 900
'                DataGrid1.Columns(4).Width = 950
'                DataGrid1.Columns(5).Width = 1200
'                DataGrid1.Columns(6).Width = 1300
'                DataGrid1.Columns(7).Width = 1300
'                DataGrid1.Columns(8).Width = 1300
'        End If
    
    If RsTemp1.State = 1 Then RsTemp1.Close
       RsTemp1.Open "Select * from Temp_Stock", con, adOpenKeyset, adLockPessimistic
    If RsTemp.RecordCount > 0 Then
          Col0 = RsTemp.Fields(0)
          Col1 = RsTemp.Fields(1)
          Col2 = RsTemp.Fields(2)
          Col3 = 0
          Col4 = 0
          Col5 = 0
          Col6 = 0
          Col7 = 0
          Col8 = 0
          Col9 = 0
          Col10 = 0
          Col11 = 0
          Col12 = 0
          
          For i = 0 To RsTemp.RecordCount - 1
             If Col0 <> RsTemp.Fields(0) Then
    
                 RsTemp1.AddNew
                   RsTemp1.Fields(0) = Col0
                   RsTemp1.Fields(1) = Col1
                   RsTemp1.Fields(2) = Col2
                   RsTemp1.Fields(3) = Col3
                   RsTemp1.Fields(4) = Col4
                   RsTemp1.Fields(5) = Col5
                   RsTemp1.Fields(6) = Col6
                   RsTemp1.Fields(7) = Col7
                   RsTemp1.Fields(8) = Col8
                   RsTemp1.Fields(9) = Col9
                   RsTemp1.Fields(10) = Col10
                   RsTemp1.Fields(11) = Col11
                   RsTemp1.Fields(12) = Col12
                   
                RsTemp1.Update
                
                Col0 = RsTemp.Fields(0)
                Col1 = RsTemp.Fields(1)
                Col2 = RsTemp.Fields(2)
                Col3 = 0
                Col4 = 0
                Col5 = 0
                Col6 = 0
                Col7 = 0
                Col8 = 0
                Col9 = 0
                Col10 = 0
                Col11 = 0
                Col12 = 0
                
             End If
    
                If RsTemp.Fields(8) = "OpenStock" Then
                   Col3 = RsTemp.Fields(3)
                   Col4 = RsTemp.Fields(4)
                ElseIf RsTemp.Fields(8) = "InStock" Then
                   Col5 = RsTemp.Fields(3)
                   Col6 = RsTemp.Fields(4)
                ElseIf RsTemp.Fields(8) = "RtrStock" Then
                   Col7 = RsTemp.Fields(3)
                   Col8 = RsTemp.Fields(4)
                ElseIf RsTemp.Fields(8) = "OutStock" Then
                   Col9 = RsTemp.Fields(3)
                   Col10 = RsTemp.Fields(4)
                   Col11 = RsTemp.Fields(6)
                   Col12 = RsTemp.Fields(7)
                End If
             
             RsTemp.MoveNext
          Next
       End If
    
       RsTemp1.AddNew
         RsTemp1.Fields(0) = Col0
         RsTemp1.Fields(1) = Col1
         RsTemp1.Fields(2) = Col2
         RsTemp1.Fields(3) = Col3
         RsTemp1.Fields(4) = Col4
         RsTemp1.Fields(5) = Col5
         RsTemp1.Fields(6) = Col6
         RsTemp1.Fields(7) = Col7
         RsTemp1.Fields(8) = Col8
         RsTemp1.Fields(9) = Col9
         RsTemp1.Fields(10) = Col10
         RsTemp1.Fields(11) = Col11
         RsTemp1.Fields(12) = Col12
       RsTemp1.Update
End If
Exit Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Select allOpen in new window

and for Print
 

If Text1(0).Text = "Low_Stock" Then
    CrystalReport1.ReportFileName = App.Path & "\Low_Stock.rpt"
    CrystalReport1.Connect = con
    CrystalReport1.DiscardSavedData = True
    CrystalReport1.RetrieveDataFiles
    CrystalReport1.Password = Chr(10) & "xxxxxxxxx"
    CrystalReport1.WindowState = crptMaximized
    CrystalReport1.Destination = crptToWindow
    CrystalReport1.Action = 1
    CrystalReport1.PageZoom 100

End If

Select allOpen in new window

What is highlighted when you get the error?

Is it sufficient to simply have the low stock item in the report?

mlmcc

<<What is highlighted when you get the error?>>
highlighted on this query
 

RsTemp.Open "SELECT Opening_Stock.Prd_Code, Opening_Stock.Prd_Name, Opening_Stock.Category, sum(Opening_Stock.CFC) as CFC, sum(Opening_Stock.PCS) AS PCS, Sum(Opening_Stock.Total_PCS) AS TTL_PCS, Product.LowStock, 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'OpenStock' as Source From Opening_Stock INNER JOIN Product ON Opening_Stock.Prd_Code = Product.Prod_Code Group by Opening_Stock.Prd_Code, Opening_Stock.Prd_Name, Opening_Stock.Category " & _
"Union Select In_Stock_Dtl.Prd_Code, In_Stock_Dtl.Prd_Name, In_Stock_Dtl.Category, sum(In_Stock_Dtl.CFC) AS CFC, sum(In_Stock_Dtl.PCS) as PCS, Sum(In_Stock_Dtl.Total_PCS) AS TTL_Pcs, Product.LowStock, 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'InStock' as Source FROM In_Stock_Dtl INNER JOIN Product ON In_Stock_Dtl.Prd_Code = Product.Prod_Code group by In_Stock_Dtl.Prd_Code, In_Stock_Dtl.Prd_Name, In_Stock_Dtl.Category " & _
"Union Select Rtr_Stock_Dtl.Prd_Code, Rtr_Stock_Dtl.Prd_Name, Rtr_Stock_Dtl.Category, sum(Rtr_Stock_Dtl.CFC), sum(Rtr_Stock_Dtl.PCS), Sum(Rtr_Stock_Dtl.Total_PCS), Product.LowStock, 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'RtrStock' as Source From Rtr_Stock_Dtl INNER JOIN Product ON Rtr_Stock_Dtl.Prd_Code = Product.Prod_Code where Rtr_Stock_Dtl.Saleable = 'Saleable' group by Rtr_Stock_Dtl.Prd_Code, Rtr_Stock_Dtl.Prd_Name, Rtr_Stock_Dtl.Category " & _
"Union Select Out_Stock_Dtl.Prd_Code, Out_Stock_Dtl.Prd_Name, Out_Stock_Dtl.Category, sum(Out_Stock_Dtl.CFC), sum(Out_Stock_Dtl.PCS), Sum(Out_Stock_Dtl.Total_PCS), Product.LowStock, Sum(Out_Stock_Dtl.Rtr_CFC) as D_Rtr_CFC, Sum(Out_Stock_Dtl.Rtr_PCS) as D_Rtr_PCS, 'OutStock' as Source FROM Out_Stock_Dtl INNER JOIN Product ON Out_Stock_Dtl.Prd_Code = Product.Prod_Code group by Out_Stock_Dtl.Prd_Code, Out_Stock_Dtl.Prd_Name, Out_Stock_Dtl.Category ", con, adOpenKeyset, adLockOptimistic

Select allOpen in new window

<<Is it sufficient to simply have the low stock item in the report?>>
yes it is very simple but

but result produce by this query then adding this result into a temp table (Temp_Stock)
 

Do you need the results in the temp table if the stock level is sufficient?

mlmcc

no i dont want if stock level is sufficient
i just want if stock level is down or out of stock

Here is an idea.  You don't need the product table inthat query.

Lines 42
          For i = 0 To RsTemp.RecordCount - 1
Add code to open Product table and select the low count for the current product
             Open product with SELECT Product.LowCOunt FROM Product Where Product.Prd_Code = Col0
Change line 43 to test if total pieces is low        
             If Col0 <> RsTemp.Fields(0)  AND RSProd.LowCOunt > Col6 Then

mlmcc

let me try

it says " Object Required "

For i = 0 To RsTemp.RecordCount - 1
            Set RsTemp2 = Nothing
            If RsTemp2.State = 1 Then RsTemp2.Close
            RsTemp2.Open "SELECT Product.Low_Stock FROM Product Where Product.Prod_Code = '" & Col0 & "'", con, adOpenKeyset, adLockPessimistic
            
''             If Col0 <> RsTemp.Fields(0) Then
              If Col0 <> RsTemp.Fields(0) And RsProd.Low_Stock > Col5 Then

Select allOpen in new window

Where?

Might be better as

 

For i = 0 To RsTemp.RecordCount - 1
            
''             If Col0 <> RsTemp.Fields(0) Then
     If Col0 <> RsTemp.Fields(0) Then 
            Set RsTemp2 = Nothing
            If RsTemp2.State = 1 Then RsTemp2.Close
            RsTemp2.Open "SELECT Product.Low_Stock FROM Product Where Product.Prod_Code = '" & Col0 & "'", con, adOpenKeyset, adLockPessimistic
            If RsProd.Low_Stock > Col5  then
                 RsTemp1.AddNew

Select allOpen in new window

mlmcc

i am getting only one record, not all

Please check my whole code, what am i doing wrong..?
 

If Text1(0).Text = "Low_Stock" Then

        con.Execute "delete * from Temp_Stock"
        Set RsTemp = Nothing

        RsTemp.Open "SELECT Opening_Stock.Prd_Code, Opening_Stock.Prd_Name, Opening_Stock.Category, sum(Opening_Stock.CFC) as CFC, sum(Opening_Stock.PCS) AS PCS, Sum(Opening_Stock.Total_PCS) AS TTL_PCS, 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'OpenStock' as Source From Opening_Stock Group by Opening_Stock.Prd_Code, Opening_Stock.Prd_Name, Opening_Stock.Category " & _
        "Union Select In_Stock_Dtl.Prd_Code, In_Stock_Dtl.Prd_Name, In_Stock_Dtl.Category, sum(In_Stock_Dtl.CFC) AS CFC, sum(In_Stock_Dtl.PCS) as PCS, Sum(In_Stock_Dtl.Total_PCS) AS TTL_Pcs, 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'InStock' as Source FROM In_Stock_Dtl group by In_Stock_Dtl.Prd_Code, In_Stock_Dtl.Prd_Name, In_Stock_Dtl.Category " & _
        "Union Select Rtr_Stock_Dtl.Prd_Code, Rtr_Stock_Dtl.Prd_Name, Rtr_Stock_Dtl.Category, sum(Rtr_Stock_Dtl.CFC), sum(Rtr_Stock_Dtl.PCS), Sum(Rtr_Stock_Dtl.Total_PCS), 0 as D_Rtr_CFC, 0 as D_Rtr_PCS, 'RtrStock' as Source From Rtr_Stock_Dtl where Rtr_Stock_Dtl.Saleable = 'Saleable' group by Rtr_Stock_Dtl.Prd_Code, Rtr_Stock_Dtl.Prd_Name, Rtr_Stock_Dtl.Category " & _
        "Union Select Out_Stock_Dtl.Prd_Code, Out_Stock_Dtl.Prd_Name, Out_Stock_Dtl.Category, sum(Out_Stock_Dtl.CFC), sum(Out_Stock_Dtl.PCS), Sum(Out_Stock_Dtl.Total_PCS), Sum(Out_Stock_Dtl.Rtr_CFC) as D_Rtr_CFC, Sum(Out_Stock_Dtl.Rtr_PCS) as D_Rtr_PCS, 'OutStock' as Source FROM Out_Stock_Dtl group by Out_Stock_Dtl.Prd_Code, Out_Stock_Dtl.Prd_Name, Out_Stock_Dtl.Category ", con, adOpenKeyset, adLockOptimistic

        If RsTemp.RecordCount > 0 Then
            Set DataGrid1.DataSource = RsTemp
                DataGrid1.Columns(0).Width = 1100
                DataGrid1.Columns(1).Width = 2000
                DataGrid1.Columns(2).Width = 1400
                DataGrid1.Columns(3).Width = 900
                DataGrid1.Columns(4).Width = 950
                DataGrid1.Columns(5).Width = 1200
                DataGrid1.Columns(6).Width = 1300
                DataGrid1.Columns(7).Width = 1300
                DataGrid1.Columns(8).Width = 1300
        End If


    If RsTemp1.State = 1 Then RsTemp1.Close
       RsTemp1.Open "Select * from Temp_Stock", con, adOpenKeyset, adLockPessimistic
    If RsTemp.RecordCount > 0 Then
          Col0 = RsTemp.Fields(0)
          Col1 = RsTemp.Fields(1)
          Col2 = RsTemp.Fields(2)
          Col3 = 0
          Col4 = 0
          Col5 = 0
          Col6 = 0
          Col7 = 0
          Col8 = 0
          Col9 = 0
          Col10 = 0
          Col11 = 0
          Col12 = 0


        For i = 0 To RsTemp.RecordCount - 1
                    
             If Col0 <> RsTemp.Fields(0) Then
                    Set RsTemp2 = Nothing
                    If RsTemp2.State = 1 Then RsTemp2.Close
                    RsTemp2.Open "SELECT Product.Low_Stock FROM Product Where Product.Prod_Code = '" & Col0 & "'", con, adOpenKeyset, adLockPessimistic
                    If RsTemp2.Fields(0) > Col5 Then

                         RsTemp1.AddNew
                           RsTemp1.Fields(0) = Col0
                           RsTemp1.Fields(1) = Col1
                           RsTemp1.Fields(2) = Col2
                           RsTemp1.Fields(3) = Col3
                           RsTemp1.Fields(4) = Col4
                           RsTemp1.Fields(5) = Col5
                           RsTemp1.Fields(6) = Col6
                           RsTemp1.Fields(7) = Col7
                           RsTemp1.Fields(8) = Col8
                           RsTemp1.Fields(9) = Col9
                           RsTemp1.Fields(10) = Col10
                           RsTemp1.Fields(11) = Col11
                           RsTemp1.Fields(12) = Col12
                        RsTemp1.Update
                
                            Col0 = RsTemp.Fields(0)
                            Col1 = RsTemp.Fields(1)
                            Col2 = RsTemp.Fields(2)
                            Col3 = 0
                            Col4 = 0
                            Col5 = 0
                            Col6 = 0
                            Col7 = 0
                            Col8 = 0
                            Col9 = 0
                            Col10 = 0
                            Col11 = 0
                            Col12 = 0
             End If
                End If
                
                If RsTemp.Fields(8) = "OpenStock" Then
                   Col3 = RsTemp.Fields(3)
                   Col4 = RsTemp.Fields(4)
                ElseIf RsTemp.Fields(8) = "InStock" Then
                   Col5 = RsTemp.Fields(3)
                   Col6 = RsTemp.Fields(4)
                ElseIf RsTemp.Fields(8) = "RtrStock" Then
                   Col7 = RsTemp.Fields(3)
                   Col8 = RsTemp.Fields(4)
                ElseIf RsTemp.Fields(8) = "OutStock" Then
                   Col9 = RsTemp.Fields(3)
                   Col10 = RsTemp.Fields(4)
                   Col11 = RsTemp.Fields(6)
                   Col12 = RsTemp.Fields(7)
                End If
             
             RsTemp.MoveNext
          Next
       End If
    
       RsTemp1.AddNew
         RsTemp1.Fields(0) = Col0
         RsTemp1.Fields(1) = Col1
         RsTemp1.Fields(2) = Col2
         RsTemp1.Fields(3) = Col3
         RsTemp1.Fields(4) = Col4
         RsTemp1.Fields(5) = Col5
         RsTemp1.Fields(6) = Col6
         RsTemp1.Fields(7) = Col7
         RsTemp1.Fields(8) = Col8
         RsTemp1.Fields(9) = Col9
         RsTemp1.Fields(10) = Col10
         RsTemp1.Fields(11) = Col11
         RsTemp1.Fields(12) = Col12
       RsTemp1.Update
End If
Exit Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''till here'''''''''''''''''''

Select allOpen in new window

WHen you say only one record.  Are there more than 1 that is low stock?

mlmcc

yes
there is more than 1 Record

ok
it was wrong
Col13 instead of Col5

Let me test , some thing is wrong..

I tried but no luck
Please assist me (asap)

what must i put instead of  Col5

                    If RsTemp2.Fields(0) > Col5 Then
'                   'If RsTemp2.Fields(0) > Col13 Then
'                    If RsTemp2.Fields(0) > RsTemp.Fields(5) Then

Just an idea, but how to do it yet i dont know.. :-(

i am getting accurate data from your Master Query.
and i am displaying data in Datagrid
Please check data in Datagrid ( Attached file )
 Query-Result-In-DataGrid.doc

in the data grid there is column name TTL_PCS
if  Prd_Code same for bellow mention SORCES then
if SOURCE = OpenStock then
TTL_PCS_1 = TTL_PCS
if SOURCE = InStock then
TTL_PCS_2 = TTL_PCS + TTL_PCS_1
if SOURCE = RtrStock then
TTL_PCS_3 =  TTL_PCS  + TTL_PCS_1 + TTL_PCS_2

Here we can deduct
if SOURCE = OutStock then
TTL_PCS_4 = TTL_PCS_3 - TTL_PCS

Now we have TTL_PCS_4 to compare with Product.Low_Stock

but how to do this ..?

May be you have better method..!

Ok what i did is
i added one column in temp table for TTL_PCS and
and inserting a value ( Stock Left )

then just adding a selection forkula

CrystalReport1.SelectionFormula = "{Product.Low_Stock} >= {Temp_Stock.TTL_PCS}"

and i got accurate result

thanks