Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Help (2000)

    I'm trying to write a "search" query that will allow me to search by date, title, and department (or any combination thereof). I have created a form that will allow a user to search by these different parameters.

    Here's my SQL:
    SELECT qryOrdersAll.*
    FROM qryOrdersAll
    WHERE (((qryOrdersAll.DEPT)=[Forms]![frmTechServicesOrdering]![txtDeptTest]) AND ((qryOrdersAll.ORDER_DATE) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate])) OR (((qryOrdersAll.ORDER_DATE) Between [Forms]!
    [frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND ((qryOrdersAll.TITLE) Like "*" & [Forms]!
    [frmTechServicesOrdering]![txtTitleSearch] & "*"));

    It's searching by date and title just fine, but when I try
    to search by date and department, it finds all of the
    records. I am passing the department ID number, which is
    the DEPT field. The DEPT field is a text field.

    Can anyone help?

    Thanks,
    Kindra

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2000)

    The reason all records are coming back is because of the OR statement. When you select Date and Department, the Or statement, in effect, says return All Records between the two dates because of the following:
    ((qryOrdersAll.TITLE) Like "*" & [Forms]![frmTechServicesOrdering]![txtTitleSearch] & "*"));. The *s cause all records to return when the text box is null. Try removing the *s to see if it fixes the date and department search (it should). If you need to keep the *s, add a field in your query that references [Forms]![frmTechServicesOrdering]![txtTitleSearch] and set the criteria to Null in the first part of your statement. This will allow you greater control over how the query should run.
    HJope that helps.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Help (2000)

    Dashiell has already explained that the OR is the problem. You can also try to use AND instead of OR. In that case, the WHERE part can be simplified to

    WHERE (ORDER_DATE Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND DEPT=[Forms]![frmTechServicesOrdering]![txtDeptTest] AND TITLE Like "*" & [Forms]![frmTechServicesOrdering]![txtTitleSearch] & "*";

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2000)

    When I do what you have suggested Hans, it will now look up the Departments but not search by the title/date range combination. Any thoughts?

    Dashiell, can you give me a little bit more info on what you mean by:

    "Try removing the *s to see if it fixes the date and department search (it should). If you need to keep the *s, add a field in your query that references [Forms]![frmTechServicesOrdering]![txtTitleSearch] and set the criteria to Null in the first part of your statement. This will allow you greater control over how the query should run."

    ?

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2000)

    Try the attached statement (it provides a long answer but should help you see what I mean by creating a field based on your form and then using Null to help contol the query):

    SELECT qryOrdersAll.*
    FROM qryOrdersAll
    WHERE (((qryOrdersAll.Dept)=[Forms]![frmTechServicesOrdering]![txtDeptTest]) AND ((qryOrdersAll.Order_Date) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND ((qryOrdersAll.Title)=[Forms]![frmTechServicesOrdering]![txtTitleSearch])) OR (((qryOrdersAll.Order_Date) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND (([Forms]![frmTechServicesOrdering]![txtDeptTest]) Is Null) AND (([Forms]![frmTechServicesOrdering]![txtTitleSearch]) Is Null)) OR (((qryOrdersAll.Order_Date) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND ((qryOrdersAll.Title)=[Forms]![frmTechServicesOrdering]![txtTitleSearch]) AND (([Forms]![frmTechServicesOrdering]![txtDeptTest]) Is Null)) OR (((qryOrdersAll.Dept)=[Forms]![frmTechServicesOrdering]![txtDeptTest]) AND ((qryOrdersAll.Order_Date) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) AND (([Forms]![frmTechServicesOrdering]![txtTitleSearch]) Is Null)) OR (((qryOrdersAll.Dept)=[Forms]![frmTechServicesOrdering]![txtDeptTest]) AND ((qryOrdersAll.Title)=[Forms]![frmTechServicesOrdering]![txtTitleSearch]) AND (([Forms]![frmTechServicesOrdering]![txtStartDate]) Is Null)) OR (((qryOrdersAll.Dept)=[Forms]![frmTechServicesOrdering]![txtDeptTest]) AND (([Forms]![frmTechServicesOrdering]![txtTitleSearch]) Is Null) AND (([Forms]![frmTechServicesOrdering]![txtStartDate]) Is Null)) OR (((qryOrdersAll.Title)=[Forms]![frmTechServicesOrdering]![txtTitleSearch]) AND (([Forms]![frmTechServicesOrdering]![txtDeptTest]) Is Null) AND (([Forms]![frmTechServicesOrdering]![txtStartDate]) Is Null)) OR ((([Forms]![frmTechServicesOrdering]![txtDeptTest]) Is Null) AND (([Forms]![frmTechServicesOrdering]![txtTitleSearch]) Is Null) AND (([Forms]![frmTechServicesOrdering]![txtStartDate]) Is Null));

    Let me know how it works.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2000)

    Hi Dashiell --

    I tried using this and it works GREAT to search by department, but not so great when just searching by title. It does not return any records for the title search.

    Any ideas?

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2000)

    Hi Kinaida,
    Mine works perfectly on title,,,whether you use a date or not, it returns records. Are you entering the entire title or just part of a string?

  8. #8
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2000)

    I'm using part of a string (sorry that I didn't make that more clear initially)...

    Actually, I think I got it (with a little help from the MS Newsgroups):

    SELECT qryOrdersAll.*
    FROM qryOrdersAll
    WHERE ((([qryOrdersAll].[DEPT])=[Forms]![frmTechServicesOrdering]![txtDeptTest]) And (([qryOrdersAll].[ORDER_DATE]) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate])) Or ((([qryOrdersAll].[ORDER_DATE]) Between [Forms]![frmTechServicesOrdering]![txtStartDate] And [Forms]![frmTechServicesOrdering]![txtEndDate]) And ([Forms]![frmTechServicesOrdering]![txtTitleSearch] Is Not Null) And (([qryOrdersAll].[TITLE]) Like "*" & [Forms]![frmTechServicesOrdering]![txtTitleSearch] & "*"));

    Thank you!!!
    Kindra

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2000)

    Well done...the *s allow the portion of the string to yield results. I'm glad it worked.

Posting Permissions

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