Access 2010 report/SQL problem

We have encountered a problem after converting to Access 2010. I will state a problem summary here but I am attaching the details in the file that is attached with this question.

Summary...
Regaring a form we call "Fill Rates" after entering a "Ship Date" range, then entering a site code, and then putting an "x" a field called "PT Group" noting happens. The SQL code is created dynamically. Upon examing the code there are spaces that get inserted into the dynamically created code which is cauing this report not to run. This worked fine in Access 97. Please see the attached files for the details.
Weekly_fr_Frm_Screen.docx Shot is the 1st file that will show you what we enter.
Code_details.docx show the code where the spaces appear after the idh_ that is in red.

Please let me know if you can offer any suggestions.
Thanks.
Weekly-fr-Frm-Screen-Shot.docx
Code-details.docx
tesla764Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
We need to see the code that creates the sql.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
post the codes from the function that builds the sql statement
0
tesla764Author Commented:
The Code is the attached docx file.
Code.docx
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

tesla764Author Commented:

Option Compare Database
Option Explicit
Public data_val As Boolean

'Central Replication Database Prefix
Public Const CentralDBPrefix As String = "RBSQLDEV01QADCentral.dbo"
'Central Replication Database Environment Name
Public Const DBEnvironmentName As String = "ALP"

Function con_sql()

Dim ctype1 As String  'MVR
Dim typepos1 As Integer
Dim ctype2 As String
Dim typepos2 As Integer

'******************************************************************************************
'Data Entry validation
'******************************************************************************************
 
On Error GoTo err:



If Not IsNull(Forms!weekly_fr_frm!txttosite) Then
        If Nz(Forms!weekly_fr_frm!txtfromsite) > Nz(Forms!weekly_fr_frm!txttosite) Then
                MsgBox "From Site is higher than To Site", 48
                data_val = False
                Exit Function
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttocust) Then
        If Nz(Forms!weekly_fr_frm!txtfromcust) > Nz(Forms!weekly_fr_frm!txttocust) Then
                MsgBox "From Customer is higher than To Customer", 48
                data_val = False
                Exit Function
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoso) Then
        If Nz(Forms!weekly_fr_frm!txtfromso) > Nz(Forms!weekly_fr_frm!txttoso) Then
                MsgBox "From Sales Order is higher than To Sales Order", 48
                data_val = False
                Exit Function
        End If
End If


If Not IsNull(Forms!weekly_fr_frm!txttobp) Then
        If Nz(Forms!weekly_fr_frm!txtfrombp) > Nz(Forms!weekly_fr_frm!txttobp) Then
                MsgBox "From Buyer Planner is higher than To Buyer Planner", 48
                data_val = False
                Exit Function
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttopl) Then
            If Nz(Forms!weekly_fr_frm!txtfrompl) > Nz(Forms!weekly_fr_frm!txttopl) Then
                    MsgBox "From Product Line is higher than To Product Line", 48
                    Exit Function
                    data_val = False
            End If
End If


If Not IsNull(Forms!weekly_fr_frm!txttoct) Then
        If Nz(Forms!weekly_fr_frm!txtfromct) > Nz(Forms!weekly_fr_frm!txttoct) Then
                MsgBox "From Customer Type is higher than To Customer Type", 48
                Exit Function
                data_val = False
        End If
End If

'MVR  - more validations for Customer type
If Not IsNull(Forms!weekly_fr_frm!txttoct) And Not IsNull(Forms!weekly_fr_frm!txtfromct) Then
    If (Left(Forms!weekly_fr_frm!txtfromct, 1) = "*" And Right(Forms!weekly_fr_frm!txtfromct, 1) = "*") Or _
       (Left(Forms!weekly_fr_frm!txttoct, 1) = "*" And Right(Forms!weekly_fr_frm!txttoct, 1) = "*") Then
        MsgBox "Customer Type's can not have *'s on both side's", 48
        Exit Function
    End If
    If (Left(Forms!weekly_fr_frm!txtfromct, 1) = "*" And Right(Forms!weekly_fr_frm!txttoct, 1) = "*") Then
        MsgBox "Invalid position of *'s in Customer Type", 48
        Exit Function
    End If
    If Len(Forms!weekly_fr_frm!txtfromct) <> Len(Forms!weekly_fr_frm!txttoct) Then
        MsgBox "Length of From and To Customer Type is not equal", 48
        Exit Function
    End If
    If (Right(Forms!weekly_fr_frm!txtfromct, 2) = "**") Or (Right(Forms!weekly_fr_frm!txttoct, 2) = "**") Then
        MsgBox "Please enter only 1 * to the right of Customer Type", 48
        Exit Function
    End If
End If


If Not IsNull(Forms!weekly_fr_frm!txttoshipto) Then
        If Nz(Forms!weekly_fr_frm!txtfromshipto) > Nz(Forms!weekly_fr_frm!txttoshipto) Then
                MsgBox "From Ship To is higher than To Ship To", 48
                data_val = False
                Exit Function
                
        End If
End If


If Not IsNull(Forms!weekly_fr_frm!txttoItemnbr) Then
        If Nz(Forms!weekly_fr_frm!txtfromItemnbr) > Nz(Forms!weekly_fr_frm!txttoItemnbr) Then
                MsgBox "From Item Number is higher than To Item Number", 48
                data_val = False
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoItemtype) Then
        If Nz(Forms!weekly_fr_frm!txtfromItemtype) > Nz(Forms!weekly_fr_frm!txttoItemtype) Then
                MsgBox "From Item Type is higher than To Item Type", 48
                data_val = False
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttopo) Then
        If Nz(Forms!weekly_fr_frm!txtfrompo) > Nz(Forms!weekly_fr_frm!txttopo) Then
                MsgBox "From Purchase Order is higher than To Purchase Order", 48
                data_val = False
        End If
End If



If Not IsNull(Forms!weekly_fr_frm!txttosite) Then
    If IsNull(Forms!weekly_fr_frm!txtfromsite) Then
        MsgBox "From Site Must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttocust) Then
    If IsNull(Forms!weekly_fr_frm!txtfromcust) Then
        MsgBox "From Customer must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoso) Then
    If IsNull(Forms!weekly_fr_frm!txtfromso) Then
        MsgBox "From Sales Order must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttobp) Then
    If IsNull(Forms!weekly_fr_frm!txtfrombp) Then
        MsgBox "From Buyer Planner must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttopl) Then
    If IsNull(Forms!weekly_fr_frm!txtfrompl) Then
        MsgBox "From Product Line must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoct) Then
    If IsNull(Forms!weekly_fr_frm!txtfromct) Then
        MsgBox "From Customer Type must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoshipto) Then
    If IsNull(Forms!weekly_fr_frm!txtfromshipto) Then
        MsgBox "From Ship To must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoItemnbr) Then
    If IsNull(Forms!weekly_fr_frm!txtfromItemnbr) Then
        MsgBox "From Item Number must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoItemtype) Then
    If IsNull(Forms!weekly_fr_frm!txtfromItemtype) Then
        MsgBox "From Item Type must have a value", 48
        Exit Function
        data_val = False
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttopo) Then
    If IsNull(Forms!weekly_fr_frm!txtfrompo) Then
        MsgBox "From Item Purchase Order must have a value", 48
        data_val = False
        Exit Function
    End If
End If


If Not IsNull(Forms!weekly_fr_frm!txttosoldto) Then
    If IsNull(Forms!weekly_fr_frm!txtfromSoldto) Then
        MsgBox "From Sold To Must have a value", 48
        data_val = False
        Exit Function
    End If
End If


If Not IsNull(Forms!weekly_fr_frm!txttoregion) Then
    If IsNull(Forms!weekly_fr_frm!txtfromregion) Then
        MsgBox "From Region To Must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txtToPTGroup) Then
        If Nz(Forms!weekly_fr_frm!txtFromPTGroup) > Nz(Forms!weekly_fr_frm!txtToPTGroup) Then
                MsgBox "From PT Group is higher than To PT Group", 48
                data_val = False
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txtToABCCode) Then  'MVR
        If Nz(Forms!weekly_fr_frm!txtFromABCCode) > Nz(Forms!weekly_fr_frm!txtToABCCode) Then
                MsgBox "From ABC Code is higher than To ABC Code", 48
                data_val = False
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txtToUML) Then
        If Nz(Forms!weekly_fr_frm!txtFromUML) > Nz(Forms!weekly_fr_frm!txtToUML) Then
                MsgBox "From Upper Managed Level is higher than To Upper Managed Level", 48
                data_val = False
        End If
End If

   Dim dbresults As Database
   Dim strsql As String
   Dim rsresults As Recordset
   
   
   If IsNull(Forms!weekly_fr_frm!txtfromdate) Then
            MsgBox "Must enter a from date", 48
            data_val = False
            Exit Function
        ElseIf IsNull(Forms!weekly_fr_frm!txttodate) Then
            MsgBox "Must enter a todate", 48
            data_val = False
            Exit Function
            
    End If
   
   

 If Forms!weekly_fr_frm!frm_rpt <> 1 Then
 
        
    Else
      

End If
    
 

        If Forms!weekly_fr_frm!frm_rpt = 2 Then
              Dim mystr As String
              Dim to_day As String
              Dim cur_date As String
              
              Dim d_begdate As String
              Dim cur_day As String
              
              
              to_day = Forms!weekly_fr_frm!txtfromdate
              cur_day = Format(DateAdd("d", 0, to_day), "dddd")
             
          
                If cur_day <> "Sunday" Then
                    MsgBox "Must enter a valid Sunday from date and Saturday to date", 48
                    data_val = False
                    Exit Function
                Else
                             cur_day = Format(DateAdd("d", 0, Forms!weekly_fr_frm!txttodate), "dddd")
                 If cur_day <> "Saturday" Then
                           MsgBox "Must enter a valid Sunday from date and Saturday to date", 48
                           data_val = False
                           Exit Function
                End If
                
                End If
      
        End If



'******************************************************************************************
'End of Data Entry Validation                                                             *
'******************************************************************************************


' use the = sign when you can in place of like

If Forms!weekly_fr_frm!frm_rpt <> 3 Then


        Dim db As Database
        Dim sql As Variant
        Set db = CurrentDb
        Dim swdefault As Boolean
        Dim r As Integer
        Dim qdf_name As QueryDef
        Dim qdfcorr As QueryDef
        
        Set qdfcorr = db.CreateQueryDef("")
        Dim s_ord(18, 3)  'MVR
                
        ' Read values into array for bubble sorting sorting order
        
        
        
            s_ord(1, 1) = "site"
            s_ord(1, 2) = Nz(Forms!weekly_fr_frm!txt_s_site)
            s_ord(1, 3) = Nz(Forms!weekly_fr_frm!txtfromsite)
            
            s_ord(2, 1) = "Customer"
            s_ord(2, 2) = Nz(Forms!weekly_fr_frm!txt_s_cust)
            s_ord(2, 3) = Nz(Forms!weekly_fr_frm!txtfromcust)
            
            
            s_ord(3, 1) = "Sales_Order"
            s_ord(3, 2) = Nz(Forms!weekly_fr_frm!txt_s_so)
            s_ord(3, 3) = Nz(Forms!weekly_fr_frm!txtfromso)
        
        
            s_ord(4, 1) = "Buyer_Planner"
            s_ord(4, 2) = Nz(Forms!weekly_fr_frm!txt_s_bp)
            s_ord(4, 3) = Nz(Forms!weekly_fr_frm!txtfrombp)
            
            
            s_ord(5, 1) = "Product_Line"
            s_ord(5, 2) = Nz(Forms!weekly_fr_frm!txt_s_prodline)
            s_ord(5, 3) = Nz(Forms!weekly_fr_frm!txtfrompl)
            
            
            
            s_ord(6, 1) = "customer_type"
            s_ord(6, 2) = Nz(Forms!weekly_fr_frm!txt_s_ct)
            s_ord(6, 3) = Nz(Forms!weekly_fr_frm!txtfromct)
            
            
            s_ord(7, 1) = "Ship_To"
            s_ord(7, 2) = Nz(Forms!weekly_fr_frm!txt_s_st)
            s_ord(7, 3) = Nz(Forms!weekly_fr_frm!txtfromshipto)
            
            
            s_ord(8, 1) = "Part"
            s_ord(8, 2) = Nz(Forms!weekly_fr_frm!txt_s_itm_nbr)
            s_ord(8, 3) = Nz(Forms!weekly_fr_frm!txtfromItemnbr)
            
            
            
            
            s_ord(9, 1) = "Part_type"
            s_ord(9, 2) = Nz(Forms!weekly_fr_frm!txt_s_itm_type)
            s_ord(9, 3) = Nz(Forms!weekly_fr_frm!txtfromItemtype)
            
            
            
            s_ord(10, 1) = "Purchase_Order"
            s_ord(10, 2) = Nz(Forms!weekly_fr_frm!txt_s_po)
            s_ord(10, 3) = Nz(Forms!weekly_fr_frm!txtfrompo)
            
            
            s_ord(11, 1) = "date"
            s_ord(11, 2) = Nz(Forms!weekly_fr_frm!txt_s_date)
            s_ord(11, 3) = Nz(Forms!weekly_fr_frm!txtfromdate)
            
            s_ord(12, 1) = "Sold_To"
            s_ord(12, 2) = Nz(Forms!weekly_fr_frm!txt_s_soldto)
            s_ord(12, 3) = Nz(Forms!weekly_fr_frm!txtfromSoldto)
            
            s_ord(13, 1) = "cm_region"
            s_ord(13, 2) = Nz(Forms!weekly_fr_frm!txt_s_region)
            s_ord(13, 3) = Nz(Forms!weekly_fr_frm!txtfromregion)
            
            s_ord(14, 1) = "Invoice"
            s_ord(14, 2) = Nz(Forms!weekly_fr_frm!txt_s_Invoice)
            s_ord(14, 3) = Nz(Forms!weekly_fr_frm!txtfromInvoice)
            
            s_ord(15, 1) = "DesignGroup"
            s_ord(15, 2) = Nz(Forms!weekly_fr_frm!txt_s_DsgnGroup)
            s_ord(15, 3) = Nz(Forms!weekly_fr_frm!txtToDesignGroup)
        
            s_ord(16, 1) = "PT_Group"
            s_ord(16, 2) = Nz(Forms!weekly_fr_frm!txt_s_PTGroup)
            s_ord(16, 3) = Nz(Forms!weekly_fr_frm!txtToPTGroup)
        
            s_ord(17, 1) = "ABC_Code"   'MVR
            s_ord(17, 2) = Nz(Forms!weekly_fr_frm!txt_s_ABC)
            s_ord(17, 3) = Nz(Forms!weekly_fr_frm!txtToABCCode)
            
            s_ord(18, 1) = "UpperManagedLevel"
            s_ord(18, 2) = Nz(Forms!weekly_fr_frm!txt_s_UML)
            s_ord(18, 3) = Nz(Forms!weekly_fr_frm!txtToUML)
            
        Dim X As Integer
        ReDim N(18, 2)    'MVR
        Dim cnt As Integer
        
        'Search for fields that have a value
        
        
        cnt = 0
        swdefault = False
        
        For X = 1 To 18     'MVR
            If (Trim(s_ord(X, 2))) <> "" Then
                cnt = cnt + 1
                N(cnt, 1) = s_ord(X, 1)
                N(cnt, 2) = (Trim(s_ord(X, 2)))
                swdefault = True
            Else
              
            End If
        Next X
        
        
      If swdefault = False Then
       For X = 1 To 18  'MVR
            If (Trim(s_ord(X, 3))) <> "" Then
                cnt = cnt + 1
                N(cnt, 1) = s_ord(X, 1)
                N(cnt, 2) = cnt
                
            Else
              
            End If
        Next X
      
      
      
      End If
      
              
        
        
        
        
        
        
        
        'Bubble sort below in order
        
        Dim p As Integer
        Dim l As Integer
        Dim s As Integer
        Dim s1 As Integer
        Dim J As Integer
        Dim t As Integer
        Dim t_name As String
        Dim res(13, 2)
        
        p = cnt - 1
        For l = 1 To p
            s = N(l, 2)
            t_name = N(l, 1)
            s1 = l
                For J = l + 1 To cnt
                    If Trim(s) < Trim(N(J, 2)) Then GoTo 40
                          s = Trim(N(J, 2))
                          t_name = N(J, 1)
                          s1 = J
                    
40:                Next J
                t = Trim(N(s1, 2))
                N(s1, 2) = Trim(N(l, 2))
                N(s1, 1) = N(l, 1)
                N(l, 2) = t
                N(l, 1) = t_name
               
            Next l
               
            
        ReDim TBLDSC(18, 1)  'MVR
        Dim ncnt As Integer
        Dim zz As Integer
        ncnt = 0
        For zz = 1 To 18   'MVR
        
        Select Case zz
        
        
                Case 1
                        If Not IsNull(Forms!weekly_fr_frm!txtfromdate) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "date"
                        End If
                Case 2
                        If Not IsNull(Forms!weekly_fr_frm!txtfromsite) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Site"
                        End If
                Case 3
                        If Not IsNull(Forms!weekly_fr_frm!txtfromcust) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Customer"
                        End If
                Case 4
                        If Not IsNull(Forms!weekly_fr_frm!txtfromso) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Sales_Order"
                        End If
                Case 5
                        If Not IsNull(Forms!weekly_fr_frm!txtfrombp) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Buyer_Planner"
                        End If
                Case 6
                        If Not IsNull(Forms!weekly_fr_frm!txtfrompl) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Product_Line"
                        End If
                Case 7
                        If Not IsNull(Forms!weekly_fr_frm!txtfromct) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Customer_Type"
                        End If
                Case 8
                        If Not IsNull(Forms!weekly_fr_frm!txtfromshipto) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Ship_To"
                        End If
                Case 9
                        If Not IsNull(Forms!weekly_fr_frm!txtfromItemnbr) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Part"
                        End If
                Case 10
                        If Not IsNull(Forms!weekly_fr_frm!txtfromItemtype) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Part_Type"
                        End If
                Case 11
                        If Not IsNull(Forms!weekly_fr_frm!txtfrompo) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Purchase_Order"
                        End If
                Case 12
                       If Not IsNull(Forms!weekly_fr_frm!txtfromSoldto) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Sold_To"
                        End If
                
                Case 13
                       If Not IsNull(Forms!weekly_fr_frm!txtfromregion) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "cm_region"
                        End If
        
                Case 14
                       If Not IsNull(Forms!weekly_fr_frm!txtfromInvoice) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "Invoice"
                        End If
        
                Case 15
                       If Not IsNull(Forms!weekly_fr_frm!txtFromDesignGroup) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "DesignGroup"
                        End If
                Case 16
                       If Not IsNull(Forms!weekly_fr_frm!txtFromPTGroup) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "PTGroup"
                        End If
                Case 17   'MVR
                       If Not IsNull(Forms!weekly_fr_frm!txtFromABCCode) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "ABC_Code"
                        End If
                Case 18
                       If Not IsNull(Forms!weekly_fr_frm!txtFromUML) Then
                            ncnt = ncnt + 1
                            TBLDSC(ncnt, 1) = "UpperManagedLevel"
                        End If
        End Select
        
                    
        Next zz
        
        Dim xx As Integer
                                  
                        
        '*************************************************************************************
        '                             Match for pt_wk_fr                                     *
        '*************************************************************************************
        
        Dim ptrs As Recordset
        Dim rsncnt As Recordset
        Dim ncntsql As String
        Dim pt_wk_sql As String
        Dim pt_sql As String
        Dim pt_wk_bo As Boolean
        Dim ll As Integer
        
        
        Set db = CurrentDb
        
        pt_wk_bo = True
            For ll = 1 To ncnt
            ncntsql = "select * from xrc_table where g_names =" & "'" & TBLDSC(ll, 1) & "'" & " and Table_Name = 'pt_wk_fr' and Freq= 'W'"
             Set rsncnt = db.OpenRecordset(ncntsql, dbOpenDynaset)
                If rsncnt.RecordCount = 0 Then
                    pt_wk_bo = False
                Else
                   
                End If
         Next ll
        
        If Forms!weekly_fr_frm!frm_rpt = 2 Then
        
                        pt_wk_sql = pt_wk_sql & "Select "
                      
                           For l = 1 To cnt
                              pt_sql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'pt_wk_fr')" & " and (sql_freq = 'SEL' or sql_freq = 'B')"
                              
                              Set ptrs = db.OpenRecordset(pt_sql, dbOpenDynaset)
                        If ptrs.RecordCount = 0 Then
                              pt_wk_bo = False
                        Else
                              ptrs.MoveFirst
                              pt_wk_sql = pt_wk_sql & ptrs!l_names & ","
                              
                        End If
                        
                           Next l
                        If pt_wk_bo = True Then
                        
                                        pt_wk_sql = pt_wk_sql & " sum( pt_wk_lines_ord) as lines_Ordered,"
                                        pt_wk_sql = pt_wk_sql & " sum( pt_wk_lines_ship)as lines_Shipped,"
                                    ''''''    pt_wk_sql = pt_wk_sql & " cast (cast (sum( pt_wk_lines_ship)as decimal (11,2))/cast (sum( pt_wk_lines_ord) as decimal (11,2))as "
                                    ''''''      HEAT 71677
                                        pt_wk_sql = pt_wk_sql & " cast (     case when cast (sum( pt_wk_lines_ord) as decimal (11,2))=0 then 0 else cast (sum( pt_wk_lines_ship) as decimal (11,2))/cast (sum( pt_wk_lines_ord) as decimal (11,2)) end      as "
                                        pt_wk_sql = pt_wk_sql & " decimal(11,2)) as lines_fr,"
                                        pt_wk_sql = pt_wk_sql & " sum(pt_wk_units_ord) as Units_Ordered,"
                                        pt_wk_sql = pt_wk_sql & "sum(pt_wk_units_ship) as Units_Shipped,"
                                     ''''    pt_wk_sql = pt_wk_sql & "cast(     cast (sum(pt_wk_units_ship)/sum(pt_wk_units_ord)as decimal (11,2))      as decimal (11,2))as units_fr,"
                                     ''''       HEAT 71677
                                        pt_wk_sql = pt_wk_sql & "cast(     case when sum(pt_wk_units_ord)=0 then 0 else cast (sum(pt_wk_units_ship)/sum(pt_wk_units_ord) as decimal (11,2)) end     as decimal (11,2))as units_fr,"
                                        pt_wk_sql = pt_wk_sql & "sum (pt_wk_dollars_ord) as dollars_Ordered,"
                                        pt_wk_sql = pt_wk_sql & "sum (pt_wk_dollars_ship)as dollars_shipped,"
                                     '''''   pt_wk_sql = pt_wk_sql & "cast (sum(pt_wk_dollars_ship)/sum(pt_wk_dollars_ord)as decimal(11,2)) as dollars_fr"
                                     '''''      HEAT 71677
                                        pt_wk_sql = pt_wk_sql & "cast (      case when sum(pt_wk_dollars_ord)=0 then 0 else sum(pt_wk_dollars_ship)/sum(pt_wk_dollars_ord) end     as decimal(11,2)) as dollars_fr"
                                        pt_wk_sql = pt_wk_sql & " From pt_wk_fr "
                                        
                                           
                                        'date
                                        
                                        If Not IsNull(Forms!weekly_fr_frm!txtfromdate) Then
                                            If Not IsNull(Forms!weekly_fr_frm!txttodate) Then
                                                    pt_wk_sql = pt_wk_sql & " Where pt_wk_week_end between" & "'" & Forms!weekly_fr_frm!txtfromdate & "'" & " And" & "'" & Forms!weekly_fr_frm!txttodate & "'"
                                            Else
                                                    pt_wk_sql = pt_wk_sql & " Where pt_wk_week_end =" & "'" & Forms!weekly_fr_frm!txtfromdate & "'"
                                            End If
                                        End If
                                        
                                        
                                        'Part
                                        
                                        If Not IsNull(Forms!weekly_fr_frm!txtfromItemnbr) Then
                                            If Not IsNull(Forms!weekly_fr_frm!txttoItemnbr) Then
                                                pt_wk_sql = pt_wk_sql & " and pt_wk_part between" & "'" & Forms!weekly_fr_frm!txtfromItemnbr & "'" & " and" & "'" & Forms!weekly_fr_frm!txttoItemnbr & "'"
                                            Else
                                                 pt_wk_sql = pt_wk_sql & " and pt_wk_part =" & "'" & Forms!weekly_fr_frm!txtfromItemnbr & "'"
                                            End If
                                        End If
                                        
                                        'Part Type
                                        
                                        If Not IsNull(Forms!weekly_fr_frm!txtfromItemtype) Then
                                            If Not IsNull(Forms!weekly_fr_frm!txttoItemtype) Then
                                                pt_wk_sql = pt_wk_sql & " and pt_wk_part_type between" & "'" & Forms!weekly_fr_frm!txtfromItemtype & "%'" & " and" & "'" & Forms!weekly_fr_frm!txttoItemtype & "%'"
                                            Else
                                                 pt_wk_sql = pt_wk_sql & " and pt_wk_part_type like " & "'" & Forms!weekly_fr_frm!txtfromItemtype & "%'"
                                            End If
                                        End If
                                        
                                        'Product Line
                                        
                                        If Not IsNull(Forms!weekly_fr_frm!txtfrompl) Then
                                            If Not IsNull(Forms!weekly_fr_frm!txttopl) Then
                                                pt_wk_sql = pt_wk_sql & " and pt_wk_prod_line between" & "'" & Forms!weekly_fr_frm!txtfrompl & "'" & " and" & "'" & Forms!weekly_fr_frm!txttopl & "'"
                                            Else
                                                 pt_wk_sql = pt_wk_sql & " and pt_wk_prod_line =" & "'" & Forms!weekly_fr_frm!txtfrompl & "'"
                                            End If
                                        End If
                                        
                                        'Buyer Planner
                                        
                                        If Not IsNull(Forms!weekly_fr_frm!txtfrombp) Then
                                            If Not IsNull(Forms!weekly_fr_frm!txttobp) Then
                                                pt_wk_sql = pt_wk_sql & " and pt_wk_buyer between" & "'" & Forms!weekly_fr_frm!txtfrombp & "'" & " and" & "'" & Forms!weekly_fr_frm!txttobp & "'"
                                            Else
                                                 pt_wk_sql = pt_wk_sql & " and pt_wk_Buyer =" & "'" & Forms!weekly_fr_frm!txtfrombp & "'"
                                            End If
                                        End If
                                        
                                        'Site
                                        
                                        If Not IsNull(Forms!weekly_fr_frm!txtfromsite) Then
                                            If Not IsNull(Forms!weekly_fr_frm!txttosite) Then
                                                pt_wk_sql = pt_wk_sql & " and pt_wk_site between" & "'" & Forms!weekly_fr_frm!txtfromsite & "'" & " and" & "'" & Forms!weekly_fr_frm!txttosite & "'"
                                            Else
                                                 pt_wk_sql = pt_wk_sql & " and pt_wk_site =" & "'" & Forms!weekly_fr_frm!txtfromsite & "'"
                                            End If
                                        End If
                                        
                                        
                                         'Design Group
                                        
                                        If Not IsNull(Forms!weekly_fr_frm!txtFromDesignGroup) Then
                                            If Not IsNull(Forms!weekly_fr_frm!txtToDesignGroup) Then
                                                pt_wk_sql = pt_wk_sql & " and pt_dsgn_grp between" & "'" & Forms!weekly_fr_frm!txtFromDesignGroup & "'" & " and" & "'" & Forms!weekly_fr_frm!txtToDesignGroup & "'"
                                            Else
                                                pt_wk_sql = pt_wk_sql & " and pt_dsgn_grp =" & "'" & Forms!weekly_fr_frm!txtToDesignGroup & "'"
                                            End If
                                        End If
                                        
                                        'Group by
                                        
                                        pt_wk_sql = pt_wk_sql & "group by " 'pt_wk_week_end"
                                        
                                          For l = 1 To cnt
                                              pt_sql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'pt_wk_fr') and (sql_freq = 'B' or 'GB')"
                                              Set ptrs = db.OpenRecordset(pt_sql, dbOpenDynaset)
                                              ptrs.MoveFirst
                                              'pt_wk_sql = pt_wk_sql & "," & ptrs!l_names
                                              pt_wk_sql = pt_wk_sql & ptrs!l_names & ","
                                           Next l
                                        pt_wk_sql = Mid(pt_wk_sql, 1, Len(pt_wk_sql) - 1)
                                    
                                        pt_wk_sql = pt_wk_sql & " With rollup"
                                        
                                        CurrentDb.QueryDefs("Catch_all").sql = pt_wk_sql
                                        
                                        DoCmd.OpenQuery "Catch_all"
                        Exit Function
                    
                    End If
        End If
        
        
            
        '*************************************************************************************
        '                             End for pt_wk_fr                                       *
        '*************************************************************************************
            
        '*************************************************************************************
        '                           Match for orig_inv_fr                                    *
        '*************************************************************************************
        Dim invrs As Recordset
        Dim inv_fr_bo As Boolean
        Dim xrcsql As String
        
        
        
        inv_fr_bo = True
        
        If Forms!weekly_fr_frm!frm_rpt = 2 Then
                             sql = sql & " Select      "
                
                  For l = 1 To cnt
                      xrcsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'orig_inv_fr' and Freq = 'W') and (sql_freq = 'b' or sql_freq = 'SEL') "
                      Set invrs = db.OpenRecordset(xrcsql, dbOpenDynaset)
                If invrs.RecordCount = 0 Then
                        inv_fr_bo = False
                Else
                      invrs.MoveFirst
                      sql = sql & invrs!l_names & ","
                End If
                Next l
        
                
                  For ll = 1 To ncnt
                    ncntsql = "select * from xrc_table where g_names =" & "'" & TBLDSC(ll, 1) & "'" & " and Table_Name = 'orig_inv_fr' and Freq = 'W'"
                     Set rsncnt = db.OpenRecordset(ncntsql, dbOpenDynaset)
                        If rsncnt.RecordCount = 0 Then
                            inv_fr_bo = False
                        Else
                          
                        End If
                 Next ll
               
               
        
        ElseIf Forms!weekly_fr_frm!frm_rpt = 1 Then
        
        
                sql = sql & " Select      "
                
                  For l = 1 To cnt
                      xrcsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'orig_inv_fr' and Freq = 'D') and (sql_freq = 'B' or 'SEL') "
                      Set invrs = db.OpenRecordset(xrcsql, dbOpenDynaset)
                If invrs.RecordCount = 0 Then
                        inv_fr_bo = False
                Else
                      invrs.MoveFirst
                      sql = sql & invrs!l_names & ","
                End If
                Next l
        
               ' inv_fr_bo = True
                  For ll = 1 To ncnt
                    ncntsql = "select * from xrc_table where (g_names =" & "'" & TBLDSC(ll, 1) & "'" & " and Table_Name = 'orig_inv_fr') and (freq = 'D' or freq = 'SEL')"
                     Set rsncnt = db.OpenRecordset(ncntsql, dbOpenDynaset)
                        If rsncnt.RecordCount = 0 Then
                            inv_fr_bo = False
                        Else
                           
                        End If
                 Next ll
                
        End If
    If Forms!weekly_fr_frm!frm_rpt <> 4 Then
    
        If inv_fr_bo = True Then
                            sql = sql & " sum(inv_lines_ord) as lines_ordered,"
                            sql = sql & " sum(inv_lines_ship) as Lines_Shipped,"
                            sql = sql & " case when sum(inv_lines_ord)= 0 then 0 else cast (cast (sum(inv_lines_ship)as decimal (11,3))/cast (sum(inv_lines_ord)as decimal(11,3)) as decimal (11,3)) end as "
                            sql = sql & "lines_fr ,"
                            sql = sql & " sum(inv_units_ord) as units_ordered,"
                            sql = sql & " sum(inv_units_ship) as units_shipped,"
                            sql = sql & " case when sum (inv_units_ord) = 0 then 0 else cast(cast (sum(inv_units_ship)/sum(inv_units_ord)as decimal (11,3)) as decimal(11,3)) end as units_fr,"
                            sql = sql & " sum (inv_dollars_ord) as dollars_ordered,"
                            sql = sql & " sum (inv_dollars_ship) as dollars_shipped,"
                            sql = sql & " case when sum (inv_dollars_ord) = 0 then 0 else cast(cast (sum(inv_dollars_ship)/sum(inv_dollars_ord)as decimal(11,2)) as decimal (11,2)) end as dollars_fr"
                            sql = sql & "  From orig_inv_fr "
                            
                            'Ship Date
                            
                            
                            
                            If Not IsNull(Forms!weekly_fr_frm!txtfromdate) Then
                                If Not IsNull(Forms!weekly_fr_frm!txttodate) Then
                                        sql = sql & " Where inv_ship_date between" & "'" & Forms!weekly_fr_frm!txtfromdate & "'" & " and" & "'" & Forms!weekly_fr_frm!txttodate & "'"
                                Else
                                        sql = sql & " Where inv_ship_date= " & "'" & Forms!weekly_fr_frm!txtfromdate & "'"
                                End If
                            End If
                                
                                
                            'Sales Number
                                
                            If Not IsNull(Forms!weekly_fr_frm!txtfromso) Then
                                If Not IsNull(Forms!weekly_fr_frm!txttoso) Then
                                    sql = sql & " and inv_sales_nbr between" & "'" & Forms!weekly_fr_frm!txtfromso & "'" & " and" & "'" & Forms!weekly_fr_frm!txttoso & "'"
                                Else
                                     sql = sql & " and inv_sales_nbr= " & "'" & Forms!weekly_fr_frm!txtfromso & "'"
                                End If
                            End If
                                
                            'site
                                
                            If Not IsNull(Forms!weekly_fr_frm!txtfromsite) Then
                                If Not IsNull(Forms!weekly_fr_frm!txttosite) Then
                                    sql = sql & " and inv_site between" & "'" & Forms!weekly_fr_frm!txtfromsite & "'" & " and" & "'" & Forms!weekly_fr_frm!txttosite & "'"
                                Else
                                     sql = sql & " and inv_site= " & "'" & Forms!weekly_fr_frm!txtfromsite & "'"
                                End If
                            End If
                                
                                
                            'Customer
                            
                            If Not IsNull(Forms!weekly_fr_frm!txtfromcust) Then
                                If Not IsNull(Forms!weekly_fr_frm!txttocust) Then
                                    sql = sql & " and inv_cust between" & "'" & Forms!weekly_fr_frm!txtfromcust & "'" & " and" & "'" & Forms!weekly_fr_frm!txttocust & "'"
                                Else
                                     sql = sql & " and inv_cust= " & "'" & Forms!weekly_fr_frm!txtfromcust & "'"
                                End If
                            End If
                                
                           'Bill to
                           
                               If Not IsNull(Forms!weekly_fr_frm!txtfromSoldto) Then
                                If Not IsNull(Forms!weekly_fr_frm!txttosoldto) Then
                                    sql = sql & " and ih_bill between" & "'" & Forms!weekly_fr_frm!txtfromSoldto & "'" & " and" & "'" & Forms!weekly_fr_frm!txttosoldto & "'"
                                Else
                                     sql = sql & " and ih_bill= " & "'" & Forms!weekly_fr_frm!txttosoldto & "'"
                                End If
                            End If
                           
                           
                           
                            'Customer Type
                            
                            'If Not IsNull(Forms!weekly_fr_frm!txtfromct) Then
                            '    If Not IsNull(Forms!weekly_fr_frm!txttoct) Then
                            '        sql = sql & " and inv_type between" & "'" & Forms!weekly_fr_frm!txtfromct & "'" & " and" & "'" & Forms!weekly_fr_frm!txttoct & "'"
                            '    Else
                            '         sql = sql & " and inv_type= " & "'" & Forms!weekly_fr_frm!txtfromct & "'"
                            '    End If
                            'End If
                            
                            
                            'MVR modified code
                            If Not IsNull(Forms!weekly_fr_frm!txtfromct) Then
                                If Not IsNull(Forms!weekly_fr_frm!txttoct) Then
                                    If Right(Forms!weekly_fr_frm!txtfromct, 1) = "*" Or Left(Forms!weekly_fr_frm!txtfromct, 1) = "*" Then
                                        ctype1 = ""
                                        typepos1 = 0
                                        ctype2 = ""
                                        typepos2 = 0
                                        parse_cust_type Forms!weekly_fr_frm!txtfromct, ctype1, typepos1
                                        parse_cust_type Forms!weekly_fr_frm!txttoct, ctype2, typepos2
                    
                                        If typepos1 = 0 Then
                                            sql = sql & " and substring(inv_type,1,len('" & ctype1 & "')) between " & "'" & ctype1 & "'" & " and" & "'" & ctype2 & "'"
                                        Else
                                            sql = sql & " and substring(inv_type," & typepos1 & "+ 1,len('" & ctype1 & "')) between " & "'" & ctype1 & "'" & " and" & "'" & ctype2 & "'"
                                        End If
                                    Else
                                        sql = sql & " and inv_type between " & "'" & Forms!weekly_fr_frm!txtfromct & "'" & " and" & "'" & Forms!weekly_fr_frm!txttoct & "'"
                                    End If
                                Else
                                    sql = sql & " and inv_type =" & "'" & Forms!weekly_fr_frm!txtfromct & "'"
                                End If
                            End If
                            
                            
                             'Invoice Number
                            
                            If Not IsNull(Forms!weekly_fr_frm!txtfromInvoice) Then
                                If Not IsNull(Forms!weekly_fr_frm!txtToInvoice) Then
                                    sql = sql & " and inv_inv_nbr between" & "'" & Forms!weekly_fr_frm!txtfromInvoice & "'" & " and" & "'" & Forms!weekly_fr_frm!txtToInvoice & "'"
                                Else
                                     sql = sql & " and inv_Inv_nbr = " & "'" & Forms!weekly_fr_frm!txtfromInvoice & "'"
                                End If
                            End If
                            
                          
                            
                            
                             sql = sql & "group by " 'inv_ship_date"
                             
                             
        
                                If Forms!weekly_fr_frm!frm_rpt = 2 Then
                                
                                              For l = 1 To cnt
                                                  xrcsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'orig_inv_fr' and Freq = 'W') AND (Sql_freq = 'GP' or 'B')"
                                                  Set invrs = db.OpenRecordset(xrcsql, dbOpenDynaset)
                                                  invrs.MoveFirst
                                                  sql = sql & invrs!l_names & ","
                                               Next l
                                                    'joe
                                              sql = Mid(sql, 1, Len(sql) - 1)
                                ElseIf Forms!weekly_fr_frm!frm_rpt = 1 Then
                                            For l = 1 To cnt
                                                  xrcsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'orig_inv_fr' and Freq = 'D') and (sql_freq = 'B' or 'GP')"
                                                  Set invrs = db.OpenRecordset(xrcsql, dbOpenDynaset)
                                                  invrs.MoveFirst
                                                  sql = sql & invrs!l_names & ","
                                            Next l
                                                  sql = Mid(sql, 1, Len(sql) - 1)
                                              
                                           
                                End If
                                
                                              
                            sql = sql & " With rollup"
                            CurrentDb.QueryDefs("Catch_all").sql = sql
                            DoCmd.OpenQuery "Catch_all", acNormal
                            Exit Function
                            
                End If
    End If
    
        '*************************************************************************************
        '                           End  for  orig_inv_fr                                    *
        '*************************************************************************************
        Dim rsdet As Recordset
        Dim detsql As String
        Dim swdet As Boolean
        Dim mstsql As String
        
            
            
                  
      
        
        'Check for daily for weekly
        
        If Forms!weekly_fr_frm!frm_rpt = 1 Then
        
        mstsql = mstsql & "Select "
                    For l = 1 To cnt
                             detsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'detail' and freq ='D') and (sql_freq = 'B' or 'GP')"
                             Set rsdet = db.OpenRecordset(detsql, dbOpenDynaset)
                       If rsdet.RecordCount = 0 Then
                             swdet = False
                       Else
                             rsdet.MoveFirst
                             mstsql = mstsql & rsdet!l_names & ","
                       End If
                       
                      Next l
                      
        ElseIf Forms!weekly_fr_frm!frm_rpt = 2 Then
        
       '     If Forms!weekly_fr_frm!frm_rpt <> 4 Then
            
            
            
                   ' For l = 1 To cnt
                   '          detsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'detail' and freq ='W') and (sql_freq = 'B' or 'GP')"
                   '          Set rsdet = db.OpenRecordset(detsql, dbOpenDynaset)
                   '    If rsdet.RecordCount = 0 Then
                   '          swdet = False
                   '    Else
                   '          rsdet.MoveFirst
                   '          mstsql = mstsql & rsdet!l_names & ","
                   '    End If
                   '
                   '   Next l
        '    End If
            
        
     End If
        
        
           If Forms!weekly_fr_frm!frm_rpt = 4 Then
           
             mstsql = mstsql & "select "
      
           For l = 1 To cnt
                             detsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'detail' and freq ='d') and (sql_freq = 'B' or 'GP') and l_names <> 'ih_ship_date'"
                             Set rsdet = db.OpenRecordset(detsql, dbOpenDynaset)
                       If rsdet.RecordCount = 0 Then
                             swdet = False
                       Else
                             rsdet.MoveFirst
                             mstsql = mstsql & rsdet!l_names & ","
                       End If
                       
                      Next l
        
       ElseIf Forms!weekly_fr_frm!frm_rpt = 2 Then
       
        mstsql = mstsql & "select "
      
           For l = 1 To cnt
                             detsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'detail' and freq ='W') and (sql_freq = 'B' or 'GP')"
                             Set rsdet = db.OpenRecordset(detsql, dbOpenDynaset)
                       If rsdet.RecordCount = 0 Then
                             swdet = False
                       Else
                             rsdet.MoveFirst
                             mstsql = mstsql & rsdet!l_names & ","
                       End If
                       
                      Next l
        
        
        
        
          End If
            
      
        
        
        
        
      ' Jk
      ' Modifications for Sold to and Customer Region must go here
      
        mstsql = mstsql & "count(*)as lines_Ordered,"
        mstsql = mstsql & "sum(case idh_qty_ship "
        mstsql = mstsql & " when 0 then 0   else 1 "
        mstsql = mstsql & "end)as lines_shipped, "
        mstsql = mstsql & " cast (sum(case idh_qty_ship "
        mstsql = mstsql & "when 0 then 0 "
        mstsql = mstsql & "else 1 "
        mstsql = mstsql & "end) * 100 /  "
        mstsql = mstsql & " nullif(count(*),0) * .01 as decimal (20,2))as lines_Fr,  "
        mstsql = mstsql & "cast(nullif(sum(idh_qty_ord),0)as decimal (15,3)) as Units_Ordered,"
        mstsql = mstsql & "cast(nullif(sum(idh_qty_ship),0)as decimal (15,3)) as Units_ship,"
        mstsql = mstsql & "cast (sum(idh_qty_ship) *100 / "
        mstsql = mstsql & "nullif(sum(case "
        mstsql = mstsql & "when idh_qty_ord < idh_qty_ship "
        mstsql = mstsql & "then idh_qty_ship "
        mstsql = mstsql & "Else: idh_qty_ord "
        mstsql = mstsql & "end), 0) * .01 as decimal (10,3)) as Units_Fr, "
        mstsql = mstsql & "cast (sum(idh_qty_ord  * idh_price) as money ) as Dollars_Ordered, "
        mstsql = mstsql & "cast (sum(idh_qty_ship * idh_price) as money) as Dollars_Shipped, "
        mstsql = mstsql & "cast (sum(idh_qty_ship * idh_price) * 100 / "
        mstsql = mstsql & "nullif(sum(case "
        mstsql = mstsql & "When idh_qty_ord * idh_price < idh_qty_ship * idh_price "
        mstsql = mstsql & "Then idh_qty_ship * idh_price "
        mstsql = mstsql & "else idh_qty_ord * idh_price "
        mstsql = mstsql & "end), 0)* .01 as decimal (11,2)) as Dollars_FR "
        mstsql = mstsql & " FROM ih_hist INNER JOIN idh_hist ON ih_hist.ih_inv_nbr = idh_hist.idh_inv_nbr AND ih_hist.ih_nbr = idh_hist.idh_nbr INNER JOIN pt_mstr ON idh_hist.idh_part = pt_mstr.pt_part INNER JOIN Cm_mstr ON ih_hist.ih_cust = Cm_mstr.cm_addr"
        mstsql = mstsql & " LEFT JOIN " & CentralDBPrefix & ".xRefManagedLevels x ON x.ManagedLevel = Left(pt_part_type,4) "
        mstsql = mstsql & " LEFT JOIN " & CentralDBPrefix & ".UpperManagedLevels u ON u.UpperManagedLevelID = x.UpperManagedLevelID "
        
        mstsql = mstsql & " WHERE ih__qadc02   = 'O' "
        mstsql = mstsql & " and (idh_type is null or idh_type != 'M') "
        mstsql = mstsql & "and idh_qty_ord  > 0 "
        mstsql = mstsql & "and pt_part_type not like '0597%' "
        mstsql = mstsql & "   and (ih__chr09 != 'e'  or ih__chr09 is null) "
        
        
        
        
        
        'Removed on 10/1/2002
        'mstsql = mstsql & "cast (sum(idh_qty_ship) as decimal (11,3)) as Units_Shipped,  "
        
        
        'mstsql = mstsql & " FROM  ih_hist INNER JOIN idh_hist ON ih_hist.ih_inv_nbr = idh_hist.idh_inv_nbr "
        'mstsql = mstsql & " AND ih_hist.ih_nbr = idh_hist.idh_nbr "
        'mstsql = mstsql & " INNER JOIN pt_mstr ON idh_hist.idh_part = pt_mstr.pt_part "
        'mstsql = mstsql & " ON IH_HIST.IH_CUST = CM_MSTR.CM_ADDR "
        'mstsql = mstsql & " INNER JOIN Cm_mstr ON ih_hist.ih_cust = Cm_mstr.cm_addr "
        
        'Ship Date
        
        If Not IsNull(Forms!weekly_fr_frm!txtfromdate) Then
            If Not IsNull(Forms!weekly_fr_frm!txttodate) Then
                    mstsql = mstsql & " and ih_inv_date between" & "'" & Forms!weekly_fr_frm!txtfromdate & "'" & " and" & "'" & Forms!weekly_fr_frm!txttodate & "'"
                        
            Else
                    mstsql = mstsql & " and ih_inv_date = " & "'" & Forms!weekly_fr_frm!txtfromdate & "'"
            End If
        End If
        
        mstsql = mstsql & "and (idh_type is null or idh_type != 'M') "
        mstsql = mstsql & "and idh_qty_ord > 0 "
        
        'Site
        
        If Not IsNull(Forms!weekly_fr_frm!txtfromsite) Then
            If Not IsNull(Forms!weekly_fr_frm!txttosite) Then
                mstsql = mstsql & " and ih__qadc01 between" & "'" & Forms!weekly_fr_frm!txtfromsite & "'" & " and" & "'" & Forms!weekly_fr_frm!txttosite & "'"
            Else
                  mstsql = mstsql & " and ih__qadc01 like" & "'" & Forms!weekly_fr_frm!txtfromsite & "'"
            End If
        End If
        
        'Customer
        
        If Not IsNull(Forms!weekly_fr_frm!txtfromcust) Then
            If Not IsNull(Forms!weekly_fr_frm!txttocust) Then
                mstsql = mstsql & " and ih_cust between" & "'" & Forms!weekly_fr_frm!txtfromcust & "'" & " and" & "'" & Forms!weekly_fr_frm!txttocust & "'"
            Else
                 mstsql = mstsql & " and ih_cust like" & "'" & Forms!weekly_fr_frm!txtfromcust & "'"
            End If
        End If
        
        'Product line
        
        If Not IsNull(Forms!weekly_fr_frm!txtfrompl) Then
            If Not IsNull(Forms!weekly_fr_frm!txttopl) Then
                mstsql = mstsql & " and idh_prodline between" & "'" & Forms!weekly_fr_frm!txtfrompl & "'" & " and" & "'" & Forms!weekly_fr_frm!txttopl & "'"
            Else
                mstsql = mstsql & " and idh_prodline like" & "'" & Forms!weekly_fr_frm!txtfrompl & "'"
            End If
        End If
        
        
        ' Sales Orders
        
        If Not IsNull(Forms!weekly_fr_frm!txtfromso) Then
            If Not IsNull(Forms!weekly_fr_frm!txttoso) Then
                mstsql = mstsql & " and ih_nbr between" & "'" & Forms!weekly_fr_frm!txtfromso & "'" & " and" & "'" & Forms!weekly_fr_frm!txttoso & "'"
            Else
                mstsql = mstsql & " and ih_nbr =" & "'" & Forms!weekly_fr_frm!txtfromso & "'"
            End If
        End If
        
        ' Buyer Planner
        
        If Not IsNull(Forms!weekly_fr_frm!txtfrombp) Then
            If Not IsNull(Forms!weekly_fr_frm!txttobp) Then
                mstsql = mstsql & " and pt_Buyer between" & "'" & Forms!weekly_fr_frm!txtfrombp & "'" & " and" & "'" & Forms!weekly_fr_frm!txttobp & "'"
            Else
                mstsql = mstsql & " and pt_Buyer =" & "'" & Forms!weekly_fr_frm!txtfrombp & "'"
            End If
        End If
        
        ' Customer Type Need to comeback to determine field name
        
        '
        
        'Ship to
        
        
        If Not IsNull(Forms!weekly_fr_frm!txtfromshipto) Then
            If Not IsNull(Forms!weekly_fr_frm!txttoshipto) Then
                mstsql = mstsql & " and ih_ship between " & "'" & Forms!weekly_fr_frm!txtfromshipto & "'" & " and" & "'" & Forms!weekly_fr_frm!txttoshipto & "'"
            Else
                mstsql = mstsql & " and ih_ship =" & "'" & Forms!weekly_fr_frm!txtfromshipto & "'"
            End If
        End If
        
        
        'Item Number
        
        If Not IsNull(Forms!weekly_fr_frm!txtfromItemnbr) Then
            If Not IsNull(Forms!weekly_fr_frm!txtfromItemnbr) Then
                mstsql = mstsql & " and idh_part between" & "'" & Forms!weekly_fr_frm!txtfromItemnbr & "'" & " and" & "'" & Forms!weekly_fr_frm!txttoItemnbr & "'"
            Else
                mstsql = mstsql & " and idh_Part =" & "'" & Forms!weekly_fr_frm!txtfromItemnbr & "'"
            End If
        End If
        
        'Item type
        
        If Not IsNull(Forms!weekly_fr_frm!txtfromItemtype) Then
            If Not IsNull(Forms!weekly_fr_frm!txttoItemtype) Then
                mstsql = mstsql & " and pt_part_type between " & "'" & Forms!weekly_fr_frm!txtfromItemtype & "'" & " and" & "'" & Forms!weekly_fr_frm!txttoItemtype & "'"
            Else
                mstsql = mstsql & " and pt_part_type =" & "'" & Forms!weekly_fr_frm!txtfromItemtype & "'"
            End If
        End If
        
        'Purchase Order
        
        If Not IsNull(Forms!weekly_fr_frm!txtfrompo) Then
            If Not IsNull(Forms!weekly_fr_frm!txttopo) Then
                mstsql = mstsql & " and ih_po between " & "'" & Forms!weekly_fr_frm!txtfrompo & "'" & " and" & "'" & Forms!weekly_fr_frm!txttopo & "'"
            Else
                mstsql = mstsql & " and ih_po =" & "'" & Forms!weekly_fr_frm!txtfrompo & "'"
            End If
        End If
        
        'Customer Type
        
        'MVR original code
        'If Not IsNull(Forms!weekly_fr_frm!txtfromct) Then
         '   If Not IsNull(Forms!weekly_fr_frm!txttoct) Then
          '      mstsql = mstsql & " and cm_type between " & "'" & Forms!weekly_fr_frm!txtfromct & "'" & " and" & "'" & Forms!weekly_fr_frm!txttoct & "'"
           ' Else
                'mstsql = mstsql & " and cm_type =" & "'" & Forms!weekly_fr_frm!txtfromct & "'"
            'End If
        'End If
        
        

        'MVR modified code
        If Not IsNull(Forms!weekly_fr_frm!txtfromct) Then
            If Not IsNull(Forms!weekly_fr_frm!txttoct) Then
                If Right(Forms!weekly_fr_frm!txtfromct, 1) = "*" Or Left(Forms!weekly_fr_frm!txtfromct, 1) = "*" Then
                    ctype1 = ""
                    typepos1 = 0
                    ctype2 = ""
                    typepos2 = 0
                    parse_cust_type Forms!weekly_fr_frm!txtfromct, ctype1, typepos1
                    parse_cust_type Forms!weekly_fr_frm!txttoct, ctype2, typepos2
                    
                    If typepos1 = 0 Then
                        mstsql = mstsql & " and substring(cm_type,1,len('" & ctype1 & "')) between " & "'" & ctype1 & "'" & " and" & "'" & ctype2 & "'"
                    Else
                        mstsql = mstsql & " and substring(cm_type," & typepos1 & "+ 1,len('" & ctype1 & "')) between " & "'" & ctype1 & "'" & " and" & "'" & ctype2 & "'"
                    End If
                Else
                    mstsql = mstsql & " and cm_type between " & "'" & Forms!weekly_fr_frm!txtfromct & "'" & " and" & "'" & Forms!weekly_fr_frm!txttoct & "'"
                End If
            Else
                mstsql = mstsql & " and cm_type =" & "'" & Forms!weekly_fr_frm!txtfromct & "'"
            End If
        End If
        
        
        'Sold To which is ih_bill from ih_hist
        
        If Not IsNull(Forms!weekly_fr_frm!txtfromSoldto) Then
            If Not IsNull(Forms!weekly_fr_frm!txttosoldto) Then
                mstsql = mstsql & " and ih_bill between " & "'" & Forms!weekly_fr_frm!txtfromSoldto & "'" & " and" & "'" & Forms!weekly_fr_frm!txttosoldto & "'"
            Else
                mstsql = mstsql & " and ih_bill =" & "'" & Forms!weekly_fr_frm!txtfromSoldto & "'"
            End If
        End If
        
        
         If Not IsNull(Forms!weekly_fr_frm!txtfromregion) Then
            If Not IsNull(Forms!weekly_fr_frm!txttoregion) Then
                mstsql = mstsql & " and cm_region between " & "'" & Forms!weekly_fr_frm!txtfromregion & "'" & " and" & "'" & Forms!weekly_fr_frm!txttoregion & "'"
            Else
                mstsql = mstsql & " and cm_region =" & "'" & Forms!weekly_fr_frm!txtfromregion & "'"
            End If
        End If
        
        
        'Design Group
        
        If Not IsNull(Forms!weekly_fr_frm!txtFromDesignGroup) Then
            If Not IsNull(Forms!weekly_fr_frm!txtToDesignGroup) Then
                mstsql = mstsql & " and pt_dsgn_grp between " & "'" & Forms!weekly_fr_frm!txtFromDesignGroup & "'" & " and" & "'" & Forms!weekly_fr_frm!txtToDesignGroup & "'"
            Else
                mstsql = mstsql & " and pt_dsgn_grp =" & "'" & Forms!weekly_fr_frm!txtToDesignGroup & "'"
            End If
        End If
        
        'PT Group
        
        If Not IsNull(Forms!weekly_fr_frm!txtFromPTGroup) Then
            If Not IsNull(Forms!weekly_fr_frm!txtToPTGroup) Then
                mstsql = mstsql & " and pt_group between " & "'" & Forms!weekly_fr_frm!txtFromPTGroup & "'" & " and" & "'" & Forms!weekly_fr_frm!txtToPTGroup & "'"
            Else
                mstsql = mstsql & " and pt_group =" & "'" & Forms!weekly_fr_frm!txtToPTGroup & "'"
            End If
        End If
        
        'PT ABC   MVR
        
        If Not IsNull(Forms!weekly_fr_frm!txtFromABCCode) Then
            If Not IsNull(Forms!weekly_fr_frm!txtToABCCode) Then
                mstsql = mstsql & " and pt_abc between " & "'" & Forms!weekly_fr_frm!txtFromABCCode & "'" & " and" & "'" & Forms!weekly_fr_frm!txtToABCCode & "'"
            Else
                mstsql = mstsql & " and pt_abc =" & "'" & Forms!weekly_fr_frm!txtToABCCode & "'"
            End If
        End If
        
        'Upper Managed Level
        If Not IsNull(Forms!weekly_fr_frm!txtFromUML) Then
            If Not IsNull(Forms!weekly_fr_frm!txtToUML) Then
                mstsql = mstsql & " and UpperManagedLevel between " & "'" & Forms!weekly_fr_frm!txtFromUML & "'" & " and" & "'" & Forms!weekly_fr_frm!txtToUML & "'"
            Else
                mstsql = mstsql & " and UpperManagedLevel =" & "'" & Forms!weekly_fr_frm!txtToUML & "'"
            End If
        End If
        
 If Forms!weekly_fr_frm!frm_rpt <> 4 Then
       
        
       ' mstsql = mstsql & " Group by " 'ih_ship_date"
       
        mstsql = mstsql & " Group by " 'ih_ship_date,"
        
        
        
        If Forms!weekly_fr_frm!frm_rpt = 1 Then
        
                                      For l = 1 To cnt
                                          xrcsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'detail' and freq = 'D') and (sql_freq = 'B' or sql_freq = 'GP')"
                                          Set rsdet = db.OpenRecordset(xrcsql, dbOpenDynaset)
                                              If rsdet.RecordCount = 0 Then
                                              
                                              Else
                                                    rsdet.MoveFirst
                                                    mstsql = mstsql & rsdet!l_names & ","
                                              End If
                                              
                                          
                                       Next l
                                      mstsql = Mid(mstsql, 1, Len(mstsql) - 1)
                                      mstsql = mstsql & " with Rollup"
        End If
    
ElseIf Forms!weekly_fr_frm!frm_rpt = 2 Then

     If Forms!weekly_fr_frm!frm_rpt <> 4 Then
        
                                    For l = 1 To cnt
                                          xrcsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'detail' and freq = 'W') and (sql_freq = 'B' or sql_freq = 'GP')"
                                          Set rsdet = db.OpenRecordset(xrcsql, dbOpenDynaset)
                                              If rsdet.RecordCount = 0 Then
                                              
                                              Else
                                                    rsdet.MoveFirst
                                                    mstsql = mstsql & rsdet!l_names & ","
                                              End If
                                              
                                          
                                       Next l
                                      mstsql = Mid(mstsql, 1, Len(mstsql) - 1)
                                      mstsql = mstsql & " with Rollup"
  End If
  
End If
       '        Build Select statement here For Group By for Year to Date
        
         If Forms!weekly_fr_frm!frm_rpt = 4 Then
             mstsql = mstsql & " Group by "
                For l = 1 To cnt
                                          xrcsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'detail' and freq = 'D') and (sql_freq = 'B' or sql_freq = 'GP') and l_names <> 'ih_ship_date'"
                                          Set rsdet = db.OpenRecordset(xrcsql, dbOpenDynaset)
                                              If rsdet.RecordCount = 0 Then
      
                                              Else
                                                    rsdet.MoveFirst
                                                    mstsql = mstsql & rsdet!l_names & ","
                                              End If
      
      
                                       Next l
                                       
                                         mstsql = Mid(mstsql, 1, Len(mstsql) - 1)
                                      mstsql = mstsql & " with Rollup"
                                       
         ElseIf Forms!weekly_fr_frm!frm_rpt = 2 Then
           ' mstsql = mstsql & " Group by "
                For l = 1 To cnt
                                          xrcsql = "select * from xrc_table where (g_names =" & "'" & N(l, 1) & "'" & " and Table_Name = 'detail' and freq = 'W') and (sql_freq = 'B' or sql_freq = 'GP') and l_names <> 'ih_ship_date'"
                                          Set rsdet = db.OpenRecordset(xrcsql, dbOpenDynaset)
                                              If rsdet.RecordCount = 0 Then
      
                                              Else
                                                    rsdet.MoveFirst
                                                    mstsql = mstsql & rsdet!l_names & ","
                                              End If
      
      
                                       Next l
                                     
      
                                      mstsql = Mid(mstsql, 1, Len(mstsql) - 1)
                                      mstsql = mstsql & " with Rollup"
         End If
         
        
        
    
        
        
        
        
        CurrentDb.QueryDefs("catch_all").sql = mstsql
        DoCmd.OpenQuery "Catch_all", acNormal
Else


       'Builds Sql for 99.24 By Specialities
       
       
                            Dim Sql_9924 As String
                                 
                            Sql_9924 = "Select inv_site,inv_ship_date, "
                            Sql_9924 = Sql_9924 & " case when substring(inv_type,1,1)=1 then 'Retail' "
                            Sql_9924 = Sql_9924 & " else case when substring(inv_type,1,1)=2 then 'Traditional' else "
                            Sql_9924 = Sql_9924 & " case when substring(inv_type,1,1)=3 then 'Special' "
                            Sql_9924 = Sql_9924 & " else "
                            Sql_9924 = Sql_9924 & " case when substring(inv_type,1,1)=4 then 'Hardware' "
                            Sql_9924 = Sql_9924 & " else 'none' "
                            Sql_9924 = Sql_9924 & " end end end end as cust_type, "
                            
                            Sql_9924 = Sql_9924 & " sum(inv_lines_ord) as lines_ordered,"
                            Sql_9924 = Sql_9924 & " sum(inv_lines_ship) as Lines_Shipped,"
                            Sql_9924 = Sql_9924 & " case when sum(inv_lines_ord)= 0 then 0 else cast (cast (sum(inv_lines_ship)as decimal (11,2))/cast (sum(inv_lines_ord)as decimal(11,2)) as decimal (11,2)) end as "
                            Sql_9924 = Sql_9924 & "lines_fr ,"
                            Sql_9924 = Sql_9924 & " sum(inv_units_ord) as units_ordered,"
                            Sql_9924 = Sql_9924 & " sum(inv_units_ship) as units_shipped,"
                            Sql_9924 = Sql_9924 & " case when sum (inv_units_ord) = 0 then 0 else cast(cast (sum(inv_units_ship)/sum(inv_units_ord)as decimal (11,2)) as decimal(11,2)) end as units_fr,"
                            Sql_9924 = Sql_9924 & " sum (inv_dollars_ord) as dollars_ordered,"
                            Sql_9924 = Sql_9924 & " sum (inv_dollars_ship) as dollars_shipped,"
                            Sql_9924 = Sql_9924 & " case when sum (inv_dollars_ord) = 0 then 0 else cast(cast (sum(inv_dollars_ship)/sum(inv_dollars_ord)as decimal(11,2)) as decimal (11,2)) end as dollars_fr"
                            Sql_9924 = Sql_9924 & "    From orig_inv_fr"
         
       
         'Criteria for Site
                
                   If Not IsNull(Forms!weekly_fr_frm!txtfromsite) Then
                        If Not IsNull(Forms!weekly_fr_frm!txtfromsite) Then
                            Sql_9924 = Sql_9924 & " Where inv_site between" & "'" & Forms!weekly_fr_frm!txtfromsite & "'" & " and" & "'" & Forms!weekly_fr_frm!txttosite & "'"
                        Else
                            Sql_9924 = Sql_9924 & " Where inv_site =" & "'" & Forms!weekly_fr_frm!txtfromsite & "'"
                        End If
                    End If
                
          'Criteria for Date
                
                    If Not IsNull(Forms!weekly_fr_frm!txtfromdate) Then
                        If Not IsNull(Forms!weekly_fr_frm!txtfromdate) Then
                            Sql_9924 = Sql_9924 & " And inv_ship_date between" & "'" & Forms!weekly_fr_frm!txtfromdate & "'" & " and" & "'" & Forms!weekly_fr_frm!txttodate & "'"
                        Else
                            Sql_9924 = Sql_9924 & " And inv_ship_date =" & "'" & Forms!weekly_fr_frm!txtfromdate & "'"
                        End If
                    End If
                    
       'Group By
                            Sql_9924 = Sql_9924 & "Group by inv_site, inv_ship_date,"
                            Sql_9924 = Sql_9924 & " case when substring(inv_type,1,1)=1 then 'Retail' "
                            Sql_9924 = Sql_9924 & " else case when substring(inv_type,1,1)=2 then 'Traditional' else "
                            Sql_9924 = Sql_9924 & " case when substring(inv_type,1,1)=3 then 'Special' "
                            Sql_9924 = Sql_9924 & " else "
                            Sql_9924 = Sql_9924 & " case when substring(inv_type,1,1)=4 then 'Hardware' "
                            Sql_9924 = Sql_9924 & " else 'none' "
                            Sql_9924 = Sql_9924 & " end end end end "
                            Sql_9924 = Sql_9924 & " With Rollup"
                
                 CurrentDb.QueryDefs("catch_all").sql = Sql_9924
                 DoCmd.OpenQuery "Catch_all", acNormal
            
End If

Exit Function


err:

If err.Number = 3218 Then
    Resume
Else

End If


End Function

Public Function validation()
If Not IsNull(Forms!weekly_fr_frm!txttosite) Then
        If Nz(Forms!weekly_fr_frm!txtfromsite) > Nz(Forms!weekly_fr_frm!txttosite) Then
                MsgBox "From Site is higher than To Site", 48
                data_val = False
                Exit Function
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttocust) Then
        If Nz(Forms!weekly_fr_frm!txtfromcust) > Nz(Forms!weekly_fr_frm!txttocust) Then
                MsgBox "From Customer is higher than To Customer", 48
                data_val = False
                Exit Function
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoso) Then
        If Nz(Forms!weekly_fr_frm!txtfromso) > Nz(Forms!weekly_fr_frm!txttoso) Then
                MsgBox "From Sales Order is higher than To Sales Order", 48
                data_val = False
                Exit Function
        End If
End If


If Not IsNull(Forms!weekly_fr_frm!txttobp) Then
        If Nz(Forms!weekly_fr_frm!txtfrombp) > Nz(Forms!weekly_fr_frm!txttobp) Then
                MsgBox "From Buyer Planner is higher than To Buyer Planner", 48
                data_val = False
                Exit Function
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttopl) Then
            If Nz(Forms!weekly_fr_frm!txtfrompl) > Nz(Forms!weekly_fr_frm!txttopl) Then
                    MsgBox "From Product Line is higher than To Product Line", 48
                    Exit Function
                    data_val = False
            End If
End If


If Not IsNull(Forms!weekly_fr_frm!txttoct) Then
        If Nz(Forms!weekly_fr_frm!txtfromct) > Nz(Forms!weekly_fr_frm!txttoct) Then
                MsgBox "From Customer Type is higher than To Customer Type", 48
                Exit Function
                data_val = False
        End If
End If


If Not IsNull(Forms!weekly_fr_frm!txttoshipto) Then
        If Nz(Forms!weekly_fr_frm!txtfromshipto) > Nz(Forms!weekly_fr_frm!txttoshipto) Then
                MsgBox "From Ship To is higher than To Ship To", 48
                data_val = False
                Exit Function
                
        End If
End If


If Not IsNull(Forms!weekly_fr_frm!txttoItemnbr) Then
        If Nz(Forms!weekly_fr_frm!txtfromItemnbr) > Nz(Forms!weekly_fr_frm!txttoItemnbr) Then
                MsgBox "From Item Number is higher than To Item Number", 48
                data_val = False
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoItemtype) Then
        If Nz(Forms!weekly_fr_frm!txtfromItemtype) > Nz(Forms!weekly_fr_frm!txttoItemtype) Then
                MsgBox "From Item Type is higher than To Item Type", 48
                data_val = False
        End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttopo) Then
        If Nz(Forms!weekly_fr_frm!txtfrompo) > Nz(Forms!weekly_fr_frm!txttopo) Then
                MsgBox "From Purchase Order is higher than To Purchase Order", 48
                data_val = False
        End If
End If



If Not IsNull(Forms!weekly_fr_frm!txttosite) Then
    If IsNull(Forms!weekly_fr_frm!txtfromsite) Then
        MsgBox "From Site Must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttocust) Then
    If IsNull(Forms!weekly_fr_frm!txtfromcust) Then
        MsgBox "From Customer must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoso) Then
    If IsNull(Forms!weekly_fr_frm!txtfromso) Then
        MsgBox "From Sales Order must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttobp) Then
    If IsNull(Forms!weekly_fr_frm!txtfrombp) Then
        MsgBox "From Buyer Planner must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttopl) Then
    If IsNull(Forms!weekly_fr_frm!txtfrompl) Then
        MsgBox "From Product Line must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoct) Then
    If IsNull(Forms!weekly_fr_frm!txtfromct) Then
        MsgBox "From Customer Type must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoshipto) Then
    If IsNull(Forms!weekly_fr_frm!txtfromshipto) Then
        MsgBox "From Ship To must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoItemnbr) Then
    If IsNull(Forms!weekly_fr_frm!txtfromItemnbr) Then
        MsgBox "From Item Number must have a value", 48
        data_val = False
        Exit Function
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttoItemtype) Then
    If IsNull(Forms!weekly_fr_frm!txtfromItemtype) Then
        MsgBox "From Item Type must have a value", 48
        Exit Function
        data_val = False
    End If
End If

If Not IsNull(Forms!weekly_fr_frm!txttopo) Then
    If IsNull(Forms!weekly_fr_frm!txtfrompo) Then
        MsgBox "From Item Purchase Order must have a value", 48
        data_val = False
        Exit Function
    End If
End If

   Dim dbresults As Database
   Dim strsql As String
   Dim rsresults As Recordset
   
   
   If IsNull(Forms!weekly_fr_frm!txtfromdate) Then
            MsgBox "Must enter a from date", 48
            data_val = False
            Exit Function
        ElseIf IsNull(Forms!weekly_fr_frm!txttodate) Then
            MsgBox "Must enter a todate", 48
            data_val = False
            Exit Function
            
    End If
   
   

 If Forms!weekly_fr_frm!frm_rpt <> 1 Then
 
        
    Else
       ' MsgBox "Daily Options not yet available"
       ' exit function

    End If
    
 '   If IsNull(Me!chkcustomer) And IsNull(chkSo) And IsNull(chkBp) And IsNull(chkpl) And IsNull(chkCt) And IsNull(chkSt) And IsNull(chkitemnbr) And IsNull(chkItemtype) And IsNull(chkSite) And IsNull(chkPo = Null) Then
 '   MsgBox "must select a break option"
 '   exit function
 '   End If

        If Forms!weekly_fr_frm!frm_rpt = 2 Then
              Dim mystr As String
              Dim to_day As String
              Dim cur_date As String
              
              Dim d_begdate As String
              Dim cur_day As String
              
              
              to_day = Forms!weekly_fr_frm!txtfromdate
              cur_day = Format(DateAdd("d", 0, to_day), "dddd")
             
          
                If cur_day <> "Sunday" Then
                    MsgBox "Must enter a valid Saturday from date and Sunday to date", 48
                    data_val = False
                    Exit Function
                Else
                            cur_day = Format(DateAdd("d", 0, Forms!weekly_fr_frm!txttodate), "dddd")
                If cur_day <> "Saturday" Then
                           MsgBox "Must enter a valid Saturday from date and Sunday to date", 48
                           data_val = False
                           Exit Function
                End If
                
                End If
      
        End If
        
        
Exit Function

err:
If err.Number = 3218 Then
        Resume
Else

     MsgBox "application error number " & err.Number & "  has occurred, please report this number to the help desk at 5555"
     
End If




End Function

'MVR
Private Sub parse_cust_type(ByVal CustType As String, ByRef subType As String, ByRef totalStar As Integer)
    Dim i As Integer
    
        
    If Left(CustType, 1) = "*" Then
        i = 1
        Do While i <= Len(CustType)
            If Mid(CustType, i, 1) <> "*" Then
                subType = subType + Mid(CustType, i, 1)
            Else
                totalStar = totalStar + 1
            End If
            i = i + 1
        Loop
    Else
        i = 1
        Do While i <= Len(CustType)
            If Mid(CustType, i, 1) <> "*" Then
                subType = subType + Mid(CustType, i, 1)
            Else
                Exit Do
            End If
            i = i + 1
        Loop
                
    End If

End Sub

Open in new window

0
tesla764Author Commented:
Are you able to see the code?
0
Rey Obrero (Capricorn1)Commented:

try replacing your mstsql with this




        mstsql = mstsql & "count(*)as lines_Ordered,"
        mstsql = mstsql & "sum(case idh_qty_ship "
        mstsql = mstsql & " when 0 then 0   else 1 "
        mstsql = mstsql & "end)as lines_shipped, "
        mstsql = mstsql & " cast (sum(case idh_qty_ship "
        mstsql = mstsql & "when 0 then 0 "
        mstsql = mstsql & "else 1 "
        mstsql = mstsql & "end) * 100 /  "
        mstsql = mstsql & " nullif(count(*),0) * .01 as decimal (20,2))as lines_Fr,  "
        mstsql = mstsql & "cast(nullif(sum(idh_qty_ord),0)as decimal (15,3)) as Units_Ordered,"
        mstsql = mstsql & "cast(nullif(sum(idh_qty_ship),0)as decimal (15,3)) as Units_ship,"
        mstsql = mstsql & "cast (sum(idh_qty_ship) *100 / "
        mstsql = mstsql & "nullif(sum(case "
        mstsql = mstsql & "when idh_qty_ord < idh_qty_ship "
        mstsql = mstsql & "then idh_qty_ship "
        mstsql = mstsql & "Else: idh_qty_ord "
        mstsql = mstsql & "end), 0) * .01 as decimal (10,3)) as Units_Fr, "
        mstsql = mstsql & "cast (sum(idh_qty_ord  * idh_price) as money ) as Dollars_Ordered, "
        mstsql = mstsql & "cast (sum(idh_qty_ship * idh_price) as money) as Dollars_Shipped, "
        mstsql = mstsql & "cast (sum(idh_qty_ship * idh_price) * 100 / "
        mstsql = mstsql & "nullif(sum(case "
        mstsql = mstsql & "When idh_qty_ord * idh_price < idh_qty_ship * idh_price "
        mstsql = mstsql & "Then idh_qty_ship * idh_price "
        mstsql = mstsql & "else idh_qty_ord * idh_price "
        mstsql = mstsql & "end), 0)* .01 as decimal (11,2)) as Dollars_FR "
        mstsql = mstsql & " FROM ih_hist INNER JOIN idh_hist ON ih_hist.ih_inv_nbr = idh_hist.idh_inv_nbr"
        mstsql = mstsql & " AND ih_hist.ih_nbr = idh_hist.idh_nbr INNER JOIN pt_mstr"
        mstsql = mstsql & " ON idh_hist.idh_part = pt_mstr.pt_part INNER JOIN Cm_mstr"
        mstsql = mstsql & " ON ih_hist.ih_cust = Cm_mstr.cm_addr"
        mstsql = mstsql & " LEFT JOIN " & CentralDBPrefix & ".xRefManagedLevels x "
        mstsql = mstsql & " ON x.ManagedLevel = Left(pt_part_type,4) "
        mstsql = mstsql & " LEFT JOIN " & CentralDBPrefix & ".UpperManagedLevels u "
        mstsql = mstsql & " ON u.UpperManagedLevelID = x.UpperManagedLevelID "
        mstsql = mstsql & " WHERE ih__qadc02   = 'O' "
        mstsql = mstsql & " and (idh_type is null or idh_type != 'M') "
        mstsql = mstsql & " and idh_qty_ord  > 0 "
        mstsql = mstsql & " and pt_part_type not like '0597%' "
        mstsql = mstsql & " and (ih__chr09 != 'e'  or ih__chr09 is null) "

0
Rey Obrero (Capricorn1)Commented:
also check if this field names are correct


ih__qadc02

ih__chr09
0
Rey Obrero (Capricorn1)Commented:
also, this is just one of the many similar lines you have in your codes

lines 1141 to 1148
        If Not IsNull(Forms!weekly_fr_frm!txtfromdate) Then
            If Not IsNull(Forms!weekly_fr_frm!txttodate) Then
                    mstsql = mstsql & " and ih_inv_date between" & "'" & Forms!weekly_fr_frm!txtfromdate & "'" & " and" & "'" & Forms!weekly_fr_frm!txttodate & "'"
                        
            Else
                    mstsql = mstsql & " and ih_inv_date = " & "'" & Forms!weekly_fr_frm!txtfromdate & "'"
            End If
        End If

Open in new window



the word   between"  must be    between "  '< a space after the word between
the word   " and"   must be   " and "             '< a space after the word and



apply the changes to the rest of your codes

0
tesla764Author Commented:
Thanks for your responses. I am going to test these possible solutions now.
0
tesla764Author Commented:
Thanks everybody. This was very helpful.
0
tesla764Author Commented:
Thanks everybody.
0
Rey Obrero (Capricorn1)Commented:
tesla764,

you should be accepting the post that helped you resolved your problem.

if this was done in error, click on the Request Attention and ask to reopen the thread so you can select the appropriate post/comments.
0
peter57rCommented:
You should be accepting one of Capricorn1's posts as your solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.