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

Adding 'ALL' in rowsource of combobox.

P: 99
I have acombobox used to filter data on selection of any value.I want to add a value 'ALL' in my combobox for users to select all the values i.e. remove filtering.union all is not working somehow.Here's the query:
Expand|Select|Wrap|Line Numbers
  1. [*]SELECT DISTINCTROW tblMIMAIN.A_JOBNO AS Jobno, tblMIMAIN.A_EQUIPDESCR AS Equipment, tblMIMAIN.A_LOCATION
  2.  AS Location, tblMIMAIN.A_ID, IIf(IsNull(tblMIMAIN!A_PROJECTID)," ","**13Mplan**") AS 13Months FROM tblMIMAIN 
  3. WHERE (((tblMIMAIN.A_LOCATION)>IIf(GetAsset()
  4. ="**ALL**","a","ZZ") Or (tblMIMAIN.A_LOCATION)=GetAsset())
  5.  And ((tblMIMAIN.A_SYSTEM)="NEN")) ORDER BY Mid(tblMIMAIN!A_JOBNO,4,4)
  6.  
Apr 9 '12 #1
Share this Question
Share on Google+
13 Replies


Rabbit
Expert Mod 10K+
P: 12,357
Next time, format your query better. It's difficult to read when everything is on one line.

I don't see union all in your query. You must have posted the wrong query.

If that is the correct query, then you didn't use a union all. But then again, you don't have to. It's hard to say where it went wrong because you don't give us all the information we need. We need to know the code for GetAsset(). And we need to know what data is in location.

But I will say this, it would be easier if you used a LIKE predicate and had the All item in your combobox return a value of an asterisk.
Apr 9 '12 #2

P: 99
I had tried using union all like this:
Expand|Select|Wrap|Line Numbers
  1. select distinct 'ALL' from tblMIMAIN 
  2. union all select..(the above query)
However,this doesn't work.
A_Location stores string.Example is 'Haven'.
Expand|Select|Wrap|Line Numbers
  1. Public Function GetAsset() As String    'this function is used to return a choosen asset, the parameter returned is set in a form MiMainMenu
  2.    If AssetChoice = "" Then
  3.       GetAsset = "**ALL**"
  4.       AssetChoice = "**ALL**"
  5.    Else
  6.       GetAsset = AssetChoice
  7.    End If
  8. End Function
Basically,getasset() is used to fetch the A_LOCATION selected in the startup form.This helps display data in other forms relevant to A_location.

I want the user to be able to select 'ALL' from the combobox,so it is important that 'ALL' is present in the rowsource.Like predicate can be used only then, I guess.
Apr 10 '12 #3

Rabbit
Expert Mod 10K+
P: 12,357
Your union isn't going to work because it doesn't actually select any records. Your function and SQL look fine. I would debug it though to make sure it's returning the right value.
Apr 10 '12 #4

P: 99
When I ran the union it indicated error in the criteria expression.
Apr 10 '12 #5

Rabbit
Expert Mod 10K+
P: 12,357
Like I said, the union is wrong. You shouldn't use a union anyways.
Apr 10 '12 #6

NeoPa
Expert Mod 15k+
P: 31,419
Could you explain that Rabbit. I know this is hard to determine from what's posted here, but I would expect a table that held valid values, and a UNION query to allow a specific item (Whether 'All' or '*') to be added to the list from the table. Where are you coming from on this?
Apr 10 '12 #7

Rabbit
Expert Mod 10K+
P: 12,357
That's because they're not trying to add the value all to a list of values. They already have a list of values. They want to filter their records based on what is selected from the list. And if they select the all option, they want to return all records.
Apr 10 '12 #8

NeoPa
Expert Mod 15k+
P: 31,419
So you're saying the value 'All' (or equivalent) is already present within the list? Within the table?

That wasn't my interpretation, but certainly it makes sense if true.
Apr 11 '12 #9

Rabbit
Expert Mod 10K+
P: 12,357
I believe that is the case, but perhaps I misunderstood.
Apr 11 '12 #10

NeoPa
Expert Mod 15k+
P: 31,419
It's certainly a difficult set of logic to follow with what's available. My guess though, and I stress it's just a guess at this stage, from post #3 mainly, that the "**All**" string is being added into the mix in the function because it isn't available in the data (table).

@HiGu.
Perhaps you could clarify this issue for us. I appreciate Engish isn't your first language, so it's understandable that there is confusion, but it would help to have a clearer understanding of what you're attempting to achieve here. Is there any value in [All_Table] which reflects the {All} situation?

Is AssetChoice actually the ComboBox item you would like to populate? Your first post seems to indicate it might be, yet post #3 indicates you are using the results of this same ComboBox as part of its source. This would be unusual, to say the least.

Frankly, we're confused by the question.
Apr 11 '12 #11

P: 99
There is no such value in the data table which may help to select all the values.Hence,I want to include such a value in the combobox.I have done that before but the query in previous case was a simple select query with no criteria.
In the case of the function getasset(),such a value does exist and it is '**ALL**'.
The problem in the union query is that I should choose the same number of fields in both the queries I want to apply union on.It should work if I do that and also remove the order by clause.It works for the field A_JOBNO but for A_Equipdescr it only works when I run it in the query designer and shows only null values in the combobox on the form.
Apr 12 '12 #12

P: 99
The first post is the actual question.Assetchoice is not the combobox I want to populate.post#3 is a response to post#2.I posted the GetAsset() code as it is mentioned in the query in post#1.
Apr 12 '12 #13

NeoPa
Expert Mod 15k+
P: 31,419
It seems a UNION probably would be required then. The final solution is beyond me at this stage. The question is so convoluted that it's very hard to determine at this stage. You really need to start thinking about how to put your questions so they are clear HiGu. If every question needs a number of posts to clarify what you actually want then it gets very complicated to answer. Trying to work out what the single question should be over multiple posts is never a good way to work.

Certainly though, it seems clear that a UNION query, where each SELECT clause has the same number and type of fields, is the correct approach.
Apr 15 '12 #14

Post your reply

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