how do I get rid of "the expression you entered exceeds the 1,024-character limit for the query deisgn grid"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

annysjunkmail@tiscali.co.uk - 17 Apr 2008 16:09 GMT

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

Reply to this Message

KARL DEWEY - 17 Apr 2008 20:55 GMT

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.

Signature

KARL DEWEY
Build a little - Test a little

> Folks,
> I extended the SQL below (original SQL designed by a user from this
[quoted text clipped - 25 lines]
> Thanks
> Chris

Reply to this Message

annysjunkmail@tiscali.co.uk - 18 Apr 2008 09:12 GMT

> You could use more aliases but I see other problems.
> In your SELECT you have T.IndicatorID and then in the GROUP BY
[quoted text clipped - 38 lines]
>
> - Show quoted text -

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

Reply to this Message

annysjunkmail@tiscali.co.uk - 18 Apr 2008 11:48 GMT

> You could use more aliases but I see other problems.
> In your SELECT you have T.IndicatorID and then in the GROUP BY
[quoted text clipped - 38 lines]
>
> - Show quoted text -

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/microsoft.public.access.queries/browse_thread/t
hread/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

Reply to this Message

Chris O'C - 18 Apr 2008 17:50 GMT

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
[quoted text clipped - 25 lines]
>Thanks
>Chris

Reply to this Message

Chris O'C - 18 Apr 2008 18:09 GMT

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

>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:

Reply to this Message

annysjunkmail@tiscali.co.uk - 21 Apr 2008 10:36 GMT

On 18 Apr, 18:09, "Chris O'C via AccessMonster.com" <u29189@uwe>
wrote:

> Sorry, the editor's formatting messed up the SQL and separated Y.
> ActualIndicatorValue and T.IndicatorID as if they were multiple words. Let's
[quoted text clipped - 34 lines]
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200804/1

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

Reply to this Message