What's wrong with this query?

[HiGu]

This query works in the query design window but doesn't display any data in the combobox in the form.

select distinct '<ALL>' as equipment from tblmimain UNION ALL SELECT DISTINct tblMIMAIN.A_EQUIPDESCR AS
 Equipment FROM tblMIMAIN WHERE (((tblMIMAIN.A_LOCATION)>IIf
(GetAsset()="**ALL**","a","ZZ") Or (tblMIMAIN.A_LOCATION)
=GetAsset()) AND ((tblMIMAIN.A_SYSTEM)="NEN"));

[Rabbit]

I don't see how it could work anywhere when select dtaistinct isn't correct.

[HiGu]

That happened when I typed here.I have used 'distinct'.
Note: tblMIMAIN.A_EQU IPDESCR 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.Surprising ly, it has worked there.

[Rabbit]

You should check that the second part of the query runs correctly before trying a union.

[HiGu]

No even that works only in the query design window.However, check this

SELECT DISTINCTROW tblMIMAIN.A_EQUIPDESCR AS Equipment,
 tblMIMAIN.A_EQUIPNO AS [No], tblMIMAIN.A_ID, tblMIMAIN.A_LOCATION 
AS Location, 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)="STW"))
ORDER BY tblMIMAIN.A_EQUIPDESCR, tblMIMAIN.A_LOCATION;

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.

[HiGu]

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.

[Rabbit]

lol, mistakes happen. Glad you resolved your problem.