Expression is to complicated?

L

Love Buzz

Hello all.

Please see the SQL below of the queury I am building. The queury worked
fine until I set criteria to count only fields that contain the word 'Over
Drawn'. I get the message 'This expression is typed incorrectly, or it is
too complex to be evaluated'.
When I take the criteria out it adds up everything fine.

It's the (((Count([Incoming Return Item Detail].MemoPostStatus))="Over
Drawn"));
part at the bottom.

Any suggestions? Thanks for your help.

SELECT [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], Count([Incoming Return Item Detail].ReturnReason) AS
CountOfReturnReason, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1,
Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount, [RCC
Customer List].[Officer Code], [SumOfItemAmount]/[$$ of Dep Items] AS Expr2,
Count([Incoming Return Item Detail].MemoPostStatus) AS CountOfMemoPostStatus
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], [RCC Customer List].[Officer Code], [RCC Customer
List].[Numb of Dep Items], [RCC Customer List].[$$ of Dep Items]
HAVING (((Count([Incoming Return Item Detail].MemoPostStatus))="Over Drawn"));
 
J

John Spencer

Count is always going to return a number, Never "Over Drawn". Try moving the
criteria to the WHERE clause

SELECT [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, Count([Incoming Return Item Detail].ReturnReason) AS CountOfReturnReason
, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1
, Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount
, [RCC Customer List].[Officer Code]
, [SumOfItemAmount]/[$$ of Dep Items] AS Expr2,
Count([Incoming Return Item Detail].MemoPostStatus) AS CountOfMemoPostStatus

FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail]
ON [RCC Customer List].[Account #] =
[Incoming Return Item Detail].DepositingAccount

WHERE ((([Incoming Return Item Detail].Date) Between [Start Date]
And [End Date])) AND [Incoming Return Item Detail].MemoPostStatus="Over Drawn"

GROUP BY [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, [RCC Customer List].[Officer Code]
, [RCC Customer List].[Numb of Dep Items]
, [RCC Customer List].[$$ of Dep Items]

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Love said:
Hello all.

Please see the SQL below of the queury I am building. The queury worked
fine until I set criteria to count only fields that contain the word 'Over
Drawn'. I get the message 'This expression is typed incorrectly, or it is
too complex to be evaluated'.
When I take the criteria out it adds up everything fine.

It's the (((Count([Incoming Return Item Detail].MemoPostStatus))="Over
Drawn"));
part at the bottom.

Any suggestions? Thanks for your help.

SELECT [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], Count([Incoming Return Item Detail].ReturnReason) AS
CountOfReturnReason, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1,
Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount, [RCC
Customer List].[Officer Code], [SumOfItemAmount]/[$$ of Dep Items] AS Expr2,
Count([Incoming Return Item Detail].MemoPostStatus) AS CountOfMemoPostStatus
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], [RCC Customer List].[Officer Code], [RCC Customer
List].[Numb of Dep Items], [RCC Customer List].[$$ of Dep Items]
HAVING (((Count([Incoming Return Item Detail].MemoPostStatus))="Over Drawn"));
Click to expand...
 
L

Love Buzz

Sorry that I did not elaborate. My intention is to count all of the 'Over
Drawn' labels in the column.

John Spencer said:
Count is always going to return a number, Never "Over Drawn". Try moving the
criteria to the WHERE clause

SELECT [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, Count([Incoming Return Item Detail].ReturnReason) AS CountOfReturnReason
, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1
, Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount
, [RCC Customer List].[Officer Code]
, [SumOfItemAmount]/[$$ of Dep Items] AS Expr2,
Count([Incoming Return Item Detail].MemoPostStatus) AS CountOfMemoPostStatus

FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail]
ON [RCC Customer List].[Account #] =
[Incoming Return Item Detail].DepositingAccount

WHERE ((([Incoming Return Item Detail].Date) Between [Start Date]
And [End Date])) AND [Incoming Return Item Detail].MemoPostStatus="Over Drawn"

GROUP BY [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, [RCC Customer List].[Officer Code]
, [RCC Customer List].[Numb of Dep Items]
, [RCC Customer List].[$$ of Dep Items]

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Love said:
Hello all.

Please see the SQL below of the queury I am building. The queury worked
fine until I set criteria to count only fields that contain the word 'Over
Drawn'. I get the message 'This expression is typed incorrectly, or it is
too complex to be evaluated'.
When I take the criteria out it adds up everything fine.

It's the (((Count([Incoming Return Item Detail].MemoPostStatus))="Over
Drawn"));
part at the bottom.

Any suggestions? Thanks for your help.

SELECT [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], Count([Incoming Return Item Detail].ReturnReason) AS
CountOfReturnReason, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1,
Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount, [RCC
Customer List].[Officer Code], [SumOfItemAmount]/[$$ of Dep Items] AS Expr2,
Count([Incoming Return Item Detail].MemoPostStatus) AS CountOfMemoPostStatus
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], [RCC Customer List].[Officer Code], [RCC Customer
List].[Numb of Dep Items], [RCC Customer List].[$$ of Dep Items]
HAVING (((Count([Incoming Return Item Detail].MemoPostStatus))="Over Drawn"));
Click to expand...
Click to expand...
 
J

John Spencer

Please elaborate a little more.

First, did you try the modified query? If so and it worked, what is wrong
with the results you got?

Do you mean you want to count in the query the number of records where
memoPostStatus is equal to Over Drawn as a separate field? But you don't want
the records that are returned to be limited to Over Drawn records.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Love said:
Sorry that I did not elaborate. My intention is to count all of the 'Over
Drawn' labels in the column.

John Spencer said:
Count is always going to return a number, Never "Over Drawn". Try moving the
criteria to the WHERE clause

SELECT [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, Count([Incoming Return Item Detail].ReturnReason) AS CountOfReturnReason
, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1
, Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount
, [RCC Customer List].[Officer Code]
, [SumOfItemAmount]/[$$ of Dep Items] AS Expr2,
Count([Incoming Return Item Detail].MemoPostStatus) AS CountOfMemoPostStatus

FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail]
ON [RCC Customer List].[Account #] =
[Incoming Return Item Detail].DepositingAccount

WHERE ((([Incoming Return Item Detail].Date) Between [Start Date]
And [End Date])) AND [Incoming Return Item Detail].MemoPostStatus="Over Drawn"

GROUP BY [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, [RCC Customer List].[Officer Code]
, [RCC Customer List].[Numb of Dep Items]
, [RCC Customer List].[$$ of Dep Items]

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Love said:
Hello all.

Please see the SQL below of the queury I am building. The queury worked
fine until I set criteria to count only fields that contain the word 'Over
Drawn'. I get the message 'This expression is typed incorrectly, or it is
too complex to be evaluated'.
When I take the criteria out it adds up everything fine.

It's the (((Count([Incoming Return Item Detail].MemoPostStatus))="Over
Drawn"));
part at the bottom.

Any suggestions? Thanks for your help.

SELECT [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], Count([Incoming Return Item Detail].ReturnReason) AS
CountOfReturnReason, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1,
Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount, [RCC
Customer List].[Officer Code], [SumOfItemAmount]/[$$ of Dep Items] AS Expr2,
Count([Incoming Return Item Detail].MemoPostStatus) AS CountOfMemoPostStatus
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], [RCC Customer List].[Officer Code], [RCC Customer
List].[Numb of Dep Items], [RCC Customer List].[$$ of Dep Items]
HAVING (((Count([Incoming Return Item Detail].MemoPostStatus))="Over Drawn"));
Click to expand...
Click to expand...
Click to expand...
 
L

Love Buzz

You are correct, the intent is to count everything in the MemoPostStatus
column that reflects Over Drawn, but only for those records that I have
specified with the other query commands.

I am getting that 'formula to complex' message when I try to run the query.
However, when I take the criteria out (Over Drawn), it counts everything in
that column for what I have specified in the other commands fine. But....I
only want those records counted that equal Over Drawn.

John Spencer said:
Please elaborate a little more.

First, did you try the modified query? If so and it worked, what is wrong
with the results you got?

Do you mean you want to count in the query the number of records where
memoPostStatus is equal to Over Drawn as a separate field? But you don't want
the records that are returned to be limited to Over Drawn records.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Love said:
Sorry that I did not elaborate. My intention is to count all of the 'Over
Drawn' labels in the column.

John Spencer said:
Count is always going to return a number, Never "Over Drawn". Try moving the
criteria to the WHERE clause

SELECT [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, Count([Incoming Return Item Detail].ReturnReason) AS CountOfReturnReason
, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1
, Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount
, [RCC Customer List].[Officer Code]
, [SumOfItemAmount]/[$$ of Dep Items] AS Expr2,
Count([Incoming Return Item Detail].MemoPostStatus) AS CountOfMemoPostStatus

FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail]
ON [RCC Customer List].[Account #] =
[Incoming Return Item Detail].DepositingAccount

WHERE ((([Incoming Return Item Detail].Date) Between [Start Date]
And [End Date])) AND [Incoming Return Item Detail].MemoPostStatus="Over Drawn"

GROUP BY [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, [RCC Customer List].[Officer Code]
, [RCC Customer List].[Numb of Dep Items]
, [RCC Customer List].[$$ of Dep Items]

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Love Buzz wrote:
Hello all.

Please see the SQL below of the queury I am building. The queury worked
fine until I set criteria to count only fields that contain the word 'Over
Drawn'. I get the message 'This expression is typed incorrectly, or it is
too complex to be evaluated'.
When I take the criteria out it adds up everything fine.

It's the (((Count([Incoming Return Item Detail].MemoPostStatus))="Over
Drawn"));
part at the bottom.

Any suggestions? Thanks for your help.

SELECT [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], Count([Incoming Return Item Detail].ReturnReason) AS
CountOfReturnReason, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1,
Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount, [RCC
Customer List].[Officer Code], [SumOfItemAmount]/[$$ of Dep Items] AS Expr2,
Count([Incoming Return Item Detail].MemoPostStatus) AS CountOfMemoPostStatus
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], [RCC Customer List].[Officer Code], [RCC Customer
List].[Numb of Dep Items], [RCC Customer List].[$$ of Dep Items]
HAVING (((Count([Incoming Return Item Detail].MemoPostStatus))="Over Drawn"));
Click to expand...
Click to expand...
Click to expand...
 
L

Love Buzz

Sir. I ran the query you suggested and it ran fine. However, the results I
received only brought back those records that had 'Over Drawn' as the
criteria for the table of accounts I am looking @. I want the results to
reflect all accounts in the table with activity, count the Over Drawn labels
and leave it blank for those accounts that did not have Over Drawn in that
column.

I hope that made sense. I really appreciate your assistance and patience :)

John Spencer said:
Count is always going to return a number, Never "Over Drawn". Try moving the
criteria to the WHERE clause

SELECT [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, Count([Incoming Return Item Detail].ReturnReason) AS CountOfReturnReason
, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1
, Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount
, [RCC Customer List].[Officer Code]
, [SumOfItemAmount]/[$$ of Dep Items] AS Expr2,
Count([Incoming Return Item Detail].MemoPostStatus) AS CountOfMemoPostStatus

FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail]
ON [RCC Customer List].[Account #] =
[Incoming Return Item Detail].DepositingAccount

WHERE ((([Incoming Return Item Detail].Date) Between [Start Date]
And [End Date])) AND [Incoming Return Item Detail].MemoPostStatus="Over Drawn"

GROUP BY [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, [RCC Customer List].[Officer Code]
, [RCC Customer List].[Numb of Dep Items]
, [RCC Customer List].[$$ of Dep Items]

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Love said:
Hello all.

Please see the SQL below of the queury I am building. The queury worked
fine until I set criteria to count only fields that contain the word 'Over
Drawn'. I get the message 'This expression is typed incorrectly, or it is
too complex to be evaluated'.
When I take the criteria out it adds up everything fine.

It's the (((Count([Incoming Return Item Detail].MemoPostStatus))="Over
Drawn"));
part at the bottom.

Any suggestions? Thanks for your help.

SELECT [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], Count([Incoming Return Item Detail].ReturnReason) AS
CountOfReturnReason, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1,
Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount, [RCC
Customer List].[Officer Code], [SumOfItemAmount]/[$$ of Dep Items] AS Expr2,
Count([Incoming Return Item Detail].MemoPostStatus) AS CountOfMemoPostStatus
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount, [RCC Customer
List].[Customer Name], [RCC Customer List].[Sub Product], [RCC Customer
List].[Date Opened], [RCC Customer List].[Officer Code], [RCC Customer
List].[Numb of Dep Items], [RCC Customer List].[$$ of Dep Items]
HAVING (((Count([Incoming Return Item Detail].MemoPostStatus))="Over Drawn"));
Click to expand...
Click to expand...
 
J

John Spencer

Perhaps what you want is the following.

SELECT [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, Count([Incoming Return Item Detail].ReturnReason) AS CountOfReturnReason
, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1
, Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount
, [RCC Customer List].[Officer Code]
, [SumOfItemAmount]/[$$ of Dep Items] AS Expr2
, Count([Incoming Return Item Detail].MemoPostStatus) AS
CountOfMemoPostStatus

, Abs(Sum([Incoming Return Item Detail].MemoPostStatus="Over Drawn")) as
CountOverDrawn

FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail]
ON [RCC Customer List].[Account #] =
[Incoming Return Item Detail].DepositingAccount

WHERE ((([Incoming Return Item Detail].Date) Between [Start Date]
And [End Date]))

GROUP BY [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, [RCC Customer List].[Officer Code]
, [RCC Customer List].[Numb of Dep Items]
, [RCC Customer List].[$$ of Dep Items]



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
L

Love Buzz

Perfect. Thank you for taking time out of your day to help me. I really
appreciate it.

John Spencer said:
Perhaps what you want is the following.

SELECT [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, Count([Incoming Return Item Detail].ReturnReason) AS CountOfReturnReason
, [CountOfReturnReason]/[Numb of Dep Items] AS Expr1
, Sum([Incoming Return Item Detail].ItemAmount) AS SumOfItemAmount
, [RCC Customer List].[Officer Code]
, [SumOfItemAmount]/[$$ of Dep Items] AS Expr2
, Count([Incoming Return Item Detail].MemoPostStatus) AS
CountOfMemoPostStatus

, Abs(Sum([Incoming Return Item Detail].MemoPostStatus="Over Drawn")) as
CountOverDrawn

FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail]
ON [RCC Customer List].[Account #] =
[Incoming Return Item Detail].DepositingAccount

WHERE ((([Incoming Return Item Detail].Date) Between [Start Date]
And [End Date]))

GROUP BY [Incoming Return Item Detail].DepositingAccount
, [RCC Customer List].[Customer Name]
, [RCC Customer List].[Sub Product]
, [RCC Customer List].[Date Opened]
, [RCC Customer List].[Officer Code]
, [RCC Customer List].[Numb of Dep Items]
, [RCC Customer List].[$$ of Dep Items]



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Love said:
Sir. I ran the query you suggested and it ran fine. However, the results I
received only brought back those records that had 'Over Drawn' as the
criteria for the table of accounts I am looking @. I want the results to
reflect all accounts in the table with activity, count the Over Drawn labels
and leave it blank for those accounts that did not have Over Drawn in that
column.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query with multiple criteria 1
Average Records between date range 12
Prompting for date range twice 3
Sum and Join 1
need to return next match of table, only finding first match...HEL 0
only returning 1st matching entry in table, need to find next matc 0
Is Excel the best MS Office tool to use for this business applicat 6
Is there a better way to run a parameter query on a form? 0

Top