Adding 'ALL' in rowsource of combobox.

[HiGu]

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:

[*]SELECT DISTINCTROW tblMIMAIN.A_JOBNO AS Jobno, tblMIMAIN.A_EQUIPDESCR AS Equipment, tblMIMAIN.A_LOCATION
 AS Location, tblMIMAIN.A_ID, IIf(IsNull(tblMIMAIN!A_PROJECTID)," ","**13Mplan**") AS 13Months FROM tblMIMAIN 
WHERE (((tblMIMAIN.A_LOCATION)>IIf(GetAsset()
="**ALL**","a","ZZ") Or (tblMIMAIN.A_LOCATION)=GetAsset())
 And ((tblMIMAIN.A_SYSTEM)="NEN")) ORDER BY Mid(tblMIMAIN!A_JOBNO,4,4)

[Rabbit]

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.

[HiGu]

I had tried using union all like this:

select distinct 'ALL' from tblMIMAIN 
union all select..(the above query)

However,this doesn't work.
A_Location stores string.Example is 'Haven'.

Public Function GetAsset() As String    'this function is used to return a choosen asset, the parameter returned is set in a form MiMainMenu
   If AssetChoice = "" Then
      GetAsset = "**ALL**"
      AssetChoice = "**ALL**"
   Else
      GetAsset = AssetChoice
   End If
End Function

Basically,getas set() 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.

[Rabbit]

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.

[HiGu]

When I ran the union it indicated error in the criteria expression.

[Rabbit]

Like I said, the union is wrong. You shouldn't use a union anyways.

[NeoPa]

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?

[Rabbit]

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.

[NeoPa]

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.

[Rabbit]

I believe that is the case, but perhaps I misunderstood.

[NeoPa]

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.

[HiGu]

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.

[HiGu]

The first post is the actual question.Assetc hoice 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.

[NeoPa]

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.