Solved

sql - vb

Posted on 2004-11-01
272 Views
Last Modified: 2010-05-02
I have  some VB code with me that basically executes an sql statement and then saves the result in an excel worksheet. Now I have a statement in my sql like this in the where clause:

Where ……
AND  PT. TYPE IN ( 'GF', 'IV','FIL','FPL00', 'FLS',
                             'RP', 'RM', 'RU')

Now sometimes the user would want to run the sql for all these types, but sometimes the user would want to run it only for 2 or 3 types. So I want to give an option for that in the vb program, like
1. Run report for all types
2. Select the types( and some way like check box or something to select the type(I don’t want the user to type ; I prefer them to click so that they don’t give wrong spelling)

And then I want to be able to  pass these parameters to the sql...
I have some code in which I am doing something similar for dates…which I am posting below. Please paste ur code in my code.. for clarity..

FOR THE FORM:
Private Sub cmdCancel_Click()
      Unload Me
   
End Sub
Private Sub Command1_Click()
If Trim(txtBeginDate) = "" Then                      'ensure date is entered
       MsgBox "Beginning date range must be entered!", vbCritical, "Date Error"
       txtBeginDate.Text = ""
       txtBeginDate.SetFocus
       Exit Sub
    End If
   
    If Not IsDate(txtBeginDate) Then
       MsgBox "Not a valid date!", vbCritical, "Date Error"
       txtBeginDate.Text = ""
       txtBeginDate.SetFocus
       Exit Sub
    End If
   
       
  If Trim(txtEndDate) = "" Then                      'ensure date is entered
       MsgBox "End date range must be entered!", vbCritical, "Date Error"
       txtEndDate.Text = ""
       txtEndDate.SetFocus
       Exit Sub
    End If
   
     If Not IsDate(txtEndDate) Then
       MsgBox "Not a valid date!", vbCritical, "Date Error"
       txtEndDate.Text = ""
       txtEndDate.SetFocus
       Exit Sub
    End If
   
    strBegDate = ConvertDate(txtBeginDate.Text)
 
    strEndDate = ConvertDate(txtEndDate.Text)
   
    Call Main
   
End Sub



IN THE MODULE:

Option Explicit
Public strUserName As String
Public strPassWord As String
Public strBegDate As String
Public strEndDate As String
Private sFolderLoc As String
Private sSQL As String
Private conn As ADODB.Connection
Private rstRecordSet As ADODB.Recordset


Const SQL_Home = "C:\SQL"
Public Sub Main()
Dim connStr As String
Dim sFileName As String
Dim sSQL As String

   On Error GoTo main_error
   

      connStr = "PROVIDER=MSDASQL;" & _
            "DRIVER={microsoft odbc for oracle};" & _
            "SERVER=;" & _
            "UID=" & ";PWD=" & ";"
   
   
   
   

    Set conn = New ADODB.Connection

    conn.CursorLocation = adUseClient

    conn.Open connStr
   
    DoEvents

    Screen.MousePointer = vbHourglass
   
    sFolderLoc = "C:\OUTPUT”    ' assign output location
   
    Set rstRecordSet = New ADODB.Recordset
   


   
    'FIRST REPORT
   

   
    sSQL = " "
    sSQL = Build_Query(SQL_Home & "\DEVON.sql")
   
    rstRecordSet.Open sSQL, conn, adOpenForwardOnly
   
    sFileName = sFolderLoc & "\DEVON_" & Format(Now, "mm""-""dd""-""yyyy") & ".csv"
   
     WriteOutReport sFileName
     
     rstRecordSet.Close
     
     Screen.MousePointer = vbDefault
     

    If Not rstRecordSet Is Nothing Then     'unload form and close recordset and connection to Oracle
       If rstRecordSet.State <> 0 Then
              rstRecordSet.Close
       End If
       Set rstRecordSet = Nothing
    End If

    If Not conn Is Nothing Then
       conn.Close
       Set conn = Nothing
    End If
   
       
    MsgBox "Finished"
   
    Screen.MousePointer = vbNormal
   
    End
   
main_error:

    Screen.MousePointer = vbDefault
   
    MsgBox "Error- " & Err.Description & " - CALL"
   
    If Not rstRecordSet Is Nothing Then     'unload form and close recordset and connection to Oracle
       If rstRecordSet.State <> 0 Then
              rstRecordSet.Close
       End If
       Set rstRecordSet = Nothing
    End If

    If Not conn Is Nothing Then
       conn.Close
       Set conn = Nothing
    End If
   
    End
   
    End Sub


   
Private Function Build_Query(sFileName As String)
   
    Dim sInput As String
    Dim sOutput As String
   
    Open sFileName For Input As #1

    Line Input #1, sInput   ' Get a line of input from the file(sFileName) and store it in the sInput
                            '  string
   
    sOutput = sInput
   
    Do While Not EOF(1)
       
        Line Input #1, sInput
        sOutput = sOutput & Chr(10) & sInput
    Loop
   
    sOutput = Replace(sOutput, "&begdate", "'" & strBegDate & "'")
    sOutput = Replace(sOutput, "&enddate", "'" & strEndDate & "'")
    Close #1
    Build_Query = sOutput
   
End Function

Private Sub remove_file(fname As String)
    If Len(Dir(fname)) Then
        Kill fname
    End If
End Sub
Public Function ConvertDate(ByVal strDate As Date) As String
 
  'convert  date to Oracle date
 
  Dim strDateOut As String
  'Dim ConvertDate As String
 
 
  Select Case Val(Format(strDate, "mm"))
    Case 1
        strDateOut = Format(strDate, "dd") & "-JAN-" & Format(strDate, "YYYY") & ""
    Case 2
        strDateOut = Format(strDate, "dd") & "-FEB-" & Format(strDate, "YYYY") & ""
    Case 3
        strDateOut = Format(strDate, "dd") & "-MAR-" & Format(strDate, "YYYY") & ""
    Case 4
        strDateOut = Format(strDate, "dd") & "-APR-" & Format(strDate, "YYYY") & ""
    Case 5
        strDateOut = Format(strDate, "dd") & "-MAY-" & Format(strDate, "YYYY") & ""
    Case 6
        strDateOut = Format(strDate, "dd") & "-JUN-" & Format(strDate, "YYYY") & ""
    Case 7
        strDateOut = Format(strDate, "dd") & "-JUL-" & Format(strDate, "YYYY") & ""
    Case 8
        strDateOut = Format(strDate, "dd") & "-AUG-" & Format(strDate, "YYYY") & ""
    Case 9
        strDateOut = Format(strDate, "dd") & "-SEP-" & Format(strDate, "YYYY") & ""
    Case 10
        strDateOut = Format(strDate, "dd") & "-OCT-" & Format(strDate, "YYYY") & ""
    Case 11
        strDateOut = Format(strDate, "dd") & "-NOV-" & Format(strDate, "YYYY") & ""
    Case Else
        strDateOut = Format(strDate, "dd") & "-DEC-" & Format(strDate, "YYYY") & ""
  End Select

  ConvertDate = strDateOut
 
End Function

Private Sub WriteOutReport(ByVal sFileName As String)
 remove_file (sFileName)
     
     If rstRecordSet.RecordCount > 0 Then 'put data into file which will be read
     Open sFileName For Output As #1
     
        Write #1, rstRecordSet.Fields(0).Name, rstRecordSet.Fields(1).Name, _
                        rstRecordSet.Fields(2).Name, rstRecordSet.Fields(3).Name, _
                        rstRecordSet.Fields(4).Name, rstRecordSet.Fields(5).Name, _
                        rstRecordSet.Fields(6).Name, rstRecordSet.Fields(7).Name, _
                        rstRecordSet.Fields(8).Name, rstRecordSet.Fields(9).Name, _
                        rstRecordSet.Fields(10).Name, rstRecordSet.Fields(11).Name, _
                        rstRecordSet.Fields(12).Name, rstRecordSet.Fields(13).Name, _
                        rstRecordSet.Fields(14).Name, rstRecordSet.Fields(15).Name, _
                        rstRecordSet.Fields(16).Name, rstRecordSet.Fields(17).Name, _
                        rstRecordSet.Fields(18).Name, rstRecordSet.Fields(19).Name


        Do While Not rstRecordSet.EOF
                    Write #1, rstRecordSet.Fields(0), rstRecordSet.Fields(1), _
                        rstRecordSet.Fields(2), rstRecordSet.Fields(3), _
                        rstRecordSet.Fields(4), rstRecordSet.Fields(5), _
                        rstRecordSet.Fields(6), rstRecordSet.Fields(7), _
                        rstRecordSet.Fields(8), rstRecordSet.Fields(9), _
                        rstRecordSet.Fields(10), rstRecordSet.Fields(11), _
                        rstRecordSet.Fields(12), rstRecordSet.Fields(13), _
                        rstRecordSet.Fields(14), rstRecordSet.Fields(15), _
                        rstRecordSet.Fields(16), rstRecordSet.Fields(17), _
                        rstRecordSet.Fields(18), rstRecordSet.Fields(19)

        rstRecordSet.MoveNext
        Loop

        Close #1

      'rstRecordSet.Close
    End If
End Sub


0
Question by:Sara_j_11
    20 Comments
     
    LVL 16

    Accepted Solution

    by:
    Make a small form (Form1), with a control array of check boxes (CheckBox()) on it (copy and paste the check boxes and say Yes when asked if you want to create a control array) and a 'Go' command button (Command1).  Type a clause into the caption of each check box ("GF", "FIL", etc).  You should now have:

        Form1
           - CheckBox(0) - GF
           - CheckBox(1) - IV
           - CheckBox(2) - FIL
           - ...etc
           - Command1 - Go

    Now, in Form1 code:

        [General/Declarations]

             Public strWherePTTypeIn As String


        Private Sub Command1_Click()

             Dim chk as Integer

             strWherePTTypeIn = ""

             For chk = LBound(Form1.CheckBox()) to Ubound(Form1.CheckBox())
                                                                                      'FOR ALL THE CHECKBOXES IN THE CONTROL ARRAY...

                   If CheckBox(chk).Value = True then                'IF TICKED

                      strWherePTTypeIn = strWherePTTypeIn & "'" & CheckBox(chk).Caption & "'"
                                                                                      'ADD 'thischeckboxcaption' TO THE STRING

                      If chk < Ubound(Form1.CheckBox()) Then
                         strWherePTTypeIn = strWherePTTypeIn & ","
                                                                                      'ADD A COMMA IF NOT AT THE END
                      End If

                   End If

             Next chk


    Now, instead of adding

             "AND  PT. TYPE IN ( 'GF', 'IV','FIL','FPL00', 'FLS', 'RP', 'RM', 'RU')"

    to the SQL string that is passed to your RecordSet.Open command, use

             "AND  PT. TYPE IN (" & strWherePTTypeIn & ")"

    to pass the results of the Form1.CheckBox array.

    HTH

    J.

    0
     
    LVL 10

    Assisted Solution

    by:fds_fatboy
    This isn't to do with this problem, but couldn't you replace your convertdate function with something a little smaller?

    If you are in an English locale:

    Public Function ConvertDate(ByVal strDate As Date) As String
        If not isDate(strDate) then
            'Raise error in here
            Exit sub
        Else
            ConvertDate = Ucase$(Format$(CDate(strDate),"dd-mmm-yyyy"))
        End If

    End Function

    Otherwise:

    Public Function ConvertDate(ByVal strDate As Date) As String
        Const MONTHNAMES="JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEV"
        Dim MonthName As String
        Dim ConvDate As Date

        If not isDate(strDate) then
            'Raise error in here
            Exit sub
        Else
            ConvDate = CDate(strDate)
            MonthName = Mid$(MONTHNAMES, Month(CDate)*3-2, 3)
            ConvertDate = Format$(ConvDate,"dd-") & MonthName &  Format$(ConvDate,"-yyyy")
        End If

    End Function
    0
     

    Author Comment

    by:Sara_j_11
    Dear  expert jimbobmcgee , I tried doing this ,  but when I run the project , the form does not pop up for me to check the check boxes, it looks like execution starts from the module where I have my other block of code... What should I do here...
    0
     
    LVL 12

    Expert Comment

    by:jkaios
    Can you call your main form from your module as in Form1.Show. It sounds like the "Start-up Object" for your project is set to the Module.
    0
     
    LVL 16

    Expert Comment

    by:jimbobmcgee
    From what I can gather, I would place the show command in one of two places in your existing code:

          Private Sub Command1_Click()
              ...
              ...
              yourWhereInForm.Show True    'SHOW FORM MODALLY BEFORE CALLING REPORTING PROCEDURE
              Call Main
          End Sub

    or:

          Public Sub Main()
              ...
              On Error Goto main_error
              yourWhereInForm.Show True    'SHOW FORM MODALLY FIRST THING IN REPORTING PROCEDURE
              ...
              ...
          End Sub

    Personally, I would use the first point-of-entry...

    HTH

    J.

    (BTW:  Did you have any joy with the reformatting of your PHYSNAMES/MFG ID's -- I did post a reply to you, after a while, but never heard anything...)
    0
     

    Author Comment

    by:Sara_j_11
    If I place the form1.show in my module, and try to run the code,
    though the form pops up , it does not let me select any option; its just like a dummy box... this is all that I had in the code section of the form.
    Option Explicit

    Public strWhereTypeIn As String




    Private Sub Command1_Click()


    Dim chk As Integer

    strWhereTypeIn = ""

    For chk = LBound(Form1.CheckBox()) To UBound(Form1.CheckBox())
    'FOR ALL THE CHECKBOXES IN THE CONTROL ARRAY...

    If CheckBox(chk).Value = True Then 'IF TICKED

    strWhereTypeIn = strWhereTypeIn & "'" & CheckBox(chk).Caption & "'"
    'ADD 'thischeckboxcaption' TO THE STRING

    If chk < UBound(Form1.CheckBox()) Then
    strWhereTypeIn = strWhereTypeIn & ","
    'ADD A COMMA IF NOT AT THE END
    End If

    End If

    Next chk

    Call Main

    End Sub

    Dear expert J, I have not got a chance to review the formatting of the code that you posted for another question since it took a long time.. But I will definitely review and grade you for that.
    0
     

    Author Comment

    by:Sara_j_11
    When you say It sounds like the "Start-up Object" for your project is set to the Module. How can I change it...
    0
     

    Author Comment

    by:Sara_j_11
    I just tried creating a seperate project .vbp file with just this form code and a form, and it lets me click on checkboxes.. But does not work with my project..
    Option Explicit

    Public strWheretypeIn As String




    Private Sub Command1_Click()


    Dim chk As Integer

    strWhereTypeIn = ""
    For chk = Form1.Check1(0) To Form1.Check1(2)
    'For chk = LBound(Form1.Check1()) To UBound(Form1.Check1())
    'FOR ALL THE CHECKBOXES IN THE CONTROL ARRAY...

    If Check1(chk).Value = True Then 'IF TICKED

    strWhereTypeIn = strWhereTypeIn & "'" & Check1(chk).Caption & "'"
    'ADD 'thischeckboxcaption' TO THE STRING

    If chk < Form1.Check1(2) Then
    strWhereTypeIn = strWhereTypeIn & ","
    'ADD A COMMA IF NOT AT THE END
    End If

    End If

    Next chk

    'Call Main
     MsgBox "done"
    End Sub
    0
     

    Author Comment

    by:Sara_j_11
    Ok I just changed the project proprties to start wit the form1. Let me see if that works
    0
     

    Author Comment

    by:Sara_j_11
    expert Jim , could you pl. tell me why u have

             "AND  PT. TYPE IN (" & strWherePTTypeIn & ")"
    why not
     

             "AND  PT. TYPE IN ( & strWherePTTypeIn  )
    0
     

    Author Comment

    by:Sara_j_11
    aFTER i CHANGED MY project properties to start with form1; I am able to select the optioons in the form and then click the command button for 'GO'; But it looks like there is some problem with
    sOutput = Replace(sOutput, "&TypeIn", strWhereTypeIn)
    the above line in my Private Function Build_Query(sFileName As String)
    function..Also in my sql I have have        
     "AND  PT. TYPE IN ( &strWherePTTypeIn  )
    0
     

    Author Comment

    by:Sara_j_11
    I am getting an error called ' compile error; expected array in this line
    For chk = LBound(Form1.Check1()) To UBound(Form1.Check1())
    and the word  LBound gets highlighted...

    Public strWhereTherapyIn As String

    '
    Private Sub Command1_Click()


    Dim chk As Integer

    strWhereTherapyIn = ""

    For chk = LBound(Form1.Check1()) To UBound(Form1.Check1())
    'FOR ALL THE CHECKBOXES IN THE CONTROL ARRAY...

    If Check1(chk).Value = True Then 'IF TICKED

    strWhereTherapyIn = strWhereTherapyIn & "'" & Check1(chk).Caption & "'"
    'ADD 'thischeckboxcaption' TO THE STRING

    If chk < UBound(Form1.Check1()) Then
    strWhereTherapyIn = strWhereTherapyIn & ","
    'ADD A COMMA IF NOT AT THE END
    End If

    End If

    Next chk

    Call Main

    End Sub
    0
     
    LVL 16

    Expert Comment

    by:jimbobmcgee
    Have you made the control array called Check1() -- lots of check boxes all called Check1, with a number in their Index property?  If not, they should be.

    Otherwise, try this concept:

        Dim ctl
       
        For Each ctl In UserForm1.Controls
       
            If TypeOf ctl Is CheckBox and ctl.Value = vbChecked Then
       
                WherePTTypeIn = WherePTTypeIn & ctl.Caption
               
            End If
           
        Next ctl

    HTH

    J.
    0
     

    Author Comment

    by:Sara_j_11
    Yes I did create a control array, but still got that syntax error.
    But I tried ur other concept and when I executed line by line , I notices that the WherePTTypeIn got populated with the check box caption, and then after call main , execution goes to main module -- line by line when I got to the build query module(posted in question):
    Private Function Build_Query(sFileName As String)
       
        Dim sInput As String
        Dim sOutput As String
       
        Open sFileName For Input As #1

        Line Input #1, sInput   ' Get a line of input from the file(sFileName) and store it in the sInput
                                '  string
       
        sOutput = sInput
       
        Do While Not EOF(1)
           
            Line Input #1, sInput
            sOutput = sOutput & Chr(10) & sInput
        Loop
       
        sOutput = Replace(sOutput, "&begdate", "'" & strBegDate & "'")
        sOutput = Replace(sOutput, "&enddate", "'" & strEndDate & "'")
        sOutput = Replace(sOutput, "&TherapyIn", strWhereTypeIn)
        Close #1
        Build_Query = sOutput
       
    End Function

    When I placed my cursor on the strWhereTypeIn i the above module, it just does not seem to pick up the value..  Even though strBegDate reflects some date at line by line execution.. Why does the module not recognise the strWhereTypeIn as an empty string... while it is actually picking up values at the form level..

    0
     

    Author Comment

    by:Sara_j_11
    Also how can I truncate the LAST COMMA from the string, after 'RT'?
     ' AC'  ,  'BC', 'RT',
    0
     
    LVL 16

    Expert Comment

    by:jimbobmcgee
    >> it just does not seem to pick up the value

    If you copied my code to the letter, this variable will be called strWherePTTypeIn, not strWhereTypeIn.  If you didn't copy my code to the letter, did you declare strWhereTypeIn as a Public variable, at the top of your form code:

         Public strWhereTypeIn as string

    or:

         Public strWherePTTypeIn as string


    >> how can I truncate the LAST COMMA from the string

    The following:

         If Right(strWherePTTypeIn, 1) = "," Then
            strWherePTTypeIn = Left(strWherePTTypeIn, Len(strWherePTTypeIn) - 1)
         End If

    placed at the end of the 'Go' button click code, or just after you show the Form...

    HTH

    J.
    0
     
    LVL 16

    Expert Comment

    by:jimbobmcgee
    Sorry, I missed this extra question:

    >>  expert Jim , could you pl. tell me why u have "AND  PT. TYPE IN (" & strWherePTTypeIn & ")"
           why not "AND PT. TYPE IN ( & strWherePTTypeIn  )

    You want the contents of the variable, strWherePTTypeIn, not the word 'strWherePTTypeIn'.  All literal text is defined between "quote marks".  '&' is a concatention operator; it basically adds everything after it to everything before.  It should also be outside any speech marks.

    Try this (separate) sample program, as an example:

          Private Sub Command1_Click()
       
              Dim thisString as String
              thisString = InputBox("Enter some text")

              MsgBox "The text you entered was & thisString", ,"Wrong way"
              MsgBox "The text you entered was " & thisString, , "Right Way"

          End Sub

    HTH

    J.
    0
     

    Author Comment

    by:Sara_j_11
    Thanks J, you were very helpful!
    I just took out the public declaration for strWherePTTypeIn from the form module and then placed it only in the module declaration and then the module picked up the value of strWherePTTypeIn!! Did not quite understand why the module did not recognise the public declaration of strWherePTTypeIn in the form...
    But it worked at least!!!
    0
     
    LVL 10

    Expert Comment

    by:fds_fatboy
    Thanks - but you shouldn't have given me any points. My input, although hopefully helpful in general, was not relevant to the question asked. Please give my points to jimbobmcgee.
    0
     

    Author Comment

    by:Sara_j_11
    Hi fds fatboy, I gave you some points becase ur genreal suggestion was useful and I appreciate that.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    The Complete Ruby on Rails Developer Course

    Ruby on Rails is one of the most popular web development frameworks, and a useful tool used by both startups and more established companies to build strong graphic user interfaces, and responsive websites and apps.

    Suggested Solutions

    Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    857 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

    20 Experts available now in Live!

    Get 1:1 Help Now