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.T ext)
strEndDate = ConvertDate(txtEndDate.Tex t)
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).Nam e, rstRecordSet.Fields(1).Nam e, _
rstRecordSet.Fields(2).Nam e, rstRecordSet.Fields(3).Nam e, _
rstRecordSet.Fields(4).Nam e, rstRecordSet.Fields(5).Nam e, _
rstRecordSet.Fields(6).Nam e, rstRecordSet.Fields(7).Nam e, _
rstRecordSet.Fields(8).Nam e, rstRecordSet.Fields(9).Nam e, _
rstRecordSet.Fields(10).Na me, rstRecordSet.Fields(11).Na me, _
rstRecordSet.Fields(12).Na me, rstRecordSet.Fields(13).Na me, _
rstRecordSet.Fields(14).Na me, rstRecordSet.Fields(15).Na me, _
rstRecordSet.Fields(16).Na me, rstRecordSet.Fields(17).Na me, _
rstRecordSet.Fields(18).Na me, rstRecordSet.Fields(19).Na me
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
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.T
strEndDate = ConvertDate(txtEndDate.Tex
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).Nam
rstRecordSet.Fields(2).Nam
rstRecordSet.Fields(4).Nam
rstRecordSet.Fields(6).Nam
rstRecordSet.Fields(8).Nam
rstRecordSet.Fields(10).Na
rstRecordSet.Fields(12).Na
rstRecordSet.Fields(14).Na
rstRecordSet.Fields(16).Na
rstRecordSet.Fields(18).Na
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...)
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...)
ASKER
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.
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.
ASKER
When you say It sounds like the "Start-up Object" for your project is set to the Module. How can I change it...
ASKER
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
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
ASKER
Ok I just changed the project proprties to start wit the form1. Let me see if that works
ASKER
expert Jim , could you pl. tell me why u have
"AND PT. TYPE IN (" & strWherePTTypeIn & ")"
why not
"AND PT. TYPE IN ( & strWherePTTypeIn )
"AND PT. TYPE IN (" & strWherePTTypeIn & ")"
why not
"AND PT. TYPE IN ( & strWherePTTypeIn )
ASKER
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 )
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 )
ASKER
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
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.
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.
ASKER
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..
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..
ASKER
Also how can I truncate the LAST COMMA from the string, after 'RT'?
' AC' , 'BC', '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.
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.
>> 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.
ASKER
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!!!
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.
ASKER
Hi fds fatboy, I gave you some points becase ur genreal suggestion was useful and I appreciate that.
ASKER