Hello all,

I am puzzled (as usual)...

I have 2 queries in a call logging db, which return a list of events associated with calls be opened, and then the other returns events of calls being closed.

qryCallOpenings:
SELECT tblIncident.IncidentID, tblIncident.PC, tblEvent.EventType, tblEvent.DateTime, tblIncident.User, tblEvent.EnteredBy, tblEvent.Notes
FROM tblIncident INNER JOIN tblEvent ON tblIncident.IncidentID = tblEvent.IncidentID
WHERE (((tblEvent.EventType)=1));


qryCallClosings:
SELECT tblIncident.IncidentID, tblIncident.PC, tblEvent.EventType, tblEvent.DateTime, tblIncident.User, tblEvent.Notes
FROM tblIncident INNER JOIN tblEvent ON tblIncident.IncidentID = tblEvent.IncidentID
WHERE tblEvent.EventType=2;


Independantly, I can run both of these queries, and they are absolutely fine.

However, when used in this query:

SELECT qryCallOpenings.IncidentID, qryCallOpenings.PC, qryCallOpenings.User, qryCallOpenings.DateTime AS Reported, DateDiff("d",[Reported],Now()) AS TimeSinceReport, qryCallClosings.IncidentID AS ClosedID
FROM qryCallOpenings LEFT JOIN qryCallClosings ON qryCallOpenings.IncidentID = qryCallClosings.IncidentID
WHERE (((qryCallClosings.IncidentID) Is Null));


I get an error telling me that:
"ODBC Call Failed
The multi-part identifier "MS1.EventType" could not be bound. (#4104)"


VERY odd methinks? I can only assume I'm missing something REALLY obvious!

Sorry - can anyone help me? :confused:

Is that with a SQL Server backend?

Usually the "identifier could not be bound" indicate you have something that doesn't exist. This can be caused by typo or misspelling or maybe referring to something that's nto correctly qualified.

Yes, that is with SQL backend.

I'm using the query builder in Access, and I've checked as far as I can see, and everything looks OK name-wise.

I assume the two source queries must be OK, as they both run individually - but equally, all the names seem to check out on the 3rd query I mentioned? :(

I just noticed that the error identifies "MS1.EventID", which doesn't appear at all in the 3rd query, even though you have no problem running first two queries.

Try this and see what happens.

SELECT qrycallopenings.incidentid,
qrycallopenings.pc,
qrycallopenings.user,
qrycallopenings.datetime AS reported,
Datediff("d",[Reported],Now()) AS timesincereport,
qrycallclosings.incidentid AS closedid
FROM (SELECT tblincident.incidentid,
tblincident.pc,
tblevent.eventtype,
tblevent.datetime,
tblincident.user,
tblevent.enteredby,
tblevent.notes
FROM tblincident
INNER JOIN tblevent
ON tblincident.incidentid = tblevent.incidentid
WHERE (((tblevent.eventtype) = 1))) qrycallopenings
LEFT JOIN (SELECT tblincident.incidentid,
tblincident.pc,
tblevent.eventtype,
tblevent.datetime,
tblincident.user,
tblevent.notes
FROM tblincident
INNER JOIN tblevent
ON tblincident.incidentid = tblevent.incidentid
WHERE tblevent.eventtype = 2) qrycallclosings
ON qrycallopenings.incidentid = qrycallclosings.incidentid
WHERE (((qrycallclosings.incidentid) IS NULL));

I've just tried what you've posted, and am still getting the same error message, I'm afraid :(

What if you open the SSMS and try the same query there? Do you still get an error?

You aren't using COMPOSITE KEYS are you?

I've just noticed something - not sure if it will help at all.

In the qryCallClosings query, the criteria I've specified on the EventType field of 2, if I change this to 2 Or 13 Or 14, when I run that 3rd query, I get the same error, but 3 times.

So, I tried removing the criteria from the query altogether - and bosh, no error, 3rd query runs fine.

Unfortunately, I do need that criteria in there... just thought it might help troubleshoot the problem?


Thanks again for your help! :)

You aren't using COMPOSITE KEYS are you?

Not as far as I'm aware! As far as I understand it - this is where you have more than one primary key in a table, yes?

If so, then no, just 1 PK per table...

Not as far as I'm aware! As far as I understand it - this is where you have more than one primary key in a table, yes?
Not really. It is where you have 1 primary key but the key is made up of more than one field.

Well, to be honest, I didn't even really know you COULD do that... so no, none of those either!!!

By the way, you have a field (DateTime) which is an Access Reserved Word. That could also be causing you issues.

And, perhaps in your queries, you should Alias the names so that you don't have the same field names in each query when you go to use them in the third.