Getting #Error when I include an expression in a second expression

S

Steve D

I have a field that calculates the percent of the total, I am now trying to
multiply that percent by a value in another field and I keep getting #Error
as the result. The value of X is where I am having issues. I would appreciate
any help. The SQL is below:

SELECT qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount,
[qryAssociates_Paid_Chargeback].[Chargeback Percent]*[tblPL].[Amount] AS X
FROM qryAssociates_Paid_Chargeback INNER JOIN tblPL ON
qryAssociates_Paid_Chargeback.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback"))
GROUP BY qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount;
 
K

KARL DEWEY

Is [qryAssociates_Paid_Chargeback].[Chargeback Percent] a text field with the
percent sign in it by chance?
 
S

Steve D

No, it is a calculation from another query

Chargeback Percent: Format([Associate
Count]/DSum("qryAssociates_Paid_Chargeback.[Associate
Count]","qryAssociates_Paid_Chargeback"),"Percent")
--
Thank You,
Steve


KARL DEWEY said:
Is [qryAssociates_Paid_Chargeback].[Chargeback Percent] a text field with the
percent sign in it by chance?
--
KARL DEWEY
Build a little - Test a little


Steve D said:
I have a field that calculates the percent of the total, I am now trying to
multiply that percent by a value in another field and I keep getting #Error
as the result. The value of X is where I am having issues. I would appreciate
any help. The SQL is below:

SELECT qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount,
[qryAssociates_Paid_Chargeback].[Chargeback Percent]*[tblPL].[Amount] AS X
FROM qryAssociates_Paid_Chargeback INNER JOIN tblPL ON
qryAssociates_Paid_Chargeback.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback"))
GROUP BY qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount;
Click to expand...
Click to expand...
 
K

KARL DEWEY

But with the word 'Percent' it is text.
Change your query to this ---
Val([qryAssociates_Paid_Chargeback].[Chargeback
Percent])*[tblPL].[Amount] AS X

--
KARL DEWEY
Build a little - Test a little


Steve D said:
No, it is a calculation from another query

Chargeback Percent: Format([Associate
Count]/DSum("qryAssociates_Paid_Chargeback.[Associate
Count]","qryAssociates_Paid_Chargeback"),"Percent")
--
Thank You,
Steve


KARL DEWEY said:
Is [qryAssociates_Paid_Chargeback].[Chargeback Percent] a text field with the
percent sign in it by chance?
--
KARL DEWEY
Build a little - Test a little


Steve D said:
I have a field that calculates the percent of the total, I am now trying to
multiply that percent by a value in another field and I keep getting #Error
as the result. The value of X is where I am having issues. I would appreciate
any help. The SQL is below:

SELECT qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount,
[qryAssociates_Paid_Chargeback].[Chargeback Percent]*[tblPL].[Amount] AS X
FROM qryAssociates_Paid_Chargeback INNER JOIN tblPL ON
qryAssociates_Paid_Chargeback.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback"))
GROUP BY qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount;
Click to expand...
Click to expand...
Click to expand...
 
S

Steve D

The word percent does not appear in the field I get results that are
formatted like 22.46%, which is all I was trying to do with the format part
of the formula. I tried to use Val() like you suggested but it still gave me
the same #Error. Any other thoughts?
--
Thank You,
Steve


KARL DEWEY said:
But with the word 'Percent' it is text.
Change your query to this ---
Val([qryAssociates_Paid_Chargeback].[Chargeback
Percent])*[tblPL].[Amount] AS X

--
KARL DEWEY
Build a little - Test a little


Steve D said:
No, it is a calculation from another query

Chargeback Percent: Format([Associate
Count]/DSum("qryAssociates_Paid_Chargeback.[Associate
Count]","qryAssociates_Paid_Chargeback"),"Percent")
--
Thank You,
Steve


KARL DEWEY said:
Is [qryAssociates_Paid_Chargeback].[Chargeback Percent] a text field with the
percent sign in it by chance?
--
KARL DEWEY
Build a little - Test a little


:

I have a field that calculates the percent of the total, I am now trying to
multiply that percent by a value in another field and I keep getting #Error
as the result. The value of X is where I am having issues. I would appreciate
any help. The SQL is below:

SELECT qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount,
[qryAssociates_Paid_Chargeback].[Chargeback Percent]*[tblPL].[Amount] AS X
FROM qryAssociates_Paid_Chargeback INNER JOIN tblPL ON
qryAssociates_Paid_Chargeback.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback"))
GROUP BY qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount;
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Exactly what is the error?
--
KARL DEWEY
Build a little - Test a little


Steve D said:
The word percent does not appear in the field I get results that are
formatted like 22.46%, which is all I was trying to do with the format part
of the formula. I tried to use Val() like you suggested but it still gave me
the same #Error. Any other thoughts?
--
Thank You,
Steve


KARL DEWEY said:
But with the word 'Percent' it is text.
Change your query to this ---
Val([qryAssociates_Paid_Chargeback].[Chargeback
Percent])*[tblPL].[Amount] AS X

--
KARL DEWEY
Build a little - Test a little


Steve D said:
No, it is a calculation from another query

Chargeback Percent: Format([Associate
Count]/DSum("qryAssociates_Paid_Chargeback.[Associate
Count]","qryAssociates_Paid_Chargeback"),"Percent")
--
Thank You,
Steve


:

Is [qryAssociates_Paid_Chargeback].[Chargeback Percent] a text field with the
percent sign in it by chance?
--
KARL DEWEY
Build a little - Test a little


:

I have a field that calculates the percent of the total, I am now trying to
multiply that percent by a value in another field and I keep getting #Error
as the result. The value of X is where I am having issues. I would appreciate
any help. The SQL is below:

SELECT qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount,
[qryAssociates_Paid_Chargeback].[Chargeback Percent]*[tblPL].[Amount] AS X
FROM qryAssociates_Paid_Chargeback INNER JOIN tblPL ON
qryAssociates_Paid_Chargeback.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback"))
GROUP BY qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount;
Click to expand...
Click to expand...
Click to expand...
 
S

Steve D

I am getting #Error as the value in every record of the query. I think you
are correct about the cause. I removed the format from the orignal query and
instead of getting a % I get a decimal value which I am now able to multiply.
So I think I am okay now.

Thanks for your help.

--
Thank You,
Steve


KARL DEWEY said:
Exactly what is the error?
--
KARL DEWEY
Build a little - Test a little


Steve D said:
The word percent does not appear in the field I get results that are
formatted like 22.46%, which is all I was trying to do with the format part
of the formula. I tried to use Val() like you suggested but it still gave me
the same #Error. Any other thoughts?
--
Thank You,
Steve


KARL DEWEY said:
But with the word 'Percent' it is text.
Change your query to this ---
Val([qryAssociates_Paid_Chargeback].[Chargeback
Percent])*[tblPL].[Amount] AS X

--
KARL DEWEY
Build a little - Test a little


:

No, it is a calculation from another query

Chargeback Percent: Format([Associate
Count]/DSum("qryAssociates_Paid_Chargeback.[Associate
Count]","qryAssociates_Paid_Chargeback"),"Percent")
--
Thank You,
Steve


:

Is [qryAssociates_Paid_Chargeback].[Chargeback Percent] a text field with the
percent sign in it by chance?
--
KARL DEWEY
Build a little - Test a little


:

I have a field that calculates the percent of the total, I am now trying to
multiply that percent by a value in another field and I keep getting #Error
as the result. The value of X is where I am having issues. I would appreciate
any help. The SQL is below:

SELECT qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount,
[qryAssociates_Paid_Chargeback].[Chargeback Percent]*[tblPL].[Amount] AS X
FROM qryAssociates_Paid_Chargeback INNER JOIN tblPL ON
qryAssociates_Paid_Chargeback.Month = tblPL.Month
WHERE (((tblPL.Account)="Chargeback"))
GROUP BY qryAssociates_Paid_Chargeback.Oracle_Tier3,
qryAssociates_Paid_Chargeback.Region_Name_Tier3,
qryAssociates_Paid_Chargeback.[Associate Count],
qryAssociates_Paid_Chargeback.[Chargeback Percent], tblPL.Amount;
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Don't format the value. When you do you turn it into a string with "%" on the
end. Leave it as .2246 (or whatever) and use that in your calculations. If
you need it formatted then do that in the control that displays the value.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 

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

Error when adding one more criteria 9
Query does not include specified expression 6
Why am I asked to enter parameter value? (Second try) 3
can't use an aggregate function in an expression? 4
Invalid use of null in query expression 3
Expression too complex Error 6
Calculations based on counts in a crosstab 3
'Data type mismatch in criteria expression' error in Count query 2

Top