Searching in more than one column

Tags: sql excel vba adodb
By : Thomas
Source: Stackoverflow.com
Question!

I have an access database I search through from excel with the help of VBA and ADODB.

It works perfectly fine, but I can only search for one thing at a time. Say I want to search for all instances of "first name" in column [1] that also has "last name" in column [2]. Or, "first name" in column [1] and "address" in column [3].

How would I go about doing that from a textbox on a userform?

I was thinking of somehow adding "Firstname + Lastname" or something, but I cannot figure out the logic of the code. Any suggestions?

This is what I have for now.

Private Sub cmd_lookup_Click()

    Dim cn As Object
    Dim rs As Object

    Set cn = CreateObject("ADODB.Connection")
    Set sqlConnect = New ADODB.Connection
    Set rs = CreateObject("ADODB.RecordSet")

    sqlConnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Source\database.accdb;Persist Security Info=False;"

    cn.Open sqlConnect

    rs.ActiveConnection = cn

    SearchCriteria = "%" & searchCrit.Text & "%"

    rs.Open "SELECT [1],[2] ,[3],[4], [5] FROM [tblDatabase]" & _
    "WHERE      [1] LIKE '" & SearchCriteria & "' " & _
    "           OR [2] LIKE '" & SearchCriteria & "' " & _
    "           OR [3] LIKE '" & SearchCriteria & "' " & _
    "           OR [4] LIKE '" & SearchCriteria & "' " & _
    "           OR [5] LIKE '" & SearchCriteria & "' " & _
    "ORDER BY [2] Desc;", _
         cn, adOpenStatic

Dim i As Integer
If Not rs.EOF Then
    rs.MoveFirst
    i = 0
    With lstLookup
        'Code not relevant
    End With

    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
  End If
End Sub

Thanks for the suggestions :)

By : Thomas


Answers

Consider AND parings separated by OR operators. But first have userform structured with separate text fields (and each must be a required field else you must build WHERE clause dynamically):

First Name Search:_____________
Last Name Search: _____________
Address Search: _______________

And then bind criteria together. Also, I would not advise numbered but named fields for debugging reasons:

rs.Open "SELECT [1], [2], [3], [4], [5]" & _
        " FROM [tblDatabase]" & _
        " WHERE ([1] LIKE '%" & FirstNameSearch & "%' " & _
        "       AND [2] LIKE '%" & LastNameSearch & "%') " & _
        " OR ([1] LIKE '%" & FirstNameSearch & "%' " & _
        "      AND [3] LIKE '%" & AddressSearch & "%') " & _
        " OR ([2] LIKE '%" & LastNameSearch & "%' " & _
        "    AND [3] LIKE '%" & AddressSearch & "%') " & _
        " ORDER BY [2] Desc;", _
        cn, adOpenStatic

Alternatively, use a UNION query as often in the SQL world it is discussed the efficiency between UNION vs OR. To be truly equivalent, a DISTINCT should be added to above for both to remove duplicates.

rs.Open "SELECT [1], [2], [3], [4], [5]" & _
        " FROM [tblDatabase]" & _
        " WHERE ([1] LIKE '%" & FirstNameSearch & "%' " & _
        "        AND [2] LIKE '%" & LastNameSearch & "%') " & _
        " UNION" _
        " SELECT [1], [2], [3], [4], [5]" & _
        " FROM [tblDatabase]" & _
        " WHERE ([1] LIKE '%" & FirstNameSearch & "%' " & _
        "        AND [3] LIKE '%" & AddressSearch & "%') " & _
        " UNION" _
        " SELECT [1], [2], [3], [4], [5]" & _
        " FROM [tblDatabase]" & _
        " WHERE ([2] LIKE '%" & LastNameSearch & "%' " & _
        "        AND [3] LIKE '%" & AddressSearch & "%') " & _
        " ORDER BY [2] Desc;", _
        cn, adOpenStatic
By : Parfait


Here's a pretty easy way to sort the db return into separate arrays and then display them. @caralot stole my thunder so I came up with this alternative.

Using your current model return $this->db->get('prices')->result_array(); and assigning it to $data.

$data = $this->db->functionName();
//You should check $data validity but I'm skipping that
$month1 = [];
$month2 = [];
$month3 = [];
foreach($data as $row)
{
    if($row['months'] === '1')
    {
        $month1[] = $row;
    }
    elseif($row['months'] === '2')
    {
        $month2[] = $row;
    }
    else
    {
        $month3[] = $row;
    }
}
echo "Month 1<br>";
foreach($month1 as $month){
     echo "Level ". $month['level'].' is '.$month['amount'].'<br>';
}
echo "Month 2<br>";
foreach($month2 as $month){
     echo "Level ".$month['level'].' is '.$month['amount'].'<br>';
}
echo "Month 3<br>";
foreach($month3 as $month){
     echo "Level ".$month['level'].' is '.$month['amount'].'<br>';
}

If your table was less ordered than what you show it would be necessary to add a $this->db->order_by('level', 'ASC'); call to the query.

By : DFriend


i did not test this so might have made a stupid error - but basically you can foreach through each of your products - make an array - and then use that array in your view.

    // in your model Note I am returning an object not an array 
    // and always check to make sure something got returned 
    if( ! $products = $this->db->get('prices')->result() )
    {
       return false: 
    } 
    else
    {     
    $prices = array(); 

    foreach($products as $product)
    {
       // append the months number to the word 'months' to make it clear
       $month =  $product->months . 'month' ; 

       // same with level 
       $level =  'level' . $product->level ; 

       // build the array
       $prices[$month][$level] = $product->amount ; 

    }//foreach

    return $prices ; 

   }//else

so then in your controller - make sure something came back from the model, assign it to data, then pass data to your view

   if( ! $data['prices'] = $this->somemodelname->returnPrices() )
   {
       $this->showError() ; 
   }
   else
   {
       $this->load->view('yourviewname', $data); 
   }  

and then in your view you could foreach or just echo out each price if it needs to follow some layout.

 echo '1 month level 1 $' . $prices['1month']['level1'] ; 

and remember your best friend when doing arrays is print_r wrapped in pre tags so like

  echo 'start prices <br> <pre>' ;
    print_r($prices) ;
   echo '</pre>' ;

opinions - its fine to build stuff in the controller and the view while you are developing and building out. but get in the habit of refactoring to your models. keep your controllers as clean and thin as possible. if your views need complicated data structures - build them in a model first. that way if something goes wrong - your controller can decide what to do. AND you don't have to check in your view if $prices is set and valid because you have already done it. this minimizes where things can go wrong.

By : cartalot


This video can help you solving your question :)
By: admin