Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Lightbulb Unanswered: Multiple Field Query

    hi

    Im trying to make a query on a single table where i will be querying multiple fields which are Status, Tech, Site and LogType. Basically I have a form created where i can enter the data to query the database, the problem is that if i do not enter the criteria into all of the fields then I receive back an empty database and if I do enter all the criteria into the database in only actually querys the database based on the 1st criteria.

    Below is the sql statement i am using:

    SELECT *
    FROM Customers

    WHERE ((([Customers]![Site])=[Forms]![Search]![Site]) OR (([Forms]![Search]![Site]) Is Null)

    WHERE ((([Customers]![Status])=[Forms]![Search]![Status])OR (([Forms]![Search]![Status]) Is Null)

    AND (([Customers]![LogType])=[Forms]![Search]![LogType])OR (([Forms]![Search]![LogType]) Is Null)

    AND (([Customers]![Tech])=[Forms]![Search]![Tech]) OR (([Forms]![Search]![Tech]) Is Null);

    please help if you can.

    Jim

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi Jim,

    That SQL statement looks odd to me:

    1) There are two "WHERE" clauses.

    2) There are two occurances of the "OR" keyword with no space before it.

    3) There are unbalanced parenthesis.

    Your logic seems correct, so I think your problems may be due to syntax.

    JT

  3. #3
    Join Date
    Sep 2003
    Posts
    4

    Lightbulb

    my mistake I separated the statements so it would be more easily viewable in this. but say i only wanted to use 2 fields im still getting the same problem results would be

    IFsite=aub and status=closed it brings back results where site = aub but shows both status as open and closed. the same goes for status=open

    if site=null and status = closed or open it brings back the correct respective results as in all sites either open or closed

    if i make site=aub and status=null the results show all sites instead of just aub and shows both open and closed status otherwords the full database.

    This is how the sql code looks exactly

    SELECT *
    FROM Customers
    WHERE (((Customers.Site)=[Forms]![Search]![Site])) OR (((IsNull([Forms]![Search]![Site])))) AND ((Customers.Status)=[Forms]![Search]![Status])) OR (((IsNull([Forms]![Search]![Status]))));

    Any ideas?
    Last edited by minihood; 09-26-03 at 11:04.

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    The parenthesis aren't quite right, try this

    SELECT *
    FROM Customers
    WHERE ((((Customers.Site)=[Forms]![Search]![Site]) OR (IsNull([Forms]![Search]![Site]))) AND (((Customers.Status)=[Forms]![Search]![Status]) OR (IsNull([Forms]![Search]![Status]))));

Posting Permissions

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