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

How to make a query dependent on another query (example attached)?

P: 78
I am working in Access 2003 and have approximately 10 fields that I want to be able to query on.
The catch is that if I only fill in 3 or 4 fields, only the data that matches only those three fields will be displayed (fields left blank will be ignored).
I know the "Is Null" will work in a different way, but I need only filled in fields to display.

I fouond on another thread somewhere this example db that shows how it's done, by running several queries one after another by making the beginning of one dependent on the other.
The problem is that I don't know how they did it! If I look at the query2 in the attached, and look at the object dependencies, it shows query1.

I can't figure out how they made that happen. Nothing I do will cause one query to run from another....

Thanks!!!
Attached Files
File Type: zip Access-EEQ-23967041SequentialQue.zip (23.2 KB, 106 views)
Jan 18 '10 #1

✓ answered by nico5038

The data tab for the company combo should have a row source like:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblName.[Company],  tblName.[Company]
  2. FROM tblName
  3. WHERE not(tblName.[Company]) is null
  4. UNION SELECT "*", "<All>" From tblName
  5. ORDER BY 2;
Nic;o)

Share this Question
Share on Google+
31 Replies


nico5038
Expert 2.5K+
P: 3,072
The general approach I us is to take a UNION query and thus add a "<All>" option. This option is "translated" by the second combo query column into a "*", thus enabling a LIKE statement in the query.
You'll need to force the user to make a selection, thus the OnCurrent event of the form will fill the combo's with the "<All>" value and before activating the query it's tested that all combo's have a value.

The query doesn't need any "sub queries", thus I've eliminated them.
Check out this working sample.

Nic;o)
Attached Files
File Type: zip Access-EEQ-23967041SequentialQue_NICO5038.zip (27.4 KB, 117 views)
Jan 18 '10 #2

P: 78
Hey Nico, I hope you're not getting tired of me thanking you, but thank you!

I copied everything I saw over to my actual db and I have 10 combo boxes (not that that should matter).

I go to click on the first cbo box and the folloing error comes up:

"The number of columns in the two selected tables or queries of a union query do not match".
Jan 18 '10 #3

nico5038
Expert 2.5K+
P: 3,072
Check my queries. They hold for the "original" table twice the same column and for the "<All>" an additional leading "*".
The column widths are set to 0 so the first (bound) column is "hidden" and that's the column to be used by the query.

Clear ?

Nic;o)
Jan 18 '10 #4

nico5038
Expert 2.5K+
P: 3,072
I forgot to mention that I never code this on tables, but instruct the user to use the right click pop-up form. This will allow every combination needed and as a service I sometimes "echo" the used Filter on the form above the datasheet. (I normally use a mainform with a datasheet subform for this.)
Check e.g. this sample: http://bytes.com/attachments/attachm...ction-2000.zip

Nic;o)
Jan 18 '10 #5

P: 78
Nico, I have it functioning, but say I choose <All> for 9 of the combo boxes and just want to search the name "Sam" in the "Name" combo box, it will query for Sam, along with every name field that was left blank! I'm assuming that has to do with the "Or Is Null". But I believe that if that is not in there, it wont bring back anything????

I'll try it without the "Or Is Null in the query criteria and see what happens....

Also, is there a way to make the <All> the default for each box, that way the user only has to change what they need, instead of going through and choosing <All> from each box?

Thanks
Jan 19 '10 #6

P: 78
Like I thought, the "Or Is Null" removed doesn't bring anything back, as I'm 100% sure you already knew!
Jan 19 '10 #7

nico5038
Expert 2.5K+
P: 3,072
I did set the three combo´s to <All> in my sample. Check the OnCurrent event.

You can drop the "Or Is Null" for every fully filled column, but else you might miss data when querying multiple columns...

Nic;o)
Jan 19 '10 #8

P: 78
Nico, sorry I left this hanging for so long....I had to put out other fires for a while!

In your example, the three boxes don't have <All> as a default, I have to drop down and choose it for each (it is the first option though).

Everything works great with my ten boxes now except that <All> has to be chosen and isn't a default. Any ideas?

Thanks
Jan 31 '10 #9

nico5038
Expert 2.5K+
P: 3,072
Oops, I see a typo in my solution, check this attachment.

Nic;o)
Attached Files
File Type: zip Access-EEQ-23967041SequentialQue_NICO5038_2.zip (54.2 KB, 104 views)
Jan 31 '10 #10

P: 78
OK, I see now. For some reason, 9 of my 10 boxes have a blank space as the 1st option when you drop them down (meaning the second option is "<All>, which is not the default for that reason)!

I've tried sorting in ascending order in the query to try and get rid of it but nothing works! Only one has no space and does have "<All>" as a default??

So close.......
Jan 31 '10 #11

nico5038
Expert 2.5K+
P: 3,072
Just add to each query a "Not Null" condition for the "bound" column or add a description when there's an ID, but no description.

Nic;o)
Jan 31 '10 #12

P: 78
Sorry to be such a pest, but could you give an example? I've put into each query a "Is Not Null" and nothing changed?
Jan 31 '10 #13

P: 78
More specifically, how do I add a "Not Null" condition to the "bound column" or where do I add a description?
Thanks
Jan 31 '10 #14

nico5038
Expert 2.5K+
P: 3,072
Just open the query that's the record source of the combo box.
It's the query with the union. For testing create a new empty query and paste just the "real" query text without the "<All>" union part and add the Not Null as criteria.
When there's no longer an empty line than replace that part in the original query.

Nic;o)
Feb 1 '10 #15

P: 78
I must have set this up wrong or am completely missing it.......

Could you do me a favor and take a look at this example?

These are the pieces of my db that deal with what we've been discussing and show the exact issue that I'm facing....

Thanks!
Attached Files
File Type: zip dbTEST.zip (27.0 KB, 68 views)
Feb 1 '10 #16

nico5038
Expert 2.5K+
P: 3,072
The data tab for the company combo should have a row source like:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblName.[Company],  tblName.[Company]
  2. FROM tblName
  3. WHERE not(tblName.[Company]) is null
  4. UNION SELECT "*", "<All>" From tblName
  5. ORDER BY 2;
Nic;o)
Feb 1 '10 #17

P: 78
Nico,

As usual, you're the best!

Thanks a million!
Feb 1 '10 #18

NeoPa
Expert Mod 15k+
P: 31,186
@nico5038
Shouldn't that be :
Expand|Select|Wrap|Line Numbers
  1. WHERE tblName.Company Is Not Null
I would expect it to NOT the company value first otherwise, before comapring it with Null.
Feb 1 '10 #19

nico5038
Expert 2.5K+
P: 3,072
The access transformation by the query editor of the "Not is Null" criteria is:
Expand|Select|Wrap|Line Numbers
  1.  ((Not (tblName.Company) Is Null))
In general the "("and ")"are only significant when multiple conditions are present. Now the Not is just testing for the denial of the True/False result of the comparison of the field Company with the value Null.

Nic;o)
Feb 1 '10 #20

NeoPa
Expert Mod 15k+
P: 31,186
I see.

That sort of makes sense I suppose, though mathematically Not is a unary operator with higher preference than the equality operators - hence should be processed first logically. I suppose I'm not too surprised it's not implemented that way within Access.
Feb 1 '10 #21

P: 78
Hey Nico,

I hope all is well! I've programmed the main system finally and I am now revisiting the "search criteria" part of it. I've done everything listed above and the issue I'm running into is that <ALL> is not the default value. Unfortunately these are all fields that have data entered in by users so you get the occasional space before the data or special charachter before the data, which both sort before the <ALL>. So I have all that junk sitting in the combos when you first open them.

Is there a way to get around this?

Also, is there a way to have <ALL> as the only choice available? That way if the user needs to search for something they must type it in freehand (no choice available other than <ALL>.

Thanks!!!
Steve
Mar 24 '10 #22

NeoPa
Expert Mod 15k+
P: 31,186
A couple of tips :

Always use Trim() for user input where you can. That way there will be no extraneous spaces at the start or end of the entry.

I'm not sure how you're building the ComboBox, but if you only want one option available, maybe a different type of control would be a better fit?
Mar 24 '10 #23

P: 78
Thanks NeoPa! That took care of the spaces. Do you know of a way to either ignore special characters or delete them or put them at the other end of the sort (after z)?
Thanks!
Mar 24 '10 #24

NeoPa
Expert Mod 15k+
P: 31,186
What are we talking about here?

I can see that spaces may be entered by accident and not noticed. I can't see a similar scenario for spoecial characters. Do you have some examples of what operators may enter that include some of these characters?

It may be that a form of filtering upon entry is required, or maybe an alternative way of sorting. We need to see what you're talking about to know though.
Mar 25 '10 #25

P: 78
An example would be a typo such as accidentally typing " ' " before a name:
'ron

You're right though, I need to make sure upon entry that a mistake like that is not allowed. Is there an easy way to do that? Letters only or numbers only?

Thank you!
Mar 25 '10 #26

NeoPa
Expert Mod 15k+
P: 31,186
You could try an Input Mask of all 'a's, but that would involve having as many as the maximum length you want to allow. It would also appear unusual for those who haven't seen it before.

Alternatively, you could put in some code to check the data in a BeforeUpdate event procedure.
Mar 25 '10 #27

P: 78
Thanks!

I ended up using the following code in the Validation Rule:
Expand|Select|Wrap|Line Numbers
  1. Is Null Or Not Like "*[!((a-z) or (0-9))]*"
This should keep all entries to letters and numbers!

Thanks again for your help!
Mar 27 '10 #28

NeoPa
Expert Mod 15k+
P: 31,186
Silly me! Yes that's a better option than handling the BeforeUpdate event.

Regular expressions (at least a significant subset of them) are supported in SQL so that's a good solution.
Mar 29 '10 #29

NeoPa
Expert Mod 15k+
P: 31,186
I was wrong again. Your expression will not work correctly as far as I can see. Check out Find Wildcard Characters in an Access Database for the available options. One thing which appears to be missing from the available syntax (and which you would need) is a character to say "Take 0 or more of the previous character definition". You could specify a numeric or alphabetic character, [0-9A-Za-z], but you would need to specify the exact number of characters you wanted to allow. Sorry. It was a good idea anyway.
Mar 29 '10 #30

P: 78
NeoPa,

Thank you for your feedback! The code I listed seems to work perfect. It will not allow users to use special characters. The only thing I found that it does let you use is the parentheses. For now, I can work with that!

Thanks!
Mar 30 '10 #31

NeoPa
Expert Mod 15k+
P: 31,186
You're right in that I missed the cleverness of what it's doing. It is a clever way of providing the missing functionality I mentioned. Still not quite correct though, as you've found. Try this instead. It's my attempt to reproduce the cleverness, but with a specification allowing only letters, digits or spaces in the text.
Expand|Select|Wrap|Line Numbers
  1. Not Like '*[!0-9A-Za-z ]*'
Mar 30 '10 #32

Post your reply

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