query get invalid operation

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;
avoorheisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jerryb30Commented:
I suspect a circular reference.  What is SQL of qryActiveTickets?
0
stevbeCommented:
WebID = OpptyDescription

are these really the macthing fields?

0
avoorheisAuthor Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

avoorheisAuthor Commented:
....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
jerryb30Commented:
odd.  When I paste the code into notepad
Implementation – New Project
becomes
Implementation (non-printing character) New Project"
ascii 150
0
stevbeCommented:
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
stevbeCommented:
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
jerryb30Commented:
Can't get around idea there is an illegal character in the criteria in the SQL.
0
jerryb30Commented:
(i hear you on the cutting down to size.)
0
avoorheisAuthor Commented:
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
jerryb30Commented:
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
jerryb30Commented:
before we go any further:  Is this Access stand-alone, Access FE/BE, or something else?
0
avoorheisAuthor Commented:
FE/BE to sqlserver
0
avoorheisAuthor Commented:
tblGMPSAccts is local and tblTickets is on the sqlserver
0
jerryb30Commented:
(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
avoorheisAuthor Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
avoorheisAuthor Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You are welcome ... and thank you ...

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.