Error when adding one more criteria

[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;

 

[Rich]

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?

 

[KARL DEWEY]

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

 

[Steve D]

I still get the same message when I use this code.
--
Thank You,
Steve

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

--
KARL DEWEY
Build a little - Test a little

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;

 

[Steve D]

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?

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;

 

[KARL DEWEY]

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?

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;

 

[John Spencer]

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
'====================================================

 

[Steve D]

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

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;

 

[KARL DEWEY]

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

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

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;

 

[Steve D]

Thank you. I linked the lookup table into the query and it works now. Thank
you both for your assistance.
--
Thank You,
Steve

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

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

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;