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

What's wrong with this query?

P: 99
This query works in the query design window but doesn't display any data in the combobox in the form.
Expand|Select|Wrap|Line Numbers
  1. select distinct '<ALL>' as equipment from tblmimain UNION ALL SELECT DISTINct tblMIMAIN.A_EQUIPDESCR AS
  2.  Equipment FROM tblMIMAIN WHERE (((tblMIMAIN.A_LOCATION)>IIf
  3. (GetAsset()="**ALL**","a","ZZ") Or (tblMIMAIN.A_LOCATION)
  4. =GetAsset()) AND ((tblMIMAIN.A_SYSTEM)="NEN"));
Apr 16 '12 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,357
I don't see how it could work anywhere when select dtaistinct isn't correct.
Apr 16 '12 #2

P: 99
That happened when I typed here.I have used 'distinct'.
Note: tblMIMAIN.A_EQUIPDESCR stores sentences and the datatype is string. I have used the same query for another combobx where I have replaced A_EQUIPDESCR by A_JOBNO which is also string and stores a single word.Surprisingly, it has worked there.
Apr 17 '12 #3

Rabbit
Expert Mod 10K+
P: 12,357
You should check that the second part of the query runs correctly before trying a union.
Apr 17 '12 #4

P: 99
No even that works only in the query design window.However,check this
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW tblMIMAIN.A_EQUIPDESCR AS Equipment,
  2.  tblMIMAIN.A_EQUIPNO AS [No], tblMIMAIN.A_ID, tblMIMAIN.A_LOCATION 
  3. AS Location, IIf(IsNull(tblMIMAIN!A_PROJECTID)," ","**13Mplan**") AS
  4.  13Months
  5. FROM tblMIMAIN
  6. WHERE (((tblMIMAIN.A_LOCATION)>IIf(GetAsset()="**ALL**","a","ZZ") Or
  7.  (tblMIMAIN.A_LOCATION)=GetAsset()) AND ((tblMIMAIN.A_SYSTEM)="STW"))
  8. ORDER BY tblMIMAIN.A_EQUIPDESCR, tblMIMAIN.A_LOCATION;
  9.  
the above query works in the query design window and also on displays data on the form.The second part of the query in post#1 and the query in this post are different only in number of columns being selectec and the order by clause.
Apr 17 '12 #5

P: 99
Now, this is terrible of me..The reason for the second query not working was that I had column widths set as 0";1".So if I selected only 1 column nothing would be displayed.
Apr 17 '12 #6

Rabbit
Expert Mod 10K+
P: 12,357
lol, mistakes happen. Glad you resolved your problem.
Apr 17 '12 #7

Post your reply

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