Solved

query get invalid operation

Posted on 2007-03-23
20
360 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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 - Access MVP) 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now