Error when adding one more criteria

  • Thread starter Thread starter Steve D
  • Start date Start date
S

Steve D

I am having all kinds of issues today. I have a query that works fine until I
add one more criteria and then I get a message that the "expression is typed
incorrectly or too complex..." If I take out 'AND
((tblPL.[Category])="Actual")' the query runs fine. Any thoughts?

SELECT qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent],
Format([Amount]*[Chargeback_Decimal],"Currency") AS [Chargeback Amount],
tblPL.Category
FROM qryAssociates_Paid_Chargeback_Pct LEFT JOIN tblPL ON
qryAssociates_Paid_Chargeback_Pct.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback") AND ((tblPL.[Cost Center])="228065")
AND ((tblPL.[Category])="Actual"))
GROUP BY qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent], tblPL.Amount,
qryAssociates_Paid_Chargeback_Pct.Chargeback_Decimal, tblPL.Category;
 
What happens when you remove one of the other AND statements instead?
i.e. remove 'AND ((tblPL.[Cost Center])="228065")' and leave
tblPL.Category part in the statement?
 
Try this --
WHERE tblPL.Account)="Chargeback" AND tblPL.[Cost Center]="228065" AND
tblPL.[Category]="Actual"
 
I still get the same message when I use this code.
--
Thank You,
Steve


KARL DEWEY said:
Try this --
WHERE tblPL.Account)="Chargeback" AND tblPL.[Cost Center]="228065" AND
tblPL.[Category]="Actual"

--
KARL DEWEY
Build a little - Test a little


Steve D said:
I am having all kinds of issues today. I have a query that works fine until I
add one more criteria and then I get a message that the "expression is typed
incorrectly or too complex..." If I take out 'AND
((tblPL.[Category])="Actual")' the query runs fine. Any thoughts?

SELECT qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent],
Format([Amount]*[Chargeback_Decimal],"Currency") AS [Chargeback Amount],
tblPL.Category
FROM qryAssociates_Paid_Chargeback_Pct LEFT JOIN tblPL ON
qryAssociates_Paid_Chargeback_Pct.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback") AND ((tblPL.[Cost Center])="228065")
AND ((tblPL.[Category])="Actual"))
GROUP BY qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent], tblPL.Amount,
qryAssociates_Paid_Chargeback_Pct.Chargeback_Decimal, tblPL.Category;
Click to expand...
Click to expand...
 
I still have the same problem, so it appears it is specific to the one
criteria ((tblPL.[Category])="Actual"). If I remove the criteria but leave
the field it works fine and shows me the value "Actual" in the field along
with other categories, if there are any. So it is clearly tied to the
criteria.
--
Thank You,
Steve


Rich said:
What happens when you remove one of the other AND statements instead?
i.e. remove 'AND ((tblPL.[Cost Center])="228065")' and leave
tblPL.Category part in the statement?

Steve said:
I am having all kinds of issues today. I have a query that works fine until I
add one more criteria and then I get a message that the "expression is typed
incorrectly or too complex..." If I take out 'AND
((tblPL.[Category])="Actual")' the query runs fine. Any thoughts?

SELECT qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent],
Format([Amount]*[Chargeback_Decimal],"Currency") AS [Chargeback Amount],
tblPL.Category
FROM qryAssociates_Paid_Chargeback_Pct LEFT JOIN tblPL ON
qryAssociates_Paid_Chargeback_Pct.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback") AND ((tblPL.[Cost Center])="228065")
AND ((tblPL.[Category])="Actual"))
GROUP BY qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent], tblPL.Amount,
qryAssociates_Paid_Chargeback_Pct.Chargeback_Decimal, tblPL.Category;
Click to expand...
Click to expand...
 
Is [Category] lookup field by chance?
What if you use a different value in the criteria?

--
KARL DEWEY
Build a little - Test a little


Steve D said:
I still have the same problem, so it appears it is specific to the one
criteria ((tblPL.[Category])="Actual"). If I remove the criteria but leave
the field it works fine and shows me the value "Actual" in the field along
with other categories, if there are any. So it is clearly tied to the
criteria.
--
Thank You,
Steve


Rich said:
What happens when you remove one of the other AND statements instead?
i.e. remove 'AND ((tblPL.[Cost Center])="228065")' and leave
tblPL.Category part in the statement?

Steve said:
I am having all kinds of issues today. I have a query that works fine until I
add one more criteria and then I get a message that the "expression is typed
incorrectly or too complex..." If I take out 'AND
((tblPL.[Category])="Actual")' the query runs fine. Any thoughts?

SELECT qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent],
Format([Amount]*[Chargeback_Decimal],"Currency") AS [Chargeback Amount],
tblPL.Category
FROM qryAssociates_Paid_Chargeback_Pct LEFT JOIN tblPL ON
qryAssociates_Paid_Chargeback_Pct.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback") AND ((tblPL.[Cost Center])="228065")
AND ((tblPL.[Category])="Actual"))
GROUP BY qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent], tblPL.Amount,
qryAssociates_Paid_Chargeback_Pct.Chargeback_Decimal, tblPL.Category;
Click to expand...
Click to expand...
Click to expand...
 
Is tblPL.Category a text field?
Is tblPL.Category by any chance a lookup field and it gets its value
from another table?


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Yes, [Category] is a lookup field. I am guessing that is a bad thing.
--
Thank You,
Steve


KARL DEWEY said:
Is [Category] lookup field by chance?
What if you use a different value in the criteria?

--
KARL DEWEY
Build a little - Test a little


Steve D said:
I still have the same problem, so it appears it is specific to the one
criteria ((tblPL.[Category])="Actual"). If I remove the criteria but leave
the field it works fine and shows me the value "Actual" in the field along
with other categories, if there are any. So it is clearly tied to the
criteria.
--
Thank You,
Steve


Rich said:
What happens when you remove one of the other AND statements instead?
i.e. remove 'AND ((tblPL.[Cost Center])="228065")' and leave
tblPL.Category part in the statement?

Steve D wrote:
I am having all kinds of issues today. I have a query that works fine until I
add one more criteria and then I get a message that the "expression is typed
incorrectly or too complex..." If I take out 'AND
((tblPL.[Category])="Actual")' the query runs fine. Any thoughts?

SELECT qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent],
Format([Amount]*[Chargeback_Decimal],"Currency") AS [Chargeback Amount],
tblPL.Category
FROM qryAssociates_Paid_Chargeback_Pct LEFT JOIN tblPL ON
qryAssociates_Paid_Chargeback_Pct.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback") AND ((tblPL.[Cost Center])="228065")
AND ((tblPL.[Category])="Actual"))
GROUP BY qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent], tblPL.Amount,
qryAssociates_Paid_Chargeback_Pct.Chargeback_Decimal, tblPL.Category;
Click to expand...
Click to expand...
Click to expand...
 
Bad? Search on lookup fields to find the post that say it is.
The word 'Actual' is probably not stored in your [Category] field but a
number instead.
As John Spencer asked 'Is tblPL.Category a text field? Is tblPL.Category by
any chance a lookup field and it gets its value from another table?

If so, then you can join that table in your query and apply the criteria to
it instead of [Category] field.
--
KARL DEWEY
Build a little - Test a little


Steve D said:
Yes, [Category] is a lookup field. I am guessing that is a bad thing.
--
Thank You,
Steve


KARL DEWEY said:
Is [Category] lookup field by chance?
What if you use a different value in the criteria?

--
KARL DEWEY
Build a little - Test a little


Steve D said:
I still have the same problem, so it appears it is specific to the one
criteria ((tblPL.[Category])="Actual"). If I remove the criteria but leave
the field it works fine and shows me the value "Actual" in the field along
with other categories, if there are any. So it is clearly tied to the
criteria.
--
Thank You,
Steve


:

What happens when you remove one of the other AND statements instead?
i.e. remove 'AND ((tblPL.[Cost Center])="228065")' and leave
tblPL.Category part in the statement?

Steve D wrote:
I am having all kinds of issues today. I have a query that works fine until I
add one more criteria and then I get a message that the "expression is typed
incorrectly or too complex..." If I take out 'AND
((tblPL.[Category])="Actual")' the query runs fine. Any thoughts?

SELECT qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent],
Format([Amount]*[Chargeback_Decimal],"Currency") AS [Chargeback Amount],
tblPL.Category
FROM qryAssociates_Paid_Chargeback_Pct LEFT JOIN tblPL ON
qryAssociates_Paid_Chargeback_Pct.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback") AND ((tblPL.[Cost Center])="228065")
AND ((tblPL.[Category])="Actual"))
GROUP BY qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent], tblPL.Amount,
qryAssociates_Paid_Chargeback_Pct.Chargeback_Decimal, tblPL.Category;
Click to expand...
Click to expand...
Click to expand...
 
Thank you. I linked the lookup table into the query and it works now. Thank
you both for your assistance.
--
Thank You,
Steve


KARL DEWEY said:
Bad? Search on lookup fields to find the post that say it is.
The word 'Actual' is probably not stored in your [Category] field but a
number instead.
As John Spencer asked 'Is tblPL.Category a text field? Is tblPL.Category by
any chance a lookup field and it gets its value from another table?

If so, then you can join that table in your query and apply the criteria to
it instead of [Category] field.
--
KARL DEWEY
Build a little - Test a little


Steve D said:
Yes, [Category] is a lookup field. I am guessing that is a bad thing.
--
Thank You,
Steve


KARL DEWEY said:
Is [Category] lookup field by chance?
What if you use a different value in the criteria?

--
KARL DEWEY
Build a little - Test a little


:

I still have the same problem, so it appears it is specific to the one
criteria ((tblPL.[Category])="Actual"). If I remove the criteria but leave
the field it works fine and shows me the value "Actual" in the field along
with other categories, if there are any. So it is clearly tied to the
criteria.
--
Thank You,
Steve


:

What happens when you remove one of the other AND statements instead?
i.e. remove 'AND ((tblPL.[Cost Center])="228065")' and leave
tblPL.Category part in the statement?

Steve D wrote:
I am having all kinds of issues today. I have a query that works fine until I
add one more criteria and then I get a message that the "expression is typed
incorrectly or too complex..." If I take out 'AND
((tblPL.[Category])="Actual")' the query runs fine. Any thoughts?

SELECT qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent],
Format([Amount]*[Chargeback_Decimal],"Currency") AS [Chargeback Amount],
tblPL.Category
FROM qryAssociates_Paid_Chargeback_Pct LEFT JOIN tblPL ON
qryAssociates_Paid_Chargeback_Pct.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback") AND ((tblPL.[Cost Center])="228065")
AND ((tblPL.[Category])="Actual"))
GROUP BY qryAssociates_Paid_Chargeback_Pct.Oracle_Tier3,
qryAssociates_Paid_Chargeback_Pct.Region_Name_Tier3,
qryAssociates_Paid_Chargeback_Pct.[Associate Count],
qryAssociates_Paid_Chargeback_Pct.[Chargeback Percent], tblPL.Amount,
qryAssociates_Paid_Chargeback_Pct.Chargeback_Decimal, tblPL.Category;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top