By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,461 Members | 2,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,461 IT Pros & Developers. It's quick & easy.

FIltering query via Combo if there aren't records

P: 13
Hello,

I have a query that is being updated by a Combobox using the following code (introduced at the criteria of the query design) to do a filter in Field1

Debug Code:
Expand|Select|Wrap|Line Numbers
  1. Like IIf(IsNull([forms]![FormName]![ComboBox]);"*";[forms]![FormName]![Combobox])
Basically what this line does is:
1) If Combobox is empty, show all records
2) If Combobox is not empty, filter the results by the parameter introduced at the Combobox.

The problem is that if Field1 has no values, then the query simply do not show any results, even if the other fields have information.

Am I missing something? Thank you!
Sep 14 '17 #1
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,398
Indeed. Like * will match string values that have something or nothing. It won't match Nulls.

If you want to catch empty values too then try :
Expand|Select|Wrap|Line Numbers
  1. Is Null OR Like '*' & [Forms]![FormName]![Combobox] & '*'
In the QueryDef grid they would be on separate lines.
Sep 15 '17 #2

P: 13
@NeoPa

Thank you for your time and help.
With that line of code, what happens is:
1) If combobox1 is empty, all results are shown
2) If Combobox1 has, for example, "Option 1" selected, then all "Option 1" results are shown plus Null Values.

In order to obtain the following results:
1) If combobox1 is empty, all results are shown (same as before)
2) If Combobox1 has, for example, "Option 1" selected, then only "Option 1" results are shown.

I just added the combobox1 in the Null criteria.

Expand|Select|Wrap|Line Numbers
  1. Like "*" & [forms]![Formname]![Combobox1] & "*" OR [forms]![Formname]![Combobox1] Is Null
Now the problem is that my other comboboxes (that use also the query to requery themselves), just update with combobox1 (for example, combobox2 is now unable to requery combobox3 - it was working before).
Cannot understand why.
Sep 15 '17 #3

NeoPa
Expert Mod 15k+
P: 31,398
Antonio.

It's hard to give a workable answer with the question changing as we go along. Where do other ComboBox values fit into this?

Adding your extra Is Null test is unlikely to have the effect you want. It's hard to say anything definitively though, until I have a clearly defined question to work with.
Sep 15 '17 #4

P: 13
Hello NeoPa,

Sorry for the confusion I have caused with the oversimplification done at the first post.
In order to explain better, I have created a small debug example that can be downloaded from this URL:
https://www.dropbox.com/s/czul6js36z...ple.accdb?dl=0

In this database there is Query1(directly linked to Table1) and Form1 with 3 Comboboxes. The Form1 and Query1 are connected by the following conditions:
1) Each combobox of Form1 has a field of Query1 as a RowSource.
2) In Query1 each field is filtered by the comboboxes at Form1.

Well, this might seem a little bit confusing but if the form is runned, we can understand what is happening behind.

Now, there is 3 rows of information introduced at Table1 but,
if we run the query, or Form1, we can see that there are only two rows being the reason for this the one you stated:
"Like * will match string values that have something or nothing. It won't match Nulls."

If the criteria "Is Null" is added to Field number3 of Query1 (the field in which there is an empty entry), it is possible to see that Combobox filters stop working.

So, the goal is to have the following conditions verified:
1) When form is loaded, or clean button is pressed, all 3 combo should show the three possibilities.
2) After selection of one or more combo, the results of the other combos should be automatic filtered as verified at the original DebugExample.accdb
Sep 15 '17 #5

NeoPa
Expert Mod 15k+
P: 31,398
Hi Antonio.

As you've done a good job trying to explain your situation I will download your database and look at it for you. However, you should understand that most experts generally won't. The reasons are :
  1. It takes a lot more time and effort to download and run and look into something than it does to read the pertinent details in text.
  2. Many Experts have only one installation of Access on their systems. Questions come from many people using many different Access versions.
  3. It is the responsibility of the person asking the question to ask it with a clear explanation and all relevant details. This should not be left to the Experts to work out for themselves. That is generally not what they expect to have to do when they offer their expertise.
  4. Many won't touch it as they work at places where such downloads are not allowed.
  5. As a general rule Experts are looking to read through a bit of text, understand the problem, suggest a solution if they know one. The best questions are the ones with very little back and forth with extra questions and explanations.

As I say, I will download it on this occasion as I can see you're probably not a native English speaker and you've obviously put a lot of effort in to try to explain it as well as you can. I'm happy with that. It will involve further delay though, of course.
Sep 16 '17 #6

P: 13
Hello NeoPa,

I have understood your message and I really appreciate your explanations and justifications. As I am new to the forum community, I have never thought about the points you mentioned. Yet, they make all the sense and from now one I will follow them.

As I understand the time and security concerns, and in order to correct my bad approach, if you want I can open a new thread with a better explanation and with less content.
Nevertheless, I really acknowledge the effort and time you and other experts put in this forums. It is unquestionably awesome your dedication to help others! Thank you!

By the way, you are right, I am not a native English speaker. I am from Portugal and I hope my English is good enough to present my future thoughts.
Sep 17 '17 #7

NeoPa
Expert Mod 15k+
P: 31,398
Hi Antonio.

The points are important but you shouldn't be worrying about what was done before. Your attitude seems perfect and I'll happily look at your database tomorrow.

Also, your English is a lot better than my Portuguese, so don't worry too much on that score either. What you say all makes good sense.
Sep 17 '17 #8

NeoPa
Expert Mod 15k+
P: 31,398
I've looked at the database but I'm still not clear on what you're trying to achieve. When you select one record from Combo1 should that record be the only one that shows in the other two ComboBoxes, or should the other two then show all records except that one?
Sep 17 '17 #9

P: 13
Hello NeoPa,

Thank you for the look at my debug example.

Goal: have Combos that interact with each other.
How it is made: Combos use query as their RowSource. Each query field is filtered via the following code:
Expand|Select|Wrap|Line Numbers
  1. Like '*' & [Forms]![FormName]![Combo] & '*'
Results: If, for example, at combo1 "F1-1" is chosen, then Combo2 and Combo3 should be required to "F2-1" and "F3-1", respectively.
Problem: Because of the code presented, ID number 2 is not shown at the query (which reads directly through Table1)

https://www.dropbox.com/s/k2jkuygfq1...Debug.png?dl=0
Sep 18 '17 #10

NeoPa
Expert Mod 15k+
P: 31,398
Antonio:
If, for example, at combo1 "F1-1" is chosen, then Combo2 and Combo3 should be required to "F2-1" and "F3-1", respectively.
Surely you mean Combo2 would show "F2-2" & "F2-3" and Combo3 would show "F3-2" & F3-3"?
Sep 18 '17 #11

P: 13
Hello Neo :)

Well, Combo2 should require to F2-1 (same line as F1-1. The second number indicates the line and the first the column. Is was put like this to be easier to debug).
Nevertheless, I don't want to consume more your time.
I will disable this function at the database and implement in other way.
Thank you very much for your time and patience.

Regards
Sep 19 '17 #12

NeoPa
Expert Mod 15k+
P: 31,398
Antonio.

This isn't a complicated issue. I simply need a clear indication of the requirement to give you something that will work for you.

Obviously, it's your choice, but I think we can find a fairly simple solution if you can be clear on the requirement. I'll leave it with you.
Sep 19 '17 #13

Post your reply

Sign in to post your reply or Sign up for a free account.