473,327 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

What's wrong with this query?

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
6 1532
Rabbit
12,516 Expert Mod 8TB
I don't see how it could work anywhere when select dtaistinct isn't correct.
Apr 16 '12 #2
HiGu
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
12,516 Expert Mod 8TB
You should check that the second part of the query runs correctly before trying a union.
Apr 17 '12 #4
HiGu
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
HiGu
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
12,516 Expert Mod 8TB
lol, mistakes happen. Glad you resolved your problem.
Apr 17 '12 #7

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

Similar topics

3
by: Keith | last post by:
I've inherited an Access file in which there is the following query: SELECT tblItem.InstanceNo, "PL" & Left(.,InStr(.,"-")-1) AS Parent FROM tblItem, tblItem AS tblItem_1 WHERE ((("PL" &...
8
by: Brian Basquille | last post by:
Hello all, Bit of a change of pace now. As opposed to the typical questions regarding my Air Hockey game, am also working on a Photo Album which uses an Access Database to store information...
4
by: darrel | last post by:
I'm still struggling to find out why I keep getting Invalid object name 'monkey'. errors. Below is my try/catch statement. Note the first query is trying to delete a record in the table...
3
by: brianbasquille | last post by:
Hello all, Strange little problem here... am just trying to insert some basic information into an Access Database using OleDB. I'm getting a "Syntax error in Insert Into statement" when it...
12
by: questions? | last post by:
I am testing a problem with linked list. I just do a lot of times: create a list, then free it. ############################################# # include <stdio.h> # include <stdlib.h> struct...
3
by: zykes | last post by:
I have this query that produces Syntax Error(missing operator) in query operation ForumTopics.TopicID = ForumReply.TopicID LEFT OUTER JOIN Members ON ForumTopics.WrittenBy =...
8
by: Shelly | last post by:
I get an error that the input string is not in the right format. Here is the result: Here is the partial code: Dim sqlConn As New SqlConnection(SqlDataSource1.ConnectionString) Dim query As...
1
by: Andy B | last post by:
I have a delete query that spans 2 tables: NewsArticles and NewsHeaders. I am trying to figure out what tableAdaptor to put the delete query under since it deals with 2 tables. Should it be in the...
6
by: karlarneg | last post by:
Hello. I am using Xampp on Windows Vista. PHP 5.2.6 MySql 5.0.51b This is my login code in PHP: if(isset($_POST)){ // Connect and select database
1
by: oimenoi | last post by:
I am keep having the "No overload for method 'GenerateSignature' takes 9 arguments" problem. Can you please tell me what is wrong with this class. This code is the sample from the Oauth.net . I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.