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)
-
13 2457 Rabbit 12,516
Recognized Expert Moderator MVP
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.
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,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.
Rabbit 12,516
Recognized Expert Moderator MVP
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.
When I ran the union it indicated error in the criteria expression.
Rabbit 12,516
Recognized Expert Moderator MVP
Like I said, the union is wrong. You shouldn't use a union anyways.
NeoPa 32,557
Recognized Expert Moderator MVP
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 12,516
Recognized Expert Moderator MVP
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 32,557
Recognized Expert Moderator MVP
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 12,516
Recognized Expert Moderator MVP
I believe that is the case, but perhaps I misunderstood.
NeoPa 32,557
Recognized Expert Moderator MVP
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.
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.
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.
NeoPa 32,557
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: DraguVaso |
last post by:
Hi,
I have a ComboBox of +- 15.000 items in it, via a DataSource. On Top of the
Items-list, I want to add some values, based on a user choise (via a filter
on the DataSource). Does anybody know...
|
by: Richard Hallgren |
last post by:
Hi,
In Windows Forms the usual approach to add a combobox in a datagrid involves
adding a single combobox to the DataGrid.Controls, and then selectively
displaying it as needed when a...
|
by: Richard MSL |
last post by:
I am using a C# combobox control. There are too many items to add right from
the start, I want to add 20 or so, then when the user cursors down to the
last one and cursors down again, I will add...
|
by: Christopher Weaver |
last post by:
I'm familiar with the ComboBox.Items.Add method, but I can't make it work
with a ComboBox that is bound. Is it possible? Should I just add a row to
the table that it's bound to?
|
by: John Doe |
last post by:
Now i know how to manually add a combobox to a datagrid,
but how would i handle the recordset below?
ID | FirstName | LastName | Job
--------------------------------
1 |Joe | Smith |...
| |
by: Brian Tkatch |
last post by:
This is mostly for the fun of it. Just saw it in the FAQ and figured
i'd quickly give it a shot.
The syncfusion FAQ
<URL:http://www.syncfusion.com/FAQ/WindowsForms/FAQ_c44c.aspx#q480q>...
|
by: tara99 |
last post by:
Hi Guys
I have a list box which displays CCs from a table based on a query.
I have a text box for the user to input ID
I want to do the following
1. To allow the user select multiple item...
|
by: parvelli |
last post by:
adding a new combobox in a toolbar using vb.netv please help me
deepak
|
by: bobh |
last post by:
Hi All,
I've added an 'All' item to my combobox(below) and it does display as
the first item
If Me.cboLOB.Column(0) = "auto" Then
strSQL = "SELECT DISTINCT tblAUTO_MASTER.Section" & _
" FROM...
|
by: wassimdaccache |
last post by:
Hello everybody ..
I have a 2 combox and
I need after updating the value of combo1 the source of the combo2 will change for example if combo1 = 1
Then set criteria in combo2 for example...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |