Query syntax, error in FROM clause

[Josh]

Hello folks!
Can someone help me with the following SELECT statement? I
apologize that this is tedious. I get "error in the FROM
clause" when trying to run this. I've looked for a couple
of days and I don't see the problem.


SELECT t.SlotID, t.DID AS TDID, t.ApptCodeID AS
TApptCodeID, t.ApptStart AS TApptStart, t.DayOfWk, n.*, IIf
(IsNull(a.ApptStart),t.ApptStart], a.ApptStart) AS Sort
FROM ApptTemplate AS t
LEFT JOIN [SELECT a.AppointmentID, a.ApptTypeID,
a.ApptCodeID, a.RecordID AS PersonID, a.DID, a.ApptDate,
a.ApptStart AS OVApptStart, a.TmStamp, a.DateUpdated,
a.DateCreated, a.SlotID AS ASlotID, a.ApptComment,
ovd.ApptOVDetailID, ovd.AppointmentID AS DApptID,
ovd.ApptReasonCode, ovd.TmStamp AS DTmStamp, p.LastName,
p.FirstName, p.NickName, p.NPInfo FROM Person AS p INNER
JOIN (Appointment AS a LEFT JOIN ApptOVDetail AS ovd ON
a.AppointmentID = ovd.AppointmentID) ON p.PersonID =
a.RecordID WHERE (((a.ApptTypeID)=1 Or (a.ApptTypeID)=2))
AND (a.ApptDate)=[forms]![ApptFrm2]![ApptDateSelect]]. AS
n ON t.SlotID = n.ASlotID
WHERE (((t.DID)=[forms]![apptFrm2]![cboDID]))
ORDER BY IIf(IsNull([OVApptStart]),[TApptStart],
[OVApptStart]);

Thanks so much!!
Josh

 

[Van T. Dinh]

You have an unmatched closing square bracket on line 3 of
the SQL String.

Also, the Query Grid puts the SubQuery in square brackets
and this seems to creates problems sometimes.

Try the following *untested* SQL String using the SQLView
of the Query GUI:

SELECT t.SlotID, t.DID AS TDID,
t.ApptCodeID AS TApptCodeID,
t.ApptStart AS TApptStart, t.DayOfWk, n.*,
IIf(IsNull(a.ApptStart),t.ApptStart, a.ApptStart) AS [Sort]
FROM ApptTemplate AS t
LEFT JOIN
( SELECT a.AppointmentID, a.ApptTypeID,
a.ApptCodeID, a.RecordID AS PersonID,
a.DID, a.ApptDate,
a.ApptStart AS OVApptStart, a.TmStamp, a.DateUpdated,
a.DateCreated, a.SlotID AS ASlotID, a.ApptComment,
ovd.ApptOVDetailID, ovd.AppointmentID AS DApptID,
ovd.ApptReasonCode, ovd.TmStamp AS DTmStamp,
p.LastName, p.FirstName, p.NickName, p.NPInfo
FROM Person AS p
INNER JOIN
( Appointment AS a
LEFT JOIN ApptOVDetail AS ovd
ON a.AppointmentID = ovd.AppointmentID )
ON p.PersonID = a.RecordID
WHERE (a.ApptTypeID=1 Or a.ApptTypeID=2)
AND (a.ApptDate=[forms]![ApptFrm2]![ApptDateSelect])
) AS n
ON t.SlotID = n.ASlotID
WHERE (t.DID=[forms]![apptFrm2]![cboDID])
ORDER BY IIf(IsNull([OVApptStart]),[TApptStart],
[OVApptStart]);

HTH
Van T. Dinh
MVP (Access)

 

[Jim Shaw]

Josh;
It is difficult to go through this without having the database to play with.
You need to break the task down into smaller units for evaluation.
First, there are several "FROM" clauses. Which one is in error?
Is there a way you can strip down the code so that it can be tested in
smaller code segments to see which segment is giving the error?
Can you cut and paste this code into a new, empty query to see what the
query compiler thinks of it?
Just a few thoughts.
-Jim Shaw

 

[Josh]

Van:
Thanks! Removing the square brackets did the trick. But
everytime I close the form and reopen in design view, the
form query opens in the Query grid and puts those brackets
back! How do I tell it to open only in SQLview?
Josh

-----Original Message-----
You have an unmatched closing square bracket on line 3 of
the SQL String.

Also, the Query Grid puts the SubQuery in square brackets
and this seems to creates problems sometimes.

Try the following *untested* SQL String using the SQLView
of the Query GUI:

SELECT t.SlotID, t.DID AS TDID,
t.ApptCodeID AS TApptCodeID,
t.ApptStart AS TApptStart, t.DayOfWk, n.*,
IIf(IsNull(a.ApptStart),t.ApptStart, a.ApptStart) AS [Sort]
FROM ApptTemplate AS t
LEFT JOIN
( SELECT a.AppointmentID, a.ApptTypeID,
a.ApptCodeID, a.RecordID AS PersonID,
a.DID, a.ApptDate,
a.ApptStart AS OVApptStart, a.TmStamp, a.DateUpdated,
a.DateCreated, a.SlotID AS ASlotID, a.ApptComment,
ovd.ApptOVDetailID, ovd.AppointmentID AS DApptID,
ovd.ApptReasonCode, ovd.TmStamp AS DTmStamp,
p.LastName, p.FirstName, p.NickName, p.NPInfo
FROM Person AS p
INNER JOIN
( Appointment AS a
LEFT JOIN ApptOVDetail AS ovd
ON a.AppointmentID = ovd.AppointmentID )
ON p.PersonID = a.RecordID
WHERE (a.ApptTypeID=1 Or a.ApptTypeID=2)
AND (a.ApptDate=[forms]![ApptFrm2]![ApptDateSelect])
) AS n
ON t.SlotID = n.ASlotID
WHERE (t.DID=[forms]![apptFrm2]![cboDID])
ORDER BY IIf(IsNull([OVApptStart]),[TApptStart],
[OVApptStart]);

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Hello folks!
Can someone help me with the following SELECT statement? I
apologize that this is tedious. I get "error in the FROM
clause" when trying to run this. I've looked for a couple
of days and I don't see the problem.


SELECT t.SlotID, t.DID AS TDID, t.ApptCodeID AS
TApptCodeID, t.ApptStart AS TApptStart, t.DayOfWk, n.*, IIf
(IsNull(a.ApptStart),t.ApptStart], a.ApptStart) AS Sort
FROM ApptTemplate AS t
LEFT JOIN [SELECT a.AppointmentID, a.ApptTypeID,
a.ApptCodeID, a.RecordID AS PersonID, a.DID, a.ApptDate,
a.ApptStart AS OVApptStart, a.TmStamp, a.DateUpdated,
a.DateCreated, a.SlotID AS ASlotID, a.ApptComment,
ovd.ApptOVDetailID, ovd.AppointmentID AS DApptID,
ovd.ApptReasonCode, ovd.TmStamp AS DTmStamp, p.LastName,
p.FirstName, p.NickName, p.NPInfo FROM Person AS p INNER
JOIN (Appointment AS a LEFT JOIN ApptOVDetail AS ovd ON
a.AppointmentID = ovd.AppointmentID) ON p.PersonID =
a.RecordID WHERE (((a.ApptTypeID)=1 Or (a.ApptTypeID) =2))
AND (a.ApptDate)=[forms]![ApptFrm2]![ApptDateSelect]]. AS
n ON t.SlotID = n.ASlotID
WHERE (((t.DID)=[forms]![apptFrm2]![cboDID]))
ORDER BY IIf(IsNull([OVApptStart]),[TApptStart],
[OVApptStart]);

Thanks so much!!
Josh
.

.

 

[Van T. Dinh]

Sorry, I don't know. Once it works, I try not to open the Query in
DesignView again.

However, the square brackets + the period after for the SubQuery should work
(most of the times, anyway).