The InStr Function??


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: The InStr Function??

  1. #1
    Brad Isaacs Guest

    The InStr Function??


    Dear friends,

    I have a combo box which I have Populated with 2 recordsets, no my problem
    is when I search one of the fields within the db's I am only searching forthe
    first field within my combo box, so I have to use the InStr Function, only
    problem is how do I use this in an SQL statement to search.

    Here is my Populate code for the combo box:
    Private Sub PopulatecboStudyID()
    '******************************************************
    '* Populate the cboStudyID combobox with all the *
    '* existing StudyID's and StudyDrug *
    '* names within the database. *
    '******************************************************
    Dim rsSubjectIDName As New ADODB.Recordset
    Dim SQL As String

    Set rsSubjectIDName = New ADODB.Recordset

    'Sorts StudyID field Ascending(by default)
    SQL = "SELECT * FROM Study " & _
    "ORDER BY StudyID"

    rsStudyID.Open SQL, datCrystal.cnCrystalReports.ConnectionString


    With cboStudyID
    ' Add "<All Studies>"
    ' to the cboStudyID Using this as a marker to see what the
    ' user has chosen

    .Clear
    .AddItem "<All Studies>"
    .ItemData(.NewIndex) = 0 ' No StudyID's will ever have a Index
    of 0

    If (rsStudyID.BOF And rsStudyID.EOF) Then
    Exit Sub ' No StudyID in the database
    Else
    rsStudyID.MoveFirst
    End If

    ' Populate the control with StudyID from the database
    While Not rsStudyID.EOF
    .AddItem rsStudyID!StudyID & " " & rsStudyID!StudyDrug
    rsStudyID.MoveNext
    Wend
    End With
    ' Cleanup for memory leaks
    Set rsStudyID = Nothing
    ' Default to the StudyID and Name in the list
    If cboStudyID.ListCount > 0 Then cboStudyID.ListIndex = 0

    End Sub

    I am now trying to search JUST the StudyID field NOT the StudyDrug field,
    my SQL statement is like this, Right now it searches for the whole string
    but I cannot do this because it'll neverfind this string,,,I want to search
    for just the first field name StudyID then stop the search at the black space
    between the two fields, How can I do this in an SQL Statement?

    Any Ideas, suggestions, examples of how I can get this to perform the way
    I would like it?

    Please see below my SQL statement so far...........

    If cboStudyID.Text <> "<All Studies>" Then
    'In SQL, compare cboStudyID with 3 fields (AuditTrailTableKey,
    'AuditTrailFrom and AuditTrailTo) found within Audit Trail Table.
    SQL = SQL & " and (AuditTrailTableKey = '" & cboStudyID.Text
    & "')" & _
    " Or (AuditTrailFrom = '" & cboStudyID.Text & "') Or (AuditTrailTo
    = '" & cboStudyID.Text & "')"
    'MsgBox SQL
    'Display on the report, the StudyID that the user chose
    m_Report.SubTitle.SetText " Audit Trail - StudyID: " & cboStudyID.Text
    End If

    Thanks in advance,

    Brad Isaacs
    Junior VB Developer / Crystal Reports 8.0 / SQL Server 7.0 DB's



  2. #2
    Arthur Wood Guest

    Re: The InStr Function??


    "Brad Isaacs" <bradhouse@sprint.ca> wrote:
    >
    >Dear friends,
    >
    >I have a combo box which I have Populated with 2 recordsets, no my problem
    >is when I search one of the fields within the db's I am only searching forthe
    >first field within my combo box, so I have to use the InStr Function, only
    >problem is how do I use this in an SQL statement to search.
    >
    >Here is my Populate code for the combo box:
    >Private Sub PopulatecboStudyID()
    >'******************************************************
    >'* Populate the cboStudyID combobox with all the *
    >'* existing StudyID's and StudyDrug *
    >'* names within the database. *
    >'******************************************************
    > Dim rsSubjectIDName As New ADODB.Recordset
    > Dim SQL As String
    >
    > Set rsSubjectIDName = New ADODB.Recordset
    >
    > 'Sorts StudyID field Ascending(by default)
    > SQL = "SELECT * FROM Study " & _
    > "ORDER BY StudyID"
    >
    > rsStudyID.Open SQL, datCrystal.cnCrystalReports.ConnectionString
    >
    >
    > With cboStudyID
    > ' Add "<All Studies>"
    > ' to the cboStudyID Using this as a marker to see what the
    > ' user has chosen
    >
    > .Clear
    > .AddItem "<All Studies>"
    > .ItemData(.NewIndex) = 0 ' No StudyID's will ever have a Index
    >of 0
    >
    > If (rsStudyID.BOF And rsStudyID.EOF) Then
    > Exit Sub ' No StudyID in the database
    > Else
    > rsStudyID.MoveFirst
    > End If
    >
    > ' Populate the control with StudyID from the database
    > While Not rsStudyID.EOF
    > .AddItem rsStudyID!StudyID & " " & rsStudyID!StudyDrug
    > rsStudyID.MoveNext
    > Wend
    > End With
    > ' Cleanup for memory leaks
    > Set rsStudyID = Nothing
    > ' Default to the StudyID and Name in the list
    > If cboStudyID.ListCount > 0 Then cboStudyID.ListIndex = 0
    >
    >End Sub
    >
    >I am now trying to search JUST the StudyID field NOT the StudyDrug field,
    >my SQL statement is like this, Right now it searches for the whole string
    >but I cannot do this because it'll neverfind this string,,,I want to search
    >for just the first field name StudyID then stop the search at the black

    space
    >between the two fields, How can I do this in an SQL Statement?
    >
    >Any Ideas, suggestions, examples of how I can get this to perform the way
    >I would like it?
    >
    >Please see below my SQL statement so far...........
    >
    >If cboStudyID.Text <> "<All Studies>" Then
    > 'In SQL, compare cboStudyID with 3 fields (AuditTrailTableKey,
    > 'AuditTrailFrom and AuditTrailTo) found within Audit Trail Table.
    > SQL = SQL & " and (AuditTrailTableKey = '" & cboStudyID.Text
    >& "')" & _
    > " Or (AuditTrailFrom = '" & cboStudyID.Text & "') Or (AuditTrailTo
    >= '" & cboStudyID.Text & "')"
    > 'MsgBox SQL
    > 'Display on the report, the StudyID that the user chose
    > m_Report.SubTitle.SetText " Audit Trail - StudyID: " & cboStudyID.Text
    > End If
    >
    >Thanks in advance,
    >
    >Brad Isaacs
    >Junior VB Developer / Crystal Reports 8.0 / SQL Server 7.0 DB's
    >
    >



  3. #3
    Arthur Wood Guest

    Re: The InStr Function??


    Brad,
    Sorry about the blank response...

    What you need to to is to PARSE the entry, if it is not "<All Studies>"
    That is you have to write some code which will pull off the value of the
    StudyID from the beginning of the Line:

    Dim strIDText as String
    Dim lStudyID as Long
    Dim iPos as Integer

    strIDTest = cboStudyID.Text
    iPos = Instr(" ", strIDText)

    If iPos > 0 then
    lStudyID = CLng(Left$(strIDText,ipos-1)
    end if

    now you will have the StudyID value (as a Long) pulled from the beginning
    ot the user's choice from the Combo Box. That can then be used in you SQL
    Where Clause.

    Does This help?

    Arthur Wood




    "Brad Isaacs" <bradhouse@sprint.ca> wrote:
    >
    >Dear friends,
    >
    >I have a combo box which I have Populated with 2 recordsets, no my problem
    >is when I search one of the fields within the db's I am only searching forthe
    >first field within my combo box, so I have to use the InStr Function, only
    >problem is how do I use this in an SQL statement to search.
    >
    >Here is my Populate code for the combo box:
    >Private Sub PopulatecboStudyID()
    >'******************************************************
    >'* Populate the cboStudyID combobox with all the *
    >'* existing StudyID's and StudyDrug *
    >'* names within the database. *
    >'******************************************************
    > Dim rsSubjectIDName As New ADODB.Recordset
    > Dim SQL As String
    >
    > Set rsSubjectIDName = New ADODB.Recordset
    >
    > 'Sorts StudyID field Ascending(by default)
    > SQL = "SELECT * FROM Study " & _
    > "ORDER BY StudyID"
    >
    > rsStudyID.Open SQL, datCrystal.cnCrystalReports.ConnectionString
    >
    >
    > With cboStudyID
    > ' Add "<All Studies>"
    > ' to the cboStudyID Using this as a marker to see what the
    > ' user has chosen
    >
    > .Clear
    > .AddItem "<All Studies>"
    > .ItemData(.NewIndex) = 0 ' No StudyID's will ever have a Index
    >of 0
    >
    > If (rsStudyID.BOF And rsStudyID.EOF) Then
    > Exit Sub ' No StudyID in the database
    > Else
    > rsStudyID.MoveFirst
    > End If
    >
    > ' Populate the control with StudyID from the database
    > While Not rsStudyID.EOF
    > .AddItem rsStudyID!StudyID & " " & rsStudyID!StudyDrug
    > rsStudyID.MoveNext
    > Wend
    > End With
    > ' Cleanup for memory leaks
    > Set rsStudyID = Nothing
    > ' Default to the StudyID and Name in the list
    > If cboStudyID.ListCount > 0 Then cboStudyID.ListIndex = 0
    >
    >End Sub
    >
    >I am now trying to search JUST the StudyID field NOT the StudyDrug field,
    >my SQL statement is like this, Right now it searches for the whole string
    >but I cannot do this because it'll neverfind this string,,,I want to search
    >for just the first field name StudyID then stop the search at the black

    space
    >between the two fields, How can I do this in an SQL Statement?
    >
    >Any Ideas, suggestions, examples of how I can get this to perform the way
    >I would like it?
    >
    >Please see below my SQL statement so far...........
    >
    >If cboStudyID.Text <> "<All Studies>" Then
    > 'In SQL, compare cboStudyID with 3 fields (AuditTrailTableKey,
    > 'AuditTrailFrom and AuditTrailTo) found within Audit Trail Table.
    > SQL = SQL & " and (AuditTrailTableKey = '" & cboStudyID.Text
    >& "')" & _
    > " Or (AuditTrailFrom = '" & cboStudyID.Text & "') Or (AuditTrailTo
    >= '" & cboStudyID.Text & "')"
    > 'MsgBox SQL
    > 'Display on the report, the StudyID that the user chose
    > m_Report.SubTitle.SetText " Audit Trail - StudyID: " & cboStudyID.Text
    > End If
    >
    >Thanks in advance,
    >
    >Brad Isaacs
    >Junior VB Developer / Crystal Reports 8.0 / SQL Server 7.0 DB's
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center