Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search Fields on form Conditionally (VB6)

    I have a form in VB that the users have requested the ability to search any combination of fields. I created a function that grabs a disconnected recordset from a passed in SQL string. I thought it would be as simple as passing in each possible field in the WHERE clause and just omit the value if there is none. I'm finding that my thinking is wrong. Here is the SQL string I'm passing in:

    Set RS = GetRS("SELECT tblPatient.UID, tblPatient.MRNumber, tblPatient.COG_ID, " _
    & "tblPatient.DOB, tblPatient.Gender, tblPatient.PMD, " _
    & "tblPatient.CRA, tblPatient.DID, tblPatient.DXDate, " _
    & "tblPatient.FirstName, tblpatient.lastname " _
    & "FROM tblPatient " _
    & "WHERE (((tblPatient.MRNumber)= " & IIf(IsNull(tdbMRN.Value), vbNullString, " & tdbMRN.Value & ") & " ) " _
    & "AND ((tblPatient.LastName)= '" & TDBLname.Text & "') " _
    & "AND ((tblPatient.FirstName)='" & TDBFname.Text & "') " _
    & "AND ((tblPatient.COG_ID)='" & TDBCOG.Text & "') " _
    & "AND ((tblPatient.DOB)=" & IIf(IsNull(tdbMRN.Value), vbNullString, " & tdbdob.Value & ") & ") " _
    & "AND ((tblPatient.Gender)='" & cboGender.Text & "') " _
    & "AND ((tblPatient.PMD)='" & TDBPMD.Text & "') " _
    & "AND ((tblPatient.CRA)='" & TDBCRA.Text & "') " _
    & "AND ((tblPatient.DID)='" & cboDiag.Text & "') " _
    & "AND ((tblPatient.DXDate)=" & IIf(IsNull(tdbMRN.Value), vbNullString, " & tdbDatedx.Value & ") & "))")

    Now it works if I were to supply a value for each field, but it doesn't know what to do with no value (or rather it is passing nothing to use in the query, such as the vbNullString literally).

    I know in Access I can have the fields displayed and not give a value using the query grid. Should I create a query on the back end (which is Access 2K, by the way) and explicitly call that query?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Search Fields on form Conditionally (VB6)

    It would make your code much longer, but all you need to do is have 10 If structures to test whether the boxes are empty and build the WHERE clause with the non-empty stuff. If you use a temporary string for this, you can test whether it is empty to know whether to add Ands before adding new tests, and to know whether there are any criteria at all. This is cryptic, but does it make sense?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search Fields on form Conditionally (VB6)

    You don't even need 10 separate structures. Just use a select case inside a For ... Next loop to test each control and add any value to the WHERE clause. Whatever has been built when you finally exit the loop is the WHERE clause.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search Fields on form Conditionally (VB6)

    I like both of your suggestions. The Select Case seems like it would be more streamline. I haven't been able to get the .ControlType to work in VB like it does in Access, so I've been grabbing the prefix values I used in my naming convention for the textboxes.

    So are you suggesting that I use the for each control in controls type of loop, then embed a Select Case for each field in the SELECT and if, say, the len() is > 0, append that value to the WHERE clause?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search Fields on form Conditionally (VB6)

    Something like that. Actually, I was thinking of a case for each control to see if you needed to include that field in the the SQL. Something like this:

    <pre>For Each ctl in Me.Controls

    Select Case ctl.Name
    Case "tdbMRN"
    If Len(me.tdbMRN & vbNullString)>0 Then
    strSQL = strSQL & "tblPatient.MRNumber=" & Me.tdbMRN & " AND "
    End If

    Case ...
    End Select
    Next ctl</pre>


    Then you would have to trim off any dangling " AND ", but the WHERE clause would contain only those values you actually wanted to use for criteria.

    If the controls are in an array, you don't even have to mess with their names, just loop through the indexes. I would use the Tag property of each control to store the <table name>.<field name> string and just retrieve that at the same time. In fact, even without a control array, I'd use the Tag property that way. Then you would only need to use the select case to test for typeof like this:

    <pre> Dim ctl As Control

    For Each ctl In Me.Controls
    Select Case ctl
    Case TypeOf ctl Is TextBox
    If Len(ctl.Text & vbNullString) > 0 Then
    strWhere = strWhere & ctl.Tag & "=" & ctl.Text & " AND "
    Case ....
    End Select
    Next ctl</pre>

    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •