Solved

query get invalid operation

Posted on 2007-03-23
20
376 Views
Last Modified: 2008-01-09
I'm getting an invalid operation on the query below, so, I must be missing something. I just want to list  only items (another field) where the webid is in the first table. WebID and OpptyDescription are both text fields with 255 characters. qryActiveTickets get data from tblTickets, but, does some filtering. If I replace qryActiveTickets with tblTickets in the query below, no problem.

SELECT tblGMPSAccts.WebID, qryActiveTickets.TicketNumber
FROM tblGMPSAccts LEFT JOIN qryActiveTickets ON tblGMPSAccts.WebID = qryActiveTickets.OpptyDescription;
0
Comment
Question by:avoorheis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 3
  • +1
20 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 18782150
I suspect a circular reference.  What is SQL of qryActiveTickets?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18782175
WebID = OpptyDescription

are these really the macthing fields?

0
 

Author Comment

by:avoorheis
ID: 18782207
since there is only webid in tblGMPSAccts, did a search and didn't find it.

Also, meant to say in orig post that I want to list every record from tblGMPSAccts and another field from another field from qryActiveTickets...I need all the wedid 's listed.

SELECT tblTickets.*
FROM tblTickets
WHERE (((tblTickets.TicketSource) Is Null) AND ((tblTickets.Area) Is Null) AND ((tblTickets.Category) Is Null)) OR (((tblTickets.TicketSource) Not Like "Implementation*" And (tblTickets.TicketSource)<>"Proactive - Monitoring" And (tblTickets.TicketSource)<>"Cobalt - CSI Survey" And (tblTickets.TicketSource)<>"Website Implementation" And (tblTickets.TicketSource)<>"New Prospector Contract" And (tblTickets.TicketSource)<>"Web Site Services (IFS Only)" And (tblTickets.TicketSource)<>"Website Implementation (IFS ONLY") AND ((tblTickets.Area) Is Null) AND ((tblTickets.Category) Is Null)) OR (((tblTickets.TicketSource) Is Null) AND ((tblTickets.Area) Not Like "Premium Services*" And (tblTickets.Area) Not Like "Proactive Services*" And (tblTickets.Area) Not Like "Implementation – New Project*" And (tblTickets.Area) Not Like "Procare*") AND ((tblTickets.Category) Is Null)) OR (((tblTickets.TicketSource) Not Like "Implementation*" And (tblTickets.TicketSource)<>"Proactive - Monitoring" And (tblTickets.TicketSource)<>"Cobalt - CSI Survey" And (tblTickets.TicketSource)<>"Website Implementation" And (tblTickets.TicketSource)<>"New Prospector Contract" And (tblTickets.TicketSource)<>"Web Site Services (IFS Only)" And (tblTickets.TicketSource)<>"Website Implementation (IFS ONLY") AND ((tblTickets.Area) Not Like "Premium Services*" And (tblTickets.Area) Not Like "Proactive Services*" And (tblTickets.Area) Not Like "Implementation – New Project*" And (tblTickets.Area) Not Like "Procare*") AND ((tblTickets.Category) Is Null)) OR (((tblTickets.TicketSource) Is Null) AND ((tblTickets.Area) Is Null) AND ((tblTickets.Category)<>"SEM Acct Management" And (tblTickets.Category)<>"Prospector - NEW Project" And (tblTickets.Category)<>"Prospector- NEW Project")) OR (((tblTickets.TicketSource) Not Like "Implementation*" And (tblTickets.TicketSource)<>"Proactive - Monitoring" And (tblTickets.TicketSource)<>"Cobalt - CSI Survey" And (tblTickets.TicketSource)<>"Website Implementation" And (tblTickets.TicketSource)<>"New Prospector Contract" And (tblTickets.TicketSource)<>"Web Site Services (IFS Only)" And (tblTickets.TicketSource)<>"Website Implementation (IFS ONLY") AND ((tblTickets.Area) Is Null) AND ((tblTickets.Category)<>"SEM Acct Management" And (tblTickets.Category)<>"Prospector - NEW Project" And (tblTickets.Category)<>"Prospector- NEW Project")) OR (((tblTickets.TicketSource) Is Null) AND ((tblTickets.Area) Not Like "Premium Services*" And (tblTickets.Area) Not Like "Proactive Services*" And (tblTickets.Area) Not Like "Implementation – New Project*" And (tblTickets.Area) Not Like "Procare*") AND ((tblTickets.Category)<>"SEM Acct Management" And (tblTickets.Category)<>"Prospector - NEW Project" And (tblTickets.Category)<>"Prospector- NEW Project")) OR (((tblTickets.TicketSource) Not Like "Implementation*" And (tblTickets.TicketSource)<>"Proactive - Monitoring" And (tblTickets.TicketSource)<>"Cobalt - CSI Survey" And (tblTickets.TicketSource)<>"Website Implementation" And (tblTickets.TicketSource)<>"New Prospector Contract" And (tblTickets.TicketSource)<>"Web Site Services (IFS Only)" And (tblTickets.TicketSource)<>"Website Implementation (IFS ONLY") AND ((tblTickets.Area) Not Like "Premium Services*" And (tblTickets.Area) Not Like "Proactive Services*" And (tblTickets.Area) Not Like "Implementation – New Project*" And (tblTickets.Area) Not Like "Procare*") AND ((tblTickets.Category)<>"SEM Acct Management" And (tblTickets.Category)<>"Prospector - NEW Project" And (tblTickets.Category)<>"Prospector- NEW Project"))
ORDER BY tblTickets.TicketNumber;
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:avoorheis
ID: 18782255
....WebID = OpptyDescription    are these really the macthing fields?
I believe so, if I use the table (tblTickets) that's the source of qryActiveTickets, which has OpptyDescription, it works.... I don't see how qryActiveTickets could be changing that field (tblActiveTickets.OpptyDescription).
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18782304
odd.  When I paste the code into notepad
Implementation – New Project
becomes
Implementation (non-printing character) New Project"
ascii 150
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18782351
WebID = OpptyDescription    so the two fields have the same data, OK, I just wanted to make sure you had the join correct.

yikes ... loooong sql ... you can shorten it up by aliasing the tblTicket (right click the table while in query design and select properties, enter the letter T in the alias.) and you can probably shorten it up and speed it up by using "Not In" for the repetition of  And (T.TicketSource)<>"Proactive - Monitoring" And (T.TicketSource)<>"Cobalt - CSI Survey" And (T.TicketSource)<>"Website Implementation" etc.

like so ...
t.TicketSource Not IN("Proactive - Monitoring", "Cobalt - CSI Survey", "Website Implementation", "New Prospector Contract", "Web Site Services (IFS Only)"

this will cut down the sql considerably because you can test a whole bunch all at once without having to repeat the other criteria over and over.


0
 
LVL 39

Expert Comment

by:stevbe
ID: 18782379
nice catch jerry, I copied to notepad but did not pick up on that, I was too busy trying to cut it down to size :-)
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18782390
Can't get around idea there is an illegal character in the criteria in the SQL.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18782410
(i hear you on the cutting down to size.)
0
 

Author Comment

by:avoorheis
ID: 18782426
let me state what I'm trying to do
I have a list of names (WebIDs). I want to make a list of all the names and shows which ones have data (Tickets) associated with them from another table (tblTickets). There are things I need to filter out from tblTickets, which is why I'm using qryActiveTickets.

I've made a simple query and it's not working as I thought. I expected it to list everything in tblGMPSAccts and any TicketNumber (if any) associated with a webid, but, it comes up with no records at all.

SELECT tblGMPSAccts.WebID, tblTickets.TicketNumber
FROM tblGMPSAccts LEFT JOIN tblTickets ON tblGMPSAccts.WebID = tblTickets.OpptyDescription
WHERE (((tblTickets.TicketNumber)="1-678978"));
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18782435
If you can open this query in design view, take a look at every instance where you have
"Implementation – New Project*"
and change it to:
"Implementation - New Project*"
Unless you actually have
"Implementation – New Project*" in your fields.
If so, you might have to use
"Implementation " & chr(150) & " New Project*"
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18782512
before we go any further:  Is this Access stand-alone, Access FE/BE, or something else?
0
 

Author Comment

by:avoorheis
ID: 18782544
FE/BE to sqlserver
0
 

Author Comment

by:avoorheis
ID: 18782565
tblGMPSAccts is local and tblTickets is on the sqlserver
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 18782598
(I will bow out now, as I have no specialized knowledge in nor access to a SQL server so I could try to work this out. :\)
0
 

Author Comment

by:avoorheis
ID: 18783281
well, I think I've solved the mystery, although I'm still a bit confused. If I do this:
SELECT tblGMPSWebIDs.WebID, qryActiveTickets.TicketNumber
FROM tblGMPSWebIDs LEFT JOIN qryActiveTickets ON tblGMPSWebIDs.WebID = qryActiveTickets.OpptyDescription;

I get every record from tblGMPSWebIDs and associated records from qryActiveTickets,

but, if I put a where in the sql, the results are limited to records where tblGMPSWebIDs.WebID has an associated record in qryActiveTickets. I must have run across this before and just forgot...
anyone know the logic behind this?

SELECT tblGMPSWebIDs.WebID, qryActiveTickets.TicketNumber
FROM tblGMPSWebIDs LEFT JOIN qryActiveTickets ON tblGMPSWebIDs.WebID = qryActiveTickets.OpptyDescription
WHERE (((qryActiveTickets.TicketNumber)="1-615879"));
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 18797162
Re"but, if I put a where in the sql, the results are limited to records where tblGMPSWebIDs.WebID has an associated record in qryActiveTickets."

Well, I think that would be because the WHERE clause is on the  join (Right) table, not the other (left) table.  I just emulated that ... and get the same result.

Can you change WHERE (((qryActiveTickets.TicketNumber)="1-615879"));  to
WHERE (((tblGMPSWebIDs.TicketNumber)="1-615879"));

mx
0
 
LVL 75
ID: 18797168
Also ... re the Invalid Operation ... just curious ... is that SQL an actual saved query in the database window .. or ... SQL pasted into the Record Source of a form?

mx
0
 

Author Comment

by:avoorheis
ID: 18816153
thanks dmx
It does appear that where clause on the right side is a problem. I'll have to look for another solution because there is only one field in the other table.

And it is a saved query.

I'm going to re-post this question, worded a little differently because I'd like to understand why this is happening and it seems like this would be something that would need to be done all the time.
0
 
LVL 75
ID: 18817349
You are welcome ... and thank you ...

mx
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question