Link to home
Start Free TrialLog in
Avatar of Sara_j_11
Sara_j_11

asked on

sql - vb

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


ASKER CERTIFIED SOLUTION
Avatar of jimbobmcgee
jimbobmcgee
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sara_j_11
Sara_j_11

ASKER

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...
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.
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...)
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.
When you say It sounds like the "Start-up Object" for your project is set to the Module. How can I change it...
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
Ok I just changed the project proprties to start wit the form1. Let me see if that works
expert Jim , could you pl. tell me why u have

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

         "AND  PT. TYPE IN ( & strWherePTTypeIn  )
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  )
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
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.
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..

Also how can I truncate the LAST COMMA from the string, after 'RT'?
 ' AC'  ,  'BC', 'RT',
>> 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.
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.
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!!!
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.
Hi fds fatboy, I gave you some points becase ur genreal suggestion was useful and I appreciate that.