need a query that adds rather than takes away 1

[chaft]

I have a query set up that looks to find contacts using criteria. using queries much like

Like [forms]![searchall]![Surname] & "*"

This allows me to search and find people that are in many categories. However that doesn't suit my real purpose.

I want to be able to select the categories and have all the people in those groups to appear in my query. It doesn't work right now as I select many categories the list gets shorter and shorter as it looks people that matches "all" rather than just one category as I want.


for instance

my categories:

mammals birds fish

the data:

whales crow catfish
apes robin plaice



i want to show in my query all the birds and mammals but in this instance i get none returned as using the code up above it is looking to find a creature that is a bird AND a Mammal.


Any IDEAS?

 

[dhookom]

Share your current SQL. I expect you are using "AND" in the where condition when you should be using "OR".

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

Your question is basically "Why doesn't my SQL work?" yet you don't display your SQL, therefore we can not make any absolute diagnosis.

But as Duane says it is most likely a case of using AND when you should use OR (but it could be something else, like what joins you make between tables).

 

[chaft]

ok... it's quite large

SELECT zearchall.ContactDetailID AS Contactid, Trim([Title] & "") AS Titles, Trim([FirstName] & "") AS [First Name], Trim([Surname] & "") AS Surnames, Trim([gender] & "") AS Genders, Trim([Dateofbirth] & "") AS [Date Of Birth], Trim([Nationality] & "") AS Nationalitys, Trim([HaveChildren] & "") AS [Have Children], Trim([IsStudent] & "") AS Students, Trim([IsHousecouncil] & "") AS HouseCouncils, Trim([IsSuperior] & "") AS Superiors, Trim([ISExtConference] & "") AS Extconf, Trim([IsVicarsofFriendlyParishes] & "") AS Vicars, Trim([isbenefactor] & "") AS Benefactors, Trim([IsKeepinTouch] & "") AS KeepinTouch, Trim([IsStaff] & "") AS Staffs, Trim([IsDeceased] & "") AS Deceased, zearchall.HomeName, zearchall.HomeNumber, zearchall.HomeStreet, zearchall.HomeCity, zearchall.[HomeCounty/state], zearchall.[HomePostcode/ZipCode], zearchall.Country, zearchall.ChurchAddressingTerm, zearchall.HaveChildren, zearchall.SpouseFirstName, zearchall.SpouseTitle, zearchall.SpouseSurname, zearchall.IsHousecouncil, zearchall.SpouseAddressingTerm, zearchall.SpouseFullEnvelopeTitle, zearchall.SuperiorTitle, zearchall.SuperiorFullenvelopetitle, zearchall.SuperiorAddressTerm, zearchall.SuperiorOrderName, zearchall.SuperiorNameofRelHouse, zearchall.SuperiorTitle
FROM zearchall
WHERE (((Trim([Title] & "")) Like [forms]![searchall]![Title] & "*") AND ((Trim([FirstName] & "")) Like [forms]![searchall]![FirstName] & "*") AND ((Trim([Surname] & "")) Like [forms]![searchall]![Surname] & "*") AND ((Trim([gender] & "")) Like [forms]![searchall]![gender] & "*") AND ((Trim([Dateofbirth] & "")) Like [forms]![searchall]![dateofbirth] & "*") AND ((Trim([Nationality] & "")) Like [forms]![searchall]![Nationality] & "*") AND ((Trim([HaveChildren] & "")) Like [forms]![searchall]![HaveChildren] & "*") AND ((Trim([IsStudent] & "")) Like [forms]![searchall]![Student] & "*") AND ((Trim([IsHousecouncil] & "")) Like [forms]![searchall]![HouseCouncil] & "*") AND ((Trim([IsSuperior] & "")) Like [forms]![searchall]![Superior] & "*") AND ((Trim([ISExtConference] & "")) Like [forms]![searchall]![Extconference] & "*") AND ((Trim([IsVicarsofFriendlyParishes] & "")) Like [forms]![searchall]![Vicars] & "*") AND ((Trim([isbenefactor] & "")) Like [forms]![searchall]![isbenefactor] & "*") AND ((Trim([IsKeepinTouch] & "")) Like [forms]![searchall]![KeepinTouch] & "*") AND ((Trim([IsStaff] & "")) Like [forms]![searchall]![Staff] & "*") AND ((Trim([IsDeceased] & "")) Like [forms]![searchall]![IsDeceased] & "*")) OR (((Trim([Title] & "")) Like [forms]![searchall]![Title] & "*") AND ((Trim([FirstName] & "")) Like [forms]![searchall]![FirstName] & "*") AND ((Trim([Surname] & "")) Like [forms]![searchall]![Surname] & "*") AND ((Trim([gender] & "")) Like [forms]![searchall]![gender] & "*") AND ((Trim([Dateofbirth] & "")) Like [forms]![searchall]![dateofbirth] & "*") AND ((Trim([Nationality] & "")) Like [forms]![searchall]![Nationality] & "*") AND ((Trim([HaveChildren] & "")) Like [forms]![searchall]![HaveChildren] & "*") AND ((Trim([IsStudent] & "")) Like [forms]![searchall]![Student] & "*") AND ((Trim([IsHousecouncil] & "")) Like [forms]![searchall]![HouseCouncil] & "*") AND ((Trim([IsSuperior] & "")) Like [forms]![searchall]![Superior] & "*") AND ((Trim([ISExtConference] & "")) Like [forms]![searchall]![Extconference] & "*") AND ((Trim([IsVicarsofFriendlyParishes] & "")) Like [forms]![searchall]![Vicars] & "*") AND ((Trim([isbenefactor] & "")) Like [forms]![searchall]![isbenefactor] & "*") AND ((Trim([IsKeepinTouch] & "")) Like [forms]![searchall]![KeepinTouch] & "*") AND ((Trim([IsStaff] & "")) Like [forms]![searchall]![Staff] & "*") AND ((Trim([IsDeceased] & "")) Like [forms]![searchall]![IsDeceased] & "*"));

 

[chaft]

I tried changing all the AND's to OR but that didn't seem to do anything..

 

[dhookom]

Will the results be displayed in a report or form? Are each of the fields in the record source of the form/report?

If you just want to allow users to query based on any number of fields, I would build the SQL statement dynamically and use code to change the SQL of a saved query. There is no need to include all the criteria in the query if they aren't required.

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

First of all, you have duplicated the search fields. Your tests go from "Title" to "isdeceased" and then the whole block is repeated.

Second, the construct

... AND ((Trim([IsDeceased] & "")) Like [forms]![searchall]![IsDeceased] & "*")) 
OR (((Trim([Title] & "")) Like [forms]![searchall]![Title] & "*")

seems redundant because you already have

Trim([Title] & "")) Like [forms]![searchall]![Title] & "*")

at the beginning of the WHERE caluse.

Third ... Using AND means that every test must be satisfied and the more tests you define, the fewer records will qualify. If you use OR then meeting any of the tests will include the record. With this many tests it seems unlikely that using one or the other exclusively will meet your needs. You do understand that the tests in the WHERE clause are applied to each individual record and, as you have constructed it, each such record must pass every test to be included.

I would agree with dhookom. Build some code to dynamically construct your WHERE clause

 

[JoeAtWork]

Another thing to note is what appears to be an over-use of LIKE.

The following:
((Trim([Dateofbirth] & "")) Like [forms]![searchall]![dateofbirth] & "*")

will likely not work. With dates you search within a range, or look for an exact date. If the user typed in "March 30, 2008" no records would be returned, even if there were records with that date.

The same with boolean fields, which appears to be the case with:
((Trim([IsDeceased] & "")) Like [forms]![searchall]![IsDeceased] & "*"))

Assuming IsDeceased is boolean, it's going to be either True or False. Instead of LIKE you should be using an equals (=) here.

LIKE is only relevant to text fields.

 

[chaft]

You were all quite right. I played around and got it to work and naturally came to the same-ish conclusion as Joe At Work came to. You are quite right Gloom too. (thanks all)


I haven't used an OR clause in so long that I forgot how to do them properly.


Question is now...(not essential but worth knowing)

How do you get AND and OR statements to work on the same Query?

I have set up a purly OR clause driven query which I would like to refine with an AND statement. So that I could take away the deceased person records but my AND statements don't work at all with the OR clauses.


I could make this a separate thread if anyone insists. But I feel it is the natural progression of my original thread.

 

[PHV]

Go to the SQL view pane and write your own WHERE clause taking care of parenthesis when mixing OR and AND operators, eg:
WHERE (A and B) OR (C and D)

WHERE (A or B) AND (C or D)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

For complex queries I tend to write the SQL directly rather than use the query designer - it is closer to a human language than the visual designer is.

For search screens that have optional fields to search against, it is much better to build up your SQL statement dynamically rather than have one massive catch-all query. There's a couple reasons:

1. Maintainability - as you are finding out, it gets very difficult to keep straight in your head all the possible combinations of searching.
2. Efficiency - if the user has not filled out the DOB and FirstName fields, then there is no reason to search against them. The less fields to search against, the quicker the query will be. Especially if you can leave out the non-indexed fields.

I've seen search screens like yours that took minutes to do a search even when the user's only parameter was the indexed primary key. That's because the query was including all the other fields so that you had a bunch of parameters such as "Description LIKE '*' AND LastName LIKE '*'"...

Searching against these un-indexed text fields was slowing the query down by many magnitudes. I sped up the searches from minutes to seconds simply by building up the SQL statement to only include fields in the WHERE claused that the user was actually searching against.

Here's an example of this technique, taken from another project:

'Created:       22-Jan-2007
'Version:       1.94.8
'Programmer:    Joe Schwarz
'Purpose:       Dynamically builds the SQL statement to query for the list of orders
'               according to current search parameters.  This replaces the original
'               programmer's highly inefficient OR-everything-together query
'               (qry_PointOfSales).
Private Sub RequeryList(Optional SpecificCriteria As String = "")
    Dim SQL As String
    Dim WHERE As String
    Dim ORDER_BY As String
    Dim Criteria As String
    Dim Criteria_WHERE As String
    Dim TempPhone As String
    Dim TempInvoiceNo As String
    Dim OrderStatus As Integer
    Dim dteTempDate As Date
    
    On Error GoTo ErrHandler
    
    Call HourglassMouse
         
    SQL = "SELECT DISTINCTROW [Invoice #], Customer, Phone, " & _
     "[Ship To], Format(OrderedDate,""d/m/yy"") AS Ordered, Format(ArrivingDate,""d/m/yy"") AS Arriving, Closed, IsEstimate AS [Is Estimate] " & _
     "FROM qry_POS_List"
     
    'Always will filter out soft-deleted orders
    WHERE = " WHERE IsDeleted=False"
     
    'Filter by order status open/closed?
    OrderStatus = Nz(cboSHow, STATUS_ALL)
    If OrderStatus = STATUS_OPEN Then
        WHERE = WHERE & " AND Closed = 'No'"
    ElseIf OrderStatus = STATUS_CLOSED Then
        WHERE = WHERE & " AND Closed = 'Yes'"
    End If

    'Filter by CommissionID?
    If Nz(cboCommissionTo, 0) <> 0 Then
        WHERE = WHERE & " AND CommissionEmployeeID=" & cboCommissionTo
    End If
    
    'Filter for Orders or Estimates only?
    If Nz(cboType, 2) <> 2 Then
        If cboType = 0 Then
            WHERE = WHERE & " AND IsEstimate = 'No'"
        Else
            WHERE = WHERE & " AND IsEstimate = 'Yes'"
        End If
    End If
    
    'Filter by date range?
    If Nz(txtDateFrom, "") <> "" Or Nz(txtDateTo, "") <> "" Then
        If Nz(txtDateFrom, "") = "" Then txtDateFrom = txtDateTo
        If Nz(txtDateTo, "") = "" Then txtDateTo = txtDateFrom
        
        'Automatically fix unlogical date range
        If txtDateTo < txtDateFrom Then
            dteTempDate = txtDateTo
            txtDateTo = txtDateFrom
            txtDateFrom = dteTempDate
        End If
        
        WHERE = WHERE & " AND (OrderedDate Between #" & Format(txtDateFrom, "medium date") & _
         "# AND #" & Format(txtDateTo, "medium date") & "#)"
         
    End If
    
    'Check to search against any of Invoice #, Phone #, or Customer Name
    If Len(SpecificCriteria) > 0 Then
        Criteria = SpecificCriteria
    Else
        Criteria = Trim(Nz(txtName, ""))
    End If
    
    If Criteria <> "" Then
        'Check against Company or Ship To fields
        Criteria_WHERE = "(Customer Like ""*" & Criteria & "*"")"
        Criteria_WHERE = Criteria_WHERE & " OR " & _
         "([Ship To] Like ""*" & Criteria & "*"")"
        
        'Check to see if this could be a phone number
        TempPhone = Criteria
        TempPhone = Replace(TempPhone, "-", "")
        TempPhone = Replace(TempPhone, " ", "")
        TempPhone = Replace(TempPhone, "(", "")
        TempPhone = Replace(TempPhone, ")", "")
        
        If IsNumeric(TempPhone) Then
            If Len(TempPhone) >= 7 Then
                Criteria_WHERE = Criteria_WHERE & " OR (IIf(Not IsNull([phone])," & _
                 "Replace(Replace(Replace(Replace([Phone],""-"",""""),"" "",""""),"")"",""""),""("","""") " & _
                 "Like ""*" & TempPhone & "*""))"
            End If
        End If
        
        'Check if there's a possibility of an invoice number (use the telephone variable which
        'has already stripped out the dashes, spaces, and paranthesis)
        TempInvoiceNo = Val(TempPhone)
        If TempInvoiceNo > 0 Then
            Criteria_WHERE = Criteria_WHERE & " OR ([Invoice #] Like ""*" & _
             TempInvoiceNo & "*"")"
        End If
        
    End If
    
    'Join the text-based search criteria to the other search criteria (it is surrounded in
    'parenthesis because there may be up to 3 fields being searched against that are OR'd
    'together)
    If Len(Criteria_WHERE) > 0 Then
        WHERE = WHERE & " AND (" & Criteria_WHERE & ")"
    End If
    
    'Join the SELECT statement with the WHERE statement
    SQL = SQL & WHERE
    
    ORDER_BY = " ORDER BY Customer, [Invoice #] DESC"
    SQL = SQL & ORDER_BY
    
    lstOrders.RowSource = SQL
    lstOrders.Requery
    
Exit_Routine:
    Call DefaultMouse
    
    Exit Sub
    
ErrHandler:
    MsgBox "Error occurred while trying to build search filter, error # " & _
     Err.Number & ": " & vbCrLf & Err.Description, vbExclamation, "Error in RequeryList"
     
    GoTo Exit_Routine
End Sub

As you can see, I am only including in the WHERE clause those search fields that the user has actually filled out.

 

[chaft]

Gulp!

I did some SQL at uni and so some of the instructions look familar. However I've not really done anything like this where I'm mixing my vb skills with it.

If I get my next job I'll probably learn and do some Sql. The advantage with Access was that I didn't need to any SQL. I'm not even sure if the SQL I learnt on Oracle would work in Access.

You are right about those last points Joe. It's too late for me to add this into my project as it would take a week or so just to understand what is going on.

I may try PHV's idea though briefly

 

[BigRed1212]

Joe I think that is cool.

Build a partial SELECT statement with all but the WHERE clause.

Run through a series of IF's testing whatever critria you want and building as many different WHERE elements as you want based on your tests.

Concatenate the built up WHERE's to the partial SELECT.

If I ever have to do a query that complex I going to do it this way. I'm marking this in my archive thingie.