1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS ACCESS: Queries: i want blank or criteria to be ignored

Discussion in 'Business Applications' started by firestormer, Jan 17, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    Heres the problem. Im trying to create a complex search feature into my database.

    So i have created an unbound form with text boxes for all the different fields in a table and then created a query to search that table by the values entered in the above form. To do this i have set my criterias as [Forms]![Frm1]![Name] OR [Forms]![Frm1]![Brand] OR ect.

    The problem is i the user leaves one of the fields in the form blank then no matter what else is entered all the records are shown.

    Is there a way to tell the query to ignore blank criteria?

    I have already tried using this in the query

    IIf([Forms]![Frm1]![Name] Is Null,Null,[Forms]![Frm1]![Name])

    but to no avail.

    I realise i could use a parameter query but this would not provide the visual interface i want (Also there are about 12 fields of criteria)

    Any help will be apretiated.

    Thanks fsuk
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    firestormer, I have created many "Search Form" doing exactly as you describe. There are 3 things to remember.
    1. If you you use the "Like" function to Pattern Match then unless all the Form's fields are on one Criteria Row it will always find all records.
    2. If you want to have "combined" field searches, i.e. more than one field per criteria row to group the fields then any other columns rows have to have in their Criteria row
    Isnull([Forms]![Frm1]![Name] or [Forms]![Frm1]![Name] = ""
    Not iif and you have to allow for Text fields to be "" instead of Null.
    3. If you want to search for any one individual form field then all the column's criteria have to be on seperate rows with the "Is empty" check above on all the other rows for that column.
    If you need an example I can provide one, or I can create it for you.
     
  3. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    Thank your reply. I think i get the jist of what you say but as is often with things like this words arn't as good as actions. As im not entirley sure about what to do an example would be greatly apreciated.

    You do not need to rush as im sure you are busy i have plenty of things to to my database in the mean time.

    Thanks again, fsuk

    P.S. I just noticed your in the UK, me too. i am located just to the NE of Birmingham
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    firestormer, I am in Swansea, South Wales.
    Any chance of posting a zipped copy of your database with at least the table, search form and your query?
    It doesn't need any real data a couple of dummy records would do.
    Or private mail me your email and I will send you a copy.
     
  5. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    OK iv cut the DB down to just what u need so as to avoide confusion (There are a lot of DB objects and the empty DB itself is 14Mb)

    In the form FrmDeviceSearch the are two option buttons: And or Or. Ignore And.

    There are two queries. these are pervious attempts.
    Thanks for your time and help.
     
  6. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    OOPS forgot the attatchment
     

    Attached Files:

  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    firestormer, I have the form and Query working reasonably well, it really is a minefield of trial and error in getting all the various combinations to work.
    I have replaced your and/or buttons with an option group and added that to your first query so that part works as well, I have used "Wild Card" searching in the "And" (1) option line and exact matching in the "Or" (2) lines, but you could use Pattern matching in all lines or exact matching in all lines, it is up to you.
    The pattern matching means you can enter "Laser" and it will find laser printer and laser scanner etc.
    Have a play with and then let me know what you think.
    By the way I have played around with your Devices tables to give myself more testing options.
    There is one thing about "Pattern matching" that you should know, on the Columns (fields) that use it in the criteria it does not like Null values and won't find them, also if you leave all the Form's Search fields blank and select "And" it will return all records that have not got Null Values.
     

    Attached Files:

  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I forgot, I didn't do all the fields, I left some for you to do.:D
     
  9. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    Thanks for your time ill have a look at it and get back to you tommorow.

    Thanks
     
  10. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    Hmmm. I may have found out why all my atempts failed. It seems ive been entering Device ID into the Name field in the search form when testing. Despite that your design works brilliantly and thanks for sorting the option buttons out.

    Becasue of your help i will mention you in my database's About section.

    I only have one other question for you (i hope). Is there a way to make access look at the User Group of the person opening the database, say in a conditional macro.

    I know you can use CurrentUser() to get the user name but i want to divert the users to diffenent opening pages depending upon what permisons they have.

    The only way i can think to do it is to dissable warinings and tell the database to open both forms but only grant each user permisions to open one of them. Is the a neater method?

    Thanks OPB.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    firestormer, if you have user groups and therefore security , the CurrentUser works well.
    I use a Query and Form called "Selector" which uses a User Table as it's base, the user table holds which Main groups (not necessarily the same as the security groups) they belong to.
    This then opens whichever Main Menu is appropriate.
    The User table is maintained from an Admin Menu that is only available from the "Admin" Security groups main menu.
    This also gives you the ability to record who changes what, where and when in you forms.
    If you private mail me your email address I will send you a copy of one of my databases that uses that approach. It is too large to post on here and it has to have it's Workgroup with it for you to access it.
    I will have to try and get around to removing all the rest of the database just leaving the "Security and Admin" section for posting.
     
  12. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    I had originaly intended to have a table called Users but the obvious problem it is duplication of data so that if the admin want to add or change a user he has to do it twice once in Access and once in database. What i want to know is if it is possible to get the users user group in the same sort of way as you can get their user name.
     
  13. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    I thought you might like to see the changes i made.

    I added 3 text boxes into the search form, one to allow searching between 2 dates and 2 hidden text boxes which are equal to Location and User Assigned. This is because when i told the query to take the values from the drop combo boxes on the form it took the top value in the list when no value was entered. So i created two text boxes hidden and linked and added a repaint object event to the originals after update.

    Thanks for your help.

    fsuk

    (sry ill send attatchmet later ive got zip problems)
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    firestormer, it is possible to get the user group from security, it is just that in the early days I and the other programmer that I worked with went down the Users Table route and I have continued to use it.
    It is very useful for other things like Department data , Works ID number etc.
    So I consider it's benefits outway the small maintenance overhead.
    You can access User Data in Visual Basic, in the VB Editor Help (not Access help) type in the search box Users and User Group and it will show you the data on how to address the "User Collection", it will require the Library Reference to DAO to be set to use those VB functions.
    I may have a look at it myself, although I like the advantages the User Table gives you in other areas though.
     
  15. firestormer

    firestormer Thread Starter

    Joined:
    Sep 16, 2005
    Messages:
    1,254
    Heres the improved db.


    I had a look at ur profile. Hope u dont mind. i see u door air rifle shooting, me too. im currently shooting at 10m im not brilliant but im ok. I belong to the Lichfield Shooting Club, but im not yet shooting competion.
     

    Attached Files:

    • db1.zip
      File size:
      42.2 KB
      Views:
      79
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/535976

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice