how do I get rid of "the expression you entered exceeds the1,024-character limit for the query deisg

A

annysjunkmail

Folks,
I extended the SQL below (original SQL designed by a user from this
newsgroup) but it always returning a the error msg "the expression you
entered exceeds the 1,024-character limit for the query deisgn grid".

Can someone suggest a workaround or how to get rid of error msg as I
haven't got the foggiest!

SELECT DianesBSPTourismqry.ApplicationRefNo, Y.ActualQtrDate,
Y.ActualindicatorName, Y.ActualIndicatorValue,
DianesBSPTourismqry.Programme,
DianesBSPTourismqry.OperationalProgrammeName,
DianesBSPTourismqry.PriorityName, DianesBSPTourismqry.Priority,
DianesBSPTourismqry.Measure, DianesBSPTourismqry.MeasureDescription,
DianesBSPTourismqry.DestDescription, tblRDPApplication.JobsCreated,
T.IndicatorID
FROM ((tblProjectActualIndicator AS Y INNER JOIN [SELECT
ApplicationRefNo, IndicatorID, Max(ActualQtrDate) as MaxQ
FROM tblProjectActualIndicator
GROUP BY ApplicationRefNo, IndicatorID]. AS T ON
(Y.ApplicationRefNo = T.ApplicationRefNo) AND (Y.IndicatorID =
T.IndicatorID) AND (Y.ActualQtrDate = T.MaxQ)) INNER JOIN
DianesBSPTourismqry ON Y.ApplicationRefNo =
DianesBSPTourismqry.ApplicationRefNo) INNER JOIN tblRDPApplication ON
Y.ApplicationRefNo = tblRDPApplication.ApplicationRefNo
WHERE (((Y.ActualindicatorName) Like "*jobs*"));



Thanks
Chris
 
K

KARL DEWEY

You could use more aliases but I see other problems.
In your SELECT you have T.IndicatorID and then in the GROUP BY
IndicatorID]. AS T -- You have a closing bracket without an opening one. It
also is followed by a period. And it makes an alias T of what appears to be
a field.
 
A

annysjunkmail

You could use more aliases but I see other problems.  
In your SELECT you have T.IndicatorID and then in the GROUP BY  
IndicatorID]. AS T -- You have a closing bracket without an opening one.  It
also is followed by a period.  And it makes an alias T of what appears to be
a field.

--
KARL DEWEY
Build a little - Test a little



[email protected] said:
Folks,
I extended the SQL below (original SQL designed by a user from this
newsgroup) but it always returning a the error msg "the expression you
entered exceeds the 1,024-character limit for the query deisgn grid".
Click to expand...
Can someone suggest a workaround or how to get rid of error msg as I
haven't got the foggiest!
Click to expand...
SELECT DianesBSPTourismqry.ApplicationRefNo, Y.ActualQtrDate,
Y.ActualindicatorName, Y.ActualIndicatorValue,
DianesBSPTourismqry.Programme,
DianesBSPTourismqry.OperationalProgrammeName,
DianesBSPTourismqry.PriorityName, DianesBSPTourismqry.Priority,
DianesBSPTourismqry.Measure, DianesBSPTourismqry.MeasureDescription,
DianesBSPTourismqry.DestDescription, tblRDPApplication.JobsCreated,
T.IndicatorID
FROM ((tblProjectActualIndicator AS Y INNER JOIN [SELECT
ApplicationRefNo, IndicatorID, Max(ActualQtrDate) as MaxQ
    FROM tblProjectActualIndicator
    GROUP BY  ApplicationRefNo, IndicatorID]. AS T ON
(Y.ApplicationRefNo = T.ApplicationRefNo) AND (Y.IndicatorID =
T.IndicatorID) AND (Y.ActualQtrDate = T.MaxQ)) INNER JOIN
DianesBSPTourismqry ON Y.ApplicationRefNo =
DianesBSPTourismqry.ApplicationRefNo) INNER JOIN tblRDPApplication ON
Y.ApplicationRefNo = tblRDPApplication.ApplicationRefNo
WHERE (((Y.ActualindicatorName) Like "*jobs*"));
Click to expand...
Thanks
Chris- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks for your reply Karl,
The initial query was written by a member from this Group (sorry can't
recall name).
This is the exact SQL...

SELECT Y.ApplicationRefNo, Y.ActualQtrDate, Y.ActualindicatorName,
Y.ActualIndicatorValue
FROM tblProjectActualIndicator AS Y INNER JOIN [SELECT
ApplicationRefNo, IndicatorID, Max(ActualQtrDate) as MaxQ
FROM tblProjectActualIndicator
GROUP BY ApplicationRefNo, IndicatorID]. AS T ON (Y.ActualQtrDate
= T.MaxQ) AND (Y.IndicatorID = T.IndicatorID) AND (Y.ApplicationRefNo
= T.ApplicationRefNo);

...you can see where I added in additional tables fields

You can also see the closing bracket and period were originally
written by the Author and not by me.
I am not sure what to do really. This is an excellent query for what
it does, it reads down a list of targets and select the most recent
only for an Application. I need to add more information for
mamangement reporting purposes. I would consider myself to be an
average user of Access but the complexity of this query (and aliases)
are beyond me.

I am hoping you may some thoughts as to a solution as I don't know how
to code it myself.

Thanks
Chris
 
A

annysjunkmail

You could use more aliases but I see other problems.  
In your SELECT you have T.IndicatorID and then in the GROUP BY  
IndicatorID]. AS T -- You have a closing bracket without an opening one.  It
also is followed by a period.  And it makes an alias T of what appears to be
a field.

--
KARL DEWEY
Build a little - Test a little



[email protected] said:
Folks,
I extended the SQL below (original SQL designed by a user from this
newsgroup) but it always returning a the error msg "the expression you
entered exceeds the 1,024-character limit for the query deisgn grid".
Click to expand...
Can someone suggest a workaround or how to get rid of error msg as I
haven't got the foggiest!
Click to expand...
SELECT DianesBSPTourismqry.ApplicationRefNo, Y.ActualQtrDate,
Y.ActualindicatorName, Y.ActualIndicatorValue,
DianesBSPTourismqry.Programme,
DianesBSPTourismqry.OperationalProgrammeName,
DianesBSPTourismqry.PriorityName, DianesBSPTourismqry.Priority,
DianesBSPTourismqry.Measure, DianesBSPTourismqry.MeasureDescription,
DianesBSPTourismqry.DestDescription, tblRDPApplication.JobsCreated,
T.IndicatorID
FROM ((tblProjectActualIndicator AS Y INNER JOIN [SELECT
ApplicationRefNo, IndicatorID, Max(ActualQtrDate) as MaxQ
    FROM tblProjectActualIndicator
    GROUP BY  ApplicationRefNo, IndicatorID]. AS T ON
(Y.ApplicationRefNo = T.ApplicationRefNo) AND (Y.IndicatorID =
T.IndicatorID) AND (Y.ActualQtrDate = T.MaxQ)) INNER JOIN
DianesBSPTourismqry ON Y.ApplicationRefNo =
DianesBSPTourismqry.ApplicationRefNo) INNER JOIN tblRDPApplication ON
Y.ApplicationRefNo = tblRDPApplication.ApplicationRefNo
WHERE (((Y.ActualindicatorName) Like "*jobs*"));
Click to expand...
Thanks
Chris- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi Karl,
Thannks for your reply.
Here is a link to the posting of how I received the original SQL
http://groups.google.co.uk/group/mi.../browse_thread/thread/923b63d6c9a23552?hl=en#
It seems Access is adding in the square bracket and period on its own?
The original query works well as intended but I need to add more
tables/fields to retrieve more infomation and that's when it starts to
go belly up with the 1024 problem.
I am an average Access user but the complexity of this query is beyond
me.
I am hoping you might have some thoughts for a solution.

Many Thanks
Chris
 
C

Chris O'C via AccessMonster.com

Jet made some syntax changes to your query because you have a subquery in the
from clause. If you make changes later, you can get weird errors. It's
usually fixed by replacing Jet's brackets with the original parentheses and
removing the period notation from the alias (T). Try this query:

SELECT Q.ApplicationRefNo, Y.ActualQtrDate, Y.ActualindicatorName, Y.
ActualIndicatorValue, Q.Programme, Q.OperationalProgrammeName, Q.PriorityName,
Q.Priority, Q.Measure, Q.MeasureDescription, Q.DestDescription,
tblRDPApplication.JobsCreated, T.IndicatorID
FROM ((tblProjectActualIndicator AS Y
INNER JOIN
(
SELECT ApplicationRefNo, IndicatorID, Max(ActualQtrDate) as MaxQ
FROM tblProjectActualIndicator
GROUP BY ApplicationRefNo, IndicatorID
) AS T
ON (Y.ApplicationRefNo = T.ApplicationRefNo) AND (Y.IndicatorID = T.
IndicatorID) AND (Y.ActualQtrDate = T.MaxQ))
INNER JOIN
DianesBSPTourismqry AS Q ON Y.ApplicationRefNo = Q.ApplicationRefNo)
INNER JOIN
tblRDPApplication ON Y.ApplicationRefNo = tblRDPApplication.
ApplicationRefNo
WHERE (Y.ActualindicatorName Like "*jobs*");


Chris
Microsoft MVP


Folks,
I extended the SQL below (original SQL designed by a user from this
newsgroup) but it always returning a the error msg "the expression you
entered exceeds the 1,024-character limit for the query deisgn grid".

Can someone suggest a workaround or how to get rid of error msg as I
haven't got the foggiest!

SELECT DianesBSPTourismqry.ApplicationRefNo, Y.ActualQtrDate,
Y.ActualindicatorName, Y.ActualIndicatorValue,
DianesBSPTourismqry.Programme,
DianesBSPTourismqry.OperationalProgrammeName,
DianesBSPTourismqry.PriorityName, DianesBSPTourismqry.Priority,
DianesBSPTourismqry.Measure, DianesBSPTourismqry.MeasureDescription,
DianesBSPTourismqry.DestDescription, tblRDPApplication.JobsCreated,
T.IndicatorID
FROM ((tblProjectActualIndicator AS Y INNER JOIN [SELECT
ApplicationRefNo, IndicatorID, Max(ActualQtrDate) as MaxQ
FROM tblProjectActualIndicator
GROUP BY ApplicationRefNo, IndicatorID]. AS T ON
(Y.ApplicationRefNo = T.ApplicationRefNo) AND (Y.IndicatorID =
T.IndicatorID) AND (Y.ActualQtrDate = T.MaxQ)) INNER JOIN
DianesBSPTourismqry ON Y.ApplicationRefNo =
DianesBSPTourismqry.ApplicationRefNo) INNER JOIN tblRDPApplication ON
Y.ApplicationRefNo = tblRDPApplication.ApplicationRefNo
WHERE (((Y.ActualindicatorName) Like "*jobs*"));

Thanks
Chris
Click to expand...
 
C

Chris O'C via AccessMonster.com

Sorry, the editor's formatting messed up the SQL and separated Y.
ActualIndicatorValue and T.IndicatorID as if they were multiple words. Let's
see if I can rearrange it better this time.

SELECT Q.ApplicationRefNo, Y.ActualQtrDate,
Y.ActualindicatorName, Y.ActualIndicatorValue,
Q.Programme, Q.OperationalProgrammeName,
Q.PriorityName, Q.Priority, Q.Measure, Q.MeasureDescription,
Q.DestDescription, tblRDPApplication.JobsCreated, T.IndicatorID
FROM ((tblProjectActualIndicator AS Y
INNER JOIN
(
SELECT ApplicationRefNo, IndicatorID,
Max(ActualQtrDate) as MaxQ
FROM tblProjectActualIndicator
GROUP BY ApplicationRefNo, IndicatorID
) AS T
ON (Y.ApplicationRefNo = T.ApplicationRefNo)
AND (Y.IndicatorID = T.IndicatorID)
AND (Y.ActualQtrDate = T.MaxQ))
INNER JOIN
DianesBSPTourismqry AS Q
ON Y.ApplicationRefNo = Q.ApplicationRefNo)
INNER JOIN
tblRDPApplication
ON Y.ApplicationRefNo = tblRDPApplication.ApplicationRefNo
WHERE (Y.ActualindicatorName Like "*jobs*");


Chris
Microsoft MVP
 
A

annysjunkmail

Sorry, the editor's formatting messed up the SQL and separated Y.
ActualIndicatorValue and T.IndicatorID as if they were multiple words.  Let's
see if I can rearrange it better this time.

SELECT Q.ApplicationRefNo, Y.ActualQtrDate,
Y.ActualindicatorName, Y.ActualIndicatorValue,
Q.Programme, Q.OperationalProgrammeName,
Q.PriorityName, Q.Priority, Q.Measure, Q.MeasureDescription,
Q.DestDescription, tblRDPApplication.JobsCreated, T.IndicatorID
FROM ((tblProjectActualIndicator AS Y
  INNER JOIN
  (
    SELECT ApplicationRefNo, IndicatorID,
      Max(ActualQtrDate) as MaxQ
    FROM tblProjectActualIndicator
    GROUP BY ApplicationRefNo, IndicatorID
  ) AS T
  ON (Y.ApplicationRefNo = T.ApplicationRefNo)
    AND (Y.IndicatorID = T.IndicatorID)
    AND (Y.ActualQtrDate = T.MaxQ))
  INNER JOIN
    DianesBSPTourismqry AS Q
  ON Y.ApplicationRefNo = Q.ApplicationRefNo)
  INNER JOIN
    tblRDPApplication
  ON Y.ApplicationRefNo = tblRDPApplication.ApplicationRefNo
WHERE (Y.ActualindicatorName Like "*jobs*");

Chris
Microsoft MVP
Click to expand...

Hi Chris,
I have just returned to work from the weekend hence the delay in this
post.
Just wanted to let you that that this query works brilliantly, no
errors and the correct number of recorrds returned.
Many, many thanks indeed for this SQL and for making life a little bit
easier for me...super job and well pleased!!

Chris
 
Top