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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

OBP

Joined
Mar 8, 2005
Messages
19,896
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.
 

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
 

OBP

Joined
Mar 8, 2005
Messages
19,896
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.
 

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.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
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.
 

Attachments

OBP

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

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
 

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.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
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.
 

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.
 

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)
 

OBP

Joined
Mar 8, 2005
Messages
19,896
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.
 

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.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top