Solved

Crystal Report

Posted on 2011-09-19
32
278 Views
Last Modified: 2012-05-12
Hi
i am using following query to get Current Stock. (this query provided by "MLMCC"


What will be the low stock query
like if low stock defined 100 for Product "A" then i want  only this items detail

please Suggest me what query will be..?
0
Comment
Question by:crystal_Tech
  • 19
  • 11
32 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 36564373
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
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36566367

<<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


0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36567509
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
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36568939
yes please
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36569055
and product table is not in the query
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36569464
i tried so many ways but no luck
.....!!
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36569502
do i need to use selectionformula and put condition ..?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36570325
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
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36570457
thats the problem mlmcc
in product table i defined low stock in total pcs, and in the query there is no total pcs
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36571181
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

Open in new window

0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36576996
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

Open in new window


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

Expert Comment

by:mlmcc
ID: 36578220
Are you passing a datset to the report or does the report have a database?

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36579172
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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Open 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

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36580619
What is highlighted when you get the error?

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

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36582147

<<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

Open 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)
 
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 100

Expert Comment

by:mlmcc
ID: 36582794
Do you need the results in the temp table if the stock level is sufficient?

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36582855
no i dont want if stock level is sufficient
i just want if stock level is down or out of stock
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36583088
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
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36583318
let me try
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36583823

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

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36584576
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

Open in new window


mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36589592
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'''''''''''''''''''

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36591161
WHen you say only one record.  Are there more than 1 that is low stock?

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36596768

yes
there is more than 1 Record
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 36596940
Is Col5 the correct column for current stock?  It is CFC

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36597066

ok
it was wrong
Col13 instead of Col5
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36597069

Let me test , some thing is wrong..
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36602489

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
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36603583
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..!
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36975585
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A short article about problems I had with the new location API and permissions in Marshmallow
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now