Solved

query get invalid operation

Posted on 2007-03-23
20
370 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
default combobox value 12 18
SQL group by query to return records with highest value 6 25
Access/Visual Basic Question 3 25
MS-Access not responding 5 22
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 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