Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Swindon, Wiltshire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Whats wrong with my search query? (Access 2000/2002)

    I have a main form and a sub form (not linked)

    In the main form, i have search fields for users to input what they want. Its based on the ItemSearch table.
    I then want to run a query that will match up all the items with fields they have entered something into, and if they leave it blank, to return all records.

    One of the search criteria is this

    IIf([ItemSearch].[Description] Is Null,[Item].[Description],([Item].[Description]) Like "*" & [ItemSearch].[Description] & "*")

    This is in the Description criteria of the Item table.
    I read it to say, if the field in the ItemSearch table is null, then match the description to the current description (ie all records), but if they type something in there, then it matches on that field.

    Get it?

    Anyway, it dont work. No Records are shown.

    Please help!

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

    Re: Whats wrong with my search query? (Access 2000/2002)

    You can't set criteria this way. Just use Like "*" & [ItemSearch].[Description] & "*" as criteria for the Description field in the Item table. Add the Description field from the ItemSearch table to the query grid, uncheck the View box, and enter Is Null in the second criteria line. This makes the query return all records if ItemSearch.Description is empty.
    It becomes more complicated if you several of these criteria; it might be easiest to modify the query in SQL view. If you have conditions on description and on OtherField, the WHERE clause of the SQL statement would look like

    WHERE (Item.Description Like "*" & ItemSearch.Description & "*" Or ItemSearch.Description Is Null) And (Item.OtherField Like "*" & ItemSearch.OtherField & "*" Or ItemSearch.OtherField Is Null)

    This can be extended to more fields. Of course, you must substitute the actual names.

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    Swindon, Wiltshire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Whats wrong with my search query? (Access 2000/2002)

    This works great. Thanks!
    I would have been here a while cause i dont think i would have EVER put null on the OR line.

Posting Permissions

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