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!!!
The data tab for the company combo should have a row source like: -
SELECT tblName.[Company], tblName.[Company]
-
FROM tblName
-
WHERE not(tblName.[Company]) is null
-
UNION SELECT "*", "<All>" From tblName
-
ORDER BY 2;
Nic;o)
31 4158
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)
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".
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)
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)
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
Like I thought, the "Or Is Null" removed doesn't bring anything back, as I'm 100% sure you already knew!
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)
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
Oops, I see a typo in my solution, check this attachment.
Nic;o)
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.......
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)
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?
More specifically, how do I add a "Not Null" condition to the "bound column" or where do I add a description?
Thanks
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)
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!
The data tab for the company combo should have a row source like: -
SELECT tblName.[Company], tblName.[Company]
-
FROM tblName
-
WHERE not(tblName.[Company]) is null
-
UNION SELECT "*", "<All>" From tblName
-
ORDER BY 2;
Nic;o)
Nico,
As usual, you're the best!
Thanks a million!
NeoPa 32,556
Expert Mod 16PB @nico5038
Shouldn't that be : - WHERE tblName.Company Is Not Null
I would expect it to NOT the company value first otherwise, before comapring it with Null.
The access transformation by the query editor of the "Not is Null" criteria is: -
((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)
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.
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
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?
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!
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.
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!
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.
Thanks!
I ended up using the following code in the Validation Rule: - Is Null Or Not Like "*[!((a-z) or (0-9))]*"
This should keep all entries to letters and numbers!
Thanks again for your help!
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.
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.
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!
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. - Not Like '*[!0-9A-Za-z ]*'
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
by: Eddie Smit |
last post by:
field- field-
surname town
---- ----
john NY
john Vegas
john Boston
eddie Boston
eddie New Orleans
eddie NY
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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....
|
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
|
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...
|
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...
| |