473,321 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

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

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, 176 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)

31 4158
nico5038
3,080 Expert 2GB
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, 206 views)
Jan 18 '10 #2
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
3,080 Expert 2GB
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
3,080 Expert 2GB
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
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
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
3,080 Expert 2GB
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
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
3,080 Expert 2GB
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, 131 views)
Jan 31 '10 #10
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
3,080 Expert 2GB
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
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
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
3,080 Expert 2GB
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
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, 94 views)
Feb 1 '10 #16
nico5038
3,080 Expert 2GB
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
Nico,

As usual, you're the best!

Thanks a million!
Feb 1 '10 #18
NeoPa
32,556 Expert Mod 16PB
@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
3,080 Expert 2GB
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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

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

Similar topics

22
by: Robert Brown | last post by:
suppose I have the following table: CREATE TABLE (int level, color varchar, length int, width int, height int) It has the following rows 1, "RED", 8, 10, 12 2, NULL, NULL, NULL, 20...
7
by: Dan V. | last post by:
Situation: I have to connect with my Windows 2000 server using VS.NET 2003 and C# and connect to a remote Linux server at another company's office and query their XML file. Their file may be...
11
by: Randell D. | last post by:
Folks, I have seven text boxes which will contain measurements - I would like the user to input their values in the order that I have listed the boxes. How can I therefore make an input...
6
by: Eddie Smit | last post by:
field- field- surname town ---- ---- john NY john Vegas john Boston eddie Boston eddie New Orleans eddie NY
5
by: Randy A. Bohannon | last post by:
I have two fields on a form: GPA and Points. GPA will be entered by the user. I would like Points to be filled automatically depending on the value of GPA. For example, the following code...
14
by: Crimsonwingz | last post by:
Need to calculate a sum based on a number of factors over a period of years. I can use formula ^x for some of it, but need totals to carry over in the sum and have only been able to do this thus...
4
by: Geoff | last post by:
I need to produce a report based on a query. Cost is a calculated field and its value is dependent on another field, in the query, called Session. There are 5 different Session codes each...
6
by: scottyman | last post by:
I can't make this script work properly. I've gone as far as I can with it and the rest is out of my ability. I can do some html editing but I'm lost in the Java world. The script at the bottom of...
17
by: stubbsie | last post by:
Hi, I have redesigned our official public government website in .net and it has taken me a few months to redo. I have been the sole designer of the website from its humble beginnning a few years...
4
by: Owen Jenkins | last post by:
Hi, No-one replied to this when I sent it last week. Any bites out there today?? ----- My application allows users to create a new back end for separate purposes. It does this by using Make...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.