Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql - vb

Posted on 2004-11-01
20
Medium Priority
?
277 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
Comment
Question by:Sara_j_11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
  • 2
  • +1
20 Comments
 
LVL 16

Accepted Solution

by:
jimbobmcgee earned 1800 total points
ID: 12463921
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
fds_fatboy earned 200 total points
ID: 12463940
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
ID: 12468148
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 12

Expert Comment

by:jkaios
ID: 12470300
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
ID: 12471599
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
ID: 12472976
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
ID: 12473022
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
ID: 12473212
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
ID: 12473514
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
ID: 12473540
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
ID: 12473676
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
ID: 12474937
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
ID: 12475255
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
ID: 12476728
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
ID: 12476768
Also how can I truncate the LAST COMMA from the string, after 'RT'?
 ' AC'  ,  'BC', 'RT',
0
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12481756
>> 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
ID: 12483621
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
ID: 12484391
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
ID: 12491462
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
ID: 12496863
Hi fds fatboy, I gave you some points becase ur genreal suggestion was useful and I appreciate that.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses

610 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