How to automatically change number in expression?

[Guest]

I have limited knowledge of Access 2003 and want to find a way to have
numbers like: 1.02040816 in the following expression to change automatically
if I change them in a table using a form (something like Preferences to avoid
having to update manually)

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of PriceSold]*0.9775-[Sum
Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

As usually what I write makes sense to me, I hope it does to you too.
Let me know if it doesn't.

Thanks for any help in advance,

Emilio

 

[Jeff Boyce]

Emilio

You've described "how" you want to do something, but not what. It may be
that the business need you are trying to satisfy by changing numbers could
be handled another way, if we knew what you were trying to accomplish.

And just in case this is simpler than that, in Access, you store data in
tables, and display it in forms (and reports). If your form is "bound" to
your table, changing a value in the form means you've changed it in the
underlying table.

 

[Guest]

Thanks Jeff,

You are correct to enter the value in a table, the problem I am having is to
enter that value in my expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775


I tried including the table in the query that the report is based on, but
when I try to enter the value of CCardCharge for Visa to replace "0.9825" in
the expression can't do it, problem with syntax.
I think it is more complicated than that, I am missing steps.

Any ideas?

Thanks again,
Emilio

Emilio

You've described "how" you want to do something, but not what. It may be
that the business need you are trying to satisfy by changing numbers could
be handled another way, if we knew what you were trying to accomplish.

And just in case this is simpler than that, in Access, you store data in
tables, and display it in forms (and reports). If your form is "bound" to
your table, changing a value in the form means you've changed it in the
underlying table.

--
Regards

Jeff Boyce
<Access MVP>

I have limited knowledge of Access 2003 and want to find a way to have
numbers like: 1.02040816 in the following expression to change automatically
if I change them in a table using a form (something like Preferences to avoid
having to update manually)

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of PriceSold]*0.9775-[Sum
Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

As usually what I write makes sense to me, I hope it does to you too.
Let me know if it doesn't.

Thanks for any help in advance,

Emilio

 

[Jeff Boyce]

I'm still not clear. Are you trying to use the expression in a table? If
so, where/how?

I'd recommend using a query to do this kind of calculation. If you join
your "?main transaction?" table to your "?payment method?" table, you can
use the CCCharges column as part of your calculation.

"Problem with syntax" doesn't give us much to go on -- can you post the
exact error message?

Please post your SQL statement.

 

[Guest]

Sorry, I didn't explain properly from the beginning.
The expression is used in a text box in the detail part of a report.

I can't reproduce the error message, because I reverted what I did (which
was wrong anyway, not even close)

The query is:

SELECT Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge
FROM tblCCardCharge, [Payment Methods] INNER JOIN (Orders INNER JOIN
Payments ON Orders.OrderID = Payments.OrderID) ON [Payment
Methods].PaymentMethodID = Payments.PaymentMethodID
GROUP BY Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge, Payments.PaymentMethodID
HAVING (((Payments.PaymentDate) Like "*" & "/" & "*" & "/" & [Enter Year:])
AND ((Orders.Condominium)=No));

Hope this helps
Thanks again, for your patience.
Emilio

I'm still not clear. Are you trying to use the expression in a table? If
so, where/how?

I'd recommend using a query to do this kind of calculation. If you join
your "?main transaction?" table to your "?payment method?" table, you can
use the CCCharges column as part of your calculation.

"Problem with syntax" doesn't give us much to go on -- can you post the
exact error message?

Please post your SQL statement.

--
Regards

Jeff Boyce
<Access MVP>

Thanks Jeff,

You are correct to enter the value in a table, the problem I am having is to
enter that value in my expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775


I tried including the table in the query that the report is based on, but
when I try to enter the value of CCardCharge for Visa to replace "0.9825" in
the expression can't do it, problem with syntax.
I think it is more complicated than that, I am missing steps.

Any ideas?

Thanks again,
Emilio

 

[Jeff Boyce]

Do I understand that you no longer get the error? Is there an issue still
to be resolved?

Jeff Boyce
<Access MVP>

Sorry, I didn't explain properly from the beginning.
The expression is used in a text box in the detail part of a report.

I can't reproduce the error message, because I reverted what I did (which
was wrong anyway, not even close)

The query is:

SELECT Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge
FROM tblCCardCharge, [Payment Methods] INNER JOIN (Orders INNER JOIN
Payments ON Orders.OrderID = Payments.OrderID) ON [Payment
Methods].PaymentMethodID = Payments.PaymentMethodID
GROUP BY Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge, Payments.PaymentMethodID
HAVING (((Payments.PaymentDate) Like "*" & "/" & "*" & "/" & [Enter Year:])
AND ((Orders.Condominium)=No));

Hope this helps
Thanks again, for your patience.
Emilio

I'm still not clear. Are you trying to use the expression in a table? If
so, where/how?

I'd recommend using a query to do this kind of calculation. If you join
your "?main transaction?" table to your "?payment method?" table, you can
use the CCCharges column as part of your calculation.

"Problem with syntax" doesn't give us much to go on -- can you post the
exact error message?

Please post your SQL statement.

--
Regards

Jeff Boyce
<Access MVP>

Thanks Jeff,

You are correct to enter the value in a table, the problem I am having

is
to

enter that value in my expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775


I tried including the table in the query that the report is based on, but
when I try to enter the value of CCardCharge for Visa to replace

"0.9825"
in

the expression can't do it, problem with syntax.
I think it is more complicated than that, I am missing steps.

Any ideas?

Thanks again,
Emilio

 

[Guest]

Hi Jeff,

The problem still there, I can't get the expression in the report to work
with the table I created.
The error message was a side effect of something I was trying.


Emilio

Do I understand that you no longer get the error? Is there an issue still
to be resolved?

Jeff Boyce
<Access MVP>

Sorry, I didn't explain properly from the beginning.
The expression is used in a text box in the detail part of a report.

I can't reproduce the error message, because I reverted what I did (which
was wrong anyway, not even close)

The query is:

SELECT Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge
FROM tblCCardCharge, [Payment Methods] INNER JOIN (Orders INNER JOIN
Payments ON Orders.OrderID = Payments.OrderID) ON [Payment
Methods].PaymentMethodID = Payments.PaymentMethodID
GROUP BY Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge, Payments.PaymentMethodID
HAVING (((Payments.PaymentDate) Like "*" & "/" & "*" & "/" & [Enter Year:])
AND ((Orders.Condominium)=No));

Hope this helps
Thanks again, for your patience.
Emilio

I'm still not clear. Are you trying to use the expression in a table? If
so, where/how?

I'd recommend using a query to do this kind of calculation. If you join
your "?main transaction?" table to your "?payment method?" table, you can
use the CCCharges column as part of your calculation.

"Problem with syntax" doesn't give us much to go on -- can you post the
exact error message?

Please post your SQL statement.

--
Regards

Jeff Boyce
<Access MVP>

Thanks Jeff,

You are correct to enter the value in a table, the problem I am having is
to
enter that value in my expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775


I tried including the table in the query that the report is based on, but
when I try to enter the value of CCardCharge for Visa to replace "0.9825"
in
the expression can't do it, problem with syntax.
I think it is more complicated than that, I am missing steps.

Any ideas?

Thanks again,
Emilio

 

[Jeff Boyce]

The expression you posted earlier appears no where in the SQL statement you
provided. Where are you attempting to use the IIF() expression?

Jeff Boyce
<Access MVP>

Hi Jeff,

The problem still there, I can't get the expression in the report to work
with the table I created.
The error message was a side effect of something I was trying.


Emilio

Do I understand that you no longer get the error? Is there an issue still
to be resolved?

Jeff Boyce
<Access MVP>

Sorry, I didn't explain properly from the beginning.
The expression is used in a text box in the detail part of a report.

I can't reproduce the error message, because I reverted what I did (which
was wrong anyway, not even close)

The query is:

SELECT Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge
FROM tblCCardCharge, [Payment Methods] INNER JOIN (Orders INNER JOIN
Payments ON Orders.OrderID = Payments.OrderID) ON [Payment
Methods].PaymentMethodID = Payments.PaymentMethodID
GROUP BY Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge, Payments.PaymentMethodID
HAVING (((Payments.PaymentDate) Like "*" & "/" & "*" & "/" & [Enter Year:])
AND ((Orders.Condominium)=No));

Hope this helps
Thanks again, for your patience.
Emilio


:

I'm still not clear. Are you trying to use the expression in a

table?
If

so, where/how?

I'd recommend using a query to do this kind of calculation. If you join
your "?main transaction?" table to your "?payment method?" table,

you
can

use the CCCharges column as part of your calculation.

"Problem with syntax" doesn't give us much to go on -- can you post the
exact error message?

Please post your SQL statement.

--
Regards

Jeff Boyce
<Access MVP>

Thanks Jeff,

You are correct to enter the value in a table, the problem I am

having
is

to
enter that value in my expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775


I tried including the table in the query that the report is based

on,
but

when I try to enter the value of CCardCharge for Visa to replace "0.9825"
in
the expression can't do it, problem with syntax.
I think it is more complicated than that, I am missing steps.

Any ideas?

Thanks again,
Emilio

 

[Guest]

The expression is used in a text box in the detail part of a report.

This is the original expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of PriceSold]*0.9775-[Sum
Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

I want the values (like: 0.9825) to change if the value is changed in the
table:

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775

How to do it is my problem

Thanks again,
Emilio

The expression you posted earlier appears no where in the SQL statement you
provided. Where are you attempting to use the IIF() expression?

Jeff Boyce
<Access MVP>

Hi Jeff,

The problem still there, I can't get the expression in the report to work
with the table I created.
The error message was a side effect of something I was trying.


Emilio

Do I understand that you no longer get the error? Is there an issue still
to be resolved?

Jeff Boyce
<Access MVP>

Sorry, I didn't explain properly from the beginning.
The expression is used in a text box in the detail part of a report.

I can't reproduce the error message, because I reverted what I did (which
was wrong anyway, not even close)

The query is:

SELECT Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge
FROM tblCCardCharge, [Payment Methods] INNER JOIN (Orders INNER JOIN
Payments ON Orders.OrderID = Payments.OrderID) ON [Payment
Methods].PaymentMethodID = Payments.PaymentMethodID
GROUP BY Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge, Payments.PaymentMethodID
HAVING (((Payments.PaymentDate) Like "*" & "/" & "*" & "/" & [Enter
Year:])
AND ((Orders.Condominium)=No));

Hope this helps
Thanks again, for your patience.
Emilio


:

I'm still not clear. Are you trying to use the expression in a table?
If
so, where/how?

I'd recommend using a query to do this kind of calculation. If you join
your "?main transaction?" table to your "?payment method?" table, you
can
use the CCCharges column as part of your calculation.

"Problem with syntax" doesn't give us much to go on -- can you post the
exact error message?

Please post your SQL statement.

--
Regards

Jeff Boyce
<Access MVP>

Thanks Jeff,

You are correct to enter the value in a table, the problem I am having
is
to
enter that value in my expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775


I tried including the table in the query that the report is based on,
but
when I try to enter the value of CCardCharge for Visa to replace
"0.9825"
in
the expression can't do it, problem with syntax.
I think it is more complicated than that, I am missing steps.

Any ideas?

Thanks again,
Emilio

 

[George Nicholson]

If the CCFactor is in a table, can't you add it to the query your report is
based on so that CC Factor becomes a field in the report? The field would
show 0.9825 for Visa, 0.9775 for Mastercard and 1.02040816 for all other
possible payment methods. That way, changing the values in the table would
automatically be reflected in the report.

I believe that
= ([Sum Of PriceSold]* [CCFactor])-[Sum Of PriceSold]
could be simplified as
= [Sum Of PriceSold]* ([CCFactor]-1)
and that should be all you need if you can get the CCFactor into your query.
(And adding PaymentMethods in the future wouldn't require re-writing
formulas or code, maybe just the query.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

The expression is used in a text box in the detail part of a report.

This is the original expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum
Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

I want the values (like: 0.9825) to change if the value is changed in the
table:

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775

How to do it is my problem

Thanks again,
Emilio

The expression you posted earlier appears no where in the SQL statement
you
provided. Where are you attempting to use the IIF() expression?

Jeff Boyce
<Access MVP>

Hi Jeff,

The problem still there, I can't get the expression in the report to
work
with the table I created.
The error message was a side effect of something I was trying.


Emilio



:

Do I understand that you no longer get the error? Is there an issue still
to be resolved?

Jeff Boyce
<Access MVP>

message
Sorry, I didn't explain properly from the beginning.
The expression is used in a text box in the detail part of a
report.

I can't reproduce the error message, because I reverted what I did (which
was wrong anyway, not even close)

The query is:

SELECT Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge
FROM tblCCardCharge, [Payment Methods] INNER JOIN (Orders INNER
JOIN
Payments ON Orders.OrderID = Payments.OrderID) ON [Payment
Methods].PaymentMethodID = Payments.PaymentMethodID
GROUP BY Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod, Orders.Condominium,
tblCCardCharge.CCardCharge, Payments.PaymentMethodID
HAVING (((Payments.PaymentDate) Like "*" & "/" & "*" & "/" & [Enter
Year:])
AND ((Orders.Condominium)=No));

Hope this helps
Thanks again, for your patience.
Emilio


:

I'm still not clear. Are you trying to use the expression in a table?
If
so, where/how?

I'd recommend using a query to do this kind of calculation. If
you join
your "?main transaction?" table to your "?payment method?" table, you
can
use the CCCharges column as part of your calculation.

"Problem with syntax" doesn't give us much to go on -- can you
post the
exact error message?

Please post your SQL statement.

--
Regards

Jeff Boyce
<Access MVP>

Thanks Jeff,

You are correct to enter the value in a table, the problem I am having
is
to
enter that value in my expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum Of
PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of
PriceSold]*1.02040816-[Sum Of
PriceSold]))))

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775


I tried including the table in the query that the report is
based on,
but
when I try to enter the value of CCardCharge for Visa to
replace
"0.9825"
in
the expression can't do it, problem with syntax.
I think it is more complicated than that, I am missing steps.

Any ideas?

Thanks again,
Emilio

 

[Guest]

I have not followed all this but I think you can do it by building a lookup
table like -
CreditCard CCFactor
Mastercard 0.9775
Visa 0.9825
Others 1.02040816

Put the lookup table in your query but do not join. In the column listing
the credit card type field put [LookupTable].[CCFactor] in your math formula
and in the criteria put [LookupTable].[CreditCard].

If the CCFactor is in a table, can't you add it to the query your report is
based on so that CC Factor becomes a field in the report? The field would
show 0.9825 for Visa, 0.9775 for Mastercard and 1.02040816 for all other
possible payment methods. That way, changing the values in the table would
automatically be reflected in the report.

I believe that
= ([Sum Of PriceSold]* [CCFactor])-[Sum Of PriceSold]
could be simplified as
= [Sum Of PriceSold]* ([CCFactor]-1)
and that should be all you need if you can get the CCFactor into your query.
(And adding PaymentMethods in the future wouldn't require re-writing
formulas or code, maybe just the query.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

The expression is used in a text box in the detail part of a report.

This is the original expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum
Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

I want the values (like: 0.9825) to change if the value is changed in the
table:

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775

How to do it is my problem

Thanks again,
Emilio

The expression you posted earlier appears no where in the SQL statement
you
provided. Where are you attempting to use the IIF() expression?

Jeff Boyce
<Access MVP>

Hi Jeff,

The problem still there, I can't get the expression in the report to
work
with the table I created.
The error message was a side effect of something I was trying.


Emilio



:

Do I understand that you no longer get the error? Is there an issue
still
to be resolved?

Jeff Boyce
<Access MVP>

message
Sorry, I didn't explain properly from the beginning.
The expression is used in a text box in the detail part of a
report.

I can't reproduce the error message, because I reverted what I did
(which
was wrong anyway, not even close)

The query is:

SELECT Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod,
Orders.Condominium,
tblCCardCharge.CCardCharge
FROM tblCCardCharge, [Payment Methods] INNER JOIN (Orders INNER
JOIN
Payments ON Orders.OrderID = Payments.OrderID) ON [Payment
Methods].PaymentMethodID = Payments.PaymentMethodID
GROUP BY Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod,
Orders.Condominium,
tblCCardCharge.CCardCharge, Payments.PaymentMethodID
HAVING (((Payments.PaymentDate) Like "*" & "/" & "*" & "/" & [Enter
Year:])
AND ((Orders.Condominium)=No));

Hope this helps
Thanks again, for your patience.
Emilio


:

I'm still not clear. Are you trying to use the expression in a
table?
If
so, where/how?

I'd recommend using a query to do this kind of calculation. If
you
join
your "?main transaction?" table to your "?payment method?" table,
you
can
use the CCCharges column as part of your calculation.

"Problem with syntax" doesn't give us much to go on -- can you
post
the
exact error message?

Please post your SQL statement.

--
Regards

Jeff Boyce
<Access MVP>

message
Thanks Jeff,

You are correct to enter the value in a table, the problem I am
having
is
to
enter that value in my expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum Of
PriceSold]),IIf([PaymentMethod]<>"Visa"
Or
[PaymentMethod]<>"Mastercard",([Sum Of
PriceSold]*1.02040816-[Sum
Of
PriceSold]))))

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775


I tried including the table in the query that the report is
based
on,
but
when I try to enter the value of CCardCharge for Visa to
replace
"0.9825"
in
the expression can't do it, problem with syntax.
I think it is more complicated than that, I am missing steps.

Any ideas?

Thanks again,
Emilio

 

[Jeff Boyce]

Not sure I followed, but if you add a table (unjoined) in a query, I believe
you get the cartesian product (all possible combinations).

If there's a field in the "main" table that points to a credit card type,
and a lookup table with credit card type and factor, I'd recommend, as
George did, joining on the credit card type field and returning the CCFactor
field, so you can further use it in the query/report. You wouldn't need the
IIF() statement at all that way (but you would still want to do the
math/calculation) in the query.

Regards

Jeff Boyce
<Access MVP>

I have not followed all this but I think you can do it by building a lookup
table like -
CreditCard CCFactor
Mastercard 0.9775
Visa 0.9825
Others 1.02040816

Put the lookup table in your query but do not join. In the column listing
the credit card type field put [LookupTable].[CCFactor] in your math formula
and in the criteria put [LookupTable].[CreditCard].

If the CCFactor is in a table, can't you add it to the query your report is
based on so that CC Factor becomes a field in the report? The field would
show 0.9825 for Visa, 0.9775 for Mastercard and 1.02040816 for all other
possible payment methods. That way, changing the values in the table would
automatically be reflected in the report.

I believe that
= ([Sum Of PriceSold]* [CCFactor])-[Sum Of PriceSold]
could be simplified as
= [Sum Of PriceSold]* ([CCFactor]-1)
and that should be all you need if you can get the CCFactor into your query.
(And adding PaymentMethods in the future wouldn't require re-writing
formulas or code, maybe just the query.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

The expression is used in a text box in the detail part of a report.

This is the original expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum
Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

I want the values (like: 0.9825) to change if the value is changed in the
table:

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775

How to do it is my problem

Thanks again,
Emilio


:

The expression you posted earlier appears no where in the SQL statement
you
provided. Where are you attempting to use the IIF() expression?

Jeff Boyce
<Access MVP>

Hi Jeff,

The problem still there, I can't get the expression in the report to
work
with the table I created.
The error message was a side effect of something I was trying.


Emilio



:

Do I understand that you no longer get the error? Is there an issue
still
to be resolved?

Jeff Boyce
<Access MVP>

message
Sorry, I didn't explain properly from the beginning.
The expression is used in a text box in the detail part of a
report.

I can't reproduce the error message, because I reverted what I did
(which
was wrong anyway, not even close)

The query is:

SELECT Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod,
Orders.Condominium,
tblCCardCharge.CCardCharge
FROM tblCCardCharge, [Payment Methods] INNER JOIN (Orders INNER
JOIN
Payments ON Orders.OrderID = Payments.OrderID) ON [Payment
Methods].PaymentMethodID = Payments.PaymentMethodID
GROUP BY Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod,
Orders.Condominium,
tblCCardCharge.CCardCharge, Payments.PaymentMethodID
HAVING (((Payments.PaymentDate) Like "*" & "/" & "*" & "/" & [Enter
Year:])
AND ((Orders.Condominium)=No));

Hope this helps
Thanks again, for your patience.
Emilio


:

I'm still not clear. Are you trying to use the expression in a
table?
If
so, where/how?

I'd recommend using a query to do this kind of calculation. If
you
join
your "?main transaction?" table to your "?payment method?" table,
you
can
use the CCCharges column as part of your calculation.

"Problem with syntax" doesn't give us much to go on -- can you
post
the
exact error message?

Please post your SQL statement.

--
Regards

Jeff Boyce
<Access MVP>

message
Thanks Jeff,

You are correct to enter the value in a table, the problem I am
having
is
to
enter that value in my expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum Of
PriceSold]),IIf([PaymentMethod]<>"Visa"
Or
[PaymentMethod]<>"Mastercard",([Sum Of
PriceSold]*1.02040816-[Sum
Of
PriceSold]))))

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775


I tried including the table in the query that the report is
based
on,
but
when I try to enter the value of CCardCharge for Visa to
replace
"0.9825"
in
the expression can't do it, problem with syntax.
I think it is more complicated than that, I am missing steps.

Any ideas?

Thanks again,
Emilio

 

[Guest]

You will not get the cartesian product if you use a field of the second table
as criteria for a field of the first table.

This is usefull when there is not a direct correlation between the contents
of the two fields like one is number and the other text. Also you can parse
the fields.

Not sure I followed, but if you add a table (unjoined) in a query, I believe
you get the cartesian product (all possible combinations).

If there's a field in the "main" table that points to a credit card type,
and a lookup table with credit card type and factor, I'd recommend, as
George did, joining on the credit card type field and returning the CCFactor
field, so you can further use it in the query/report. You wouldn't need the
IIF() statement at all that way (but you would still want to do the
math/calculation) in the query.

Regards

Jeff Boyce
<Access MVP>

I have not followed all this but I think you can do it by building a lookup
table like -
CreditCard CCFactor
Mastercard 0.9775
Visa 0.9825
Others 1.02040816

Put the lookup table in your query but do not join. In the column listing
the credit card type field put [LookupTable].[CCFactor] in your math formula
and in the criteria put [LookupTable].[CreditCard].

If the CCFactor is in a table, can't you add it to the query your report is
based on so that CC Factor becomes a field in the report? The field would
show 0.9825 for Visa, 0.9775 for Mastercard and 1.02040816 for all other
possible payment methods. That way, changing the values in the table would
automatically be reflected in the report.

I believe that
= ([Sum Of PriceSold]* [CCFactor])-[Sum Of PriceSold]
could be simplified as
= [Sum Of PriceSold]* ([CCFactor]-1)
and that should be all you need if you can get the CCFactor into your query.
(And adding PaymentMethods in the future wouldn't require re-writing
formulas or code, maybe just the query.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


The expression is used in a text box in the detail part of a report.

This is the original expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum
Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

I want the values (like: 0.9825) to change if the value is changed in the
table:

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775

How to do it is my problem

Thanks again,
Emilio


:

The expression you posted earlier appears no where in the SQL statement
you
provided. Where are you attempting to use the IIF() expression?

Jeff Boyce
<Access MVP>

Hi Jeff,

The problem still there, I can't get the expression in the report to
work
with the table I created.
The error message was a side effect of something I was trying.


Emilio



:

Do I understand that you no longer get the error? Is there an issue
still
to be resolved?

Jeff Boyce
<Access MVP>

message
Sorry, I didn't explain properly from the beginning.
The expression is used in a text box in the detail part of a
report.

I can't reproduce the error message, because I reverted what I did
(which
was wrong anyway, not even close)

The query is:

SELECT Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod,
Orders.Condominium,
tblCCardCharge.CCardCharge
FROM tblCCardCharge, [Payment Methods] INNER JOIN (Orders INNER
JOIN
Payments ON Orders.OrderID = Payments.OrderID) ON [Payment
Methods].PaymentMethodID = Payments.PaymentMethodID
GROUP BY Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod,
Orders.Condominium,
tblCCardCharge.CCardCharge, Payments.PaymentMethodID
HAVING (((Payments.PaymentDate) Like "*" & "/" & "*" & "/" & [Enter
Year:])
AND ((Orders.Condominium)=No));

Hope this helps
Thanks again, for your patience.
Emilio


:

I'm still not clear. Are you trying to use the expression in a
table?
If
so, where/how?

I'd recommend using a query to do this kind of calculation. If
you
join
your "?main transaction?" table to your "?payment method?" table,
you
can
use the CCCharges column as part of your calculation.

"Problem with syntax" doesn't give us much to go on -- can you
post
the
exact error message?

Please post your SQL statement.

--
Regards

Jeff Boyce
<Access MVP>

message
Thanks Jeff,

You are correct to enter the value in a table, the problem I am
having
is
to
enter that value in my expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum Of
PriceSold]),IIf([PaymentMethod]<>"Visa"
Or
[PaymentMethod]<>"Mastercard",([Sum Of
PriceSold]*1.02040816-[Sum
Of
PriceSold]))))

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775


I tried including the table in the query that the report is
based
on,
but
when I try to enter the value of CCardCharge for Visa to
replace
"0.9825"
in
the expression can't do it, problem with syntax.
I think it is more complicated than that, I am missing steps.

Any ideas?

Thanks again,
Emilio

 

[Jeff Boyce]

Thanks! I don't recall using that approach before!

Jeff Boyce
<Access MVP>

You will not get the cartesian product if you use a field of the second table
as criteria for a field of the first table.

This is usefull when there is not a direct correlation between the contents
of the two fields like one is number and the other text. Also you can parse
the fields.

Not sure I followed, but if you add a table (unjoined) in a query, I believe
you get the cartesian product (all possible combinations).

If there's a field in the "main" table that points to a credit card type,
and a lookup table with credit card type and factor, I'd recommend, as
George did, joining on the credit card type field and returning the CCFactor
field, so you can further use it in the query/report. You wouldn't need the
IIF() statement at all that way (but you would still want to do the
math/calculation) in the query.

Regards

Jeff Boyce
<Access MVP>

I have not followed all this but I think you can do it by building a lookup
table like -
CreditCard CCFactor
Mastercard 0.9775
Visa 0.9825
Others 1.02040816

Put the lookup table in your query but do not join. In the column listing
the credit card type field put [LookupTable].[CCFactor] in your math formula
and in the criteria put [LookupTable].[CreditCard].




:

If the CCFactor is in a table, can't you add it to the query your

report
is

based on so that CC Factor becomes a field in the report? The field would
show 0.9825 for Visa, 0.9775 for Mastercard and 1.02040816 for all other
possible payment methods. That way, changing the values in the table would
automatically be reflected in the report.

I believe that
= ([Sum Of PriceSold]* [CCFactor])-[Sum Of PriceSold]
could be simplified as
= [Sum Of PriceSold]* ([CCFactor]-1)
and that should be all you need if you can get the CCFactor into

your
query.

(And adding PaymentMethods in the future wouldn't require re-writing
formulas or code, maybe just the query.)

HTH,
--
George Nicholson

Remove 'Junk' from return address.


The expression is used in a text box in the detail part of a report.

This is the original expression:

=IIf([PaymentMethod]="Visa",([Sum Of PriceSold]*0.9825-[Sum Of
PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum
Of PriceSold]),IIf([PaymentMethod]<>"Visa" Or
[PaymentMethod]<>"Mastercard",([Sum Of PriceSold]*1.02040816-[Sum Of
PriceSold]))))

I want the values (like: 0.9825) to change if the value is changed

in
the

table:

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775

How to do it is my problem

Thanks again,
Emilio


:

The expression you posted earlier appears no where in the SQL statement
you
provided. Where are you attempting to use the IIF() expression?

Jeff Boyce
<Access MVP>

Hi Jeff,

The problem still there, I can't get the expression in the

report
to

work
with the table I created.
The error message was a side effect of something I was trying.


Emilio



:

Do I understand that you no longer get the error? Is there

an
issue

still
to be resolved?

Jeff Boyce
<Access MVP>

message
Sorry, I didn't explain properly from the beginning.
The expression is used in a text box in the detail part of a
report.

I can't reproduce the error message, because I reverted

what I
did

(which
was wrong anyway, not even close)

The query is:

SELECT Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod,
Orders.Condominium,
tblCCardCharge.CCardCharge
FROM tblCCardCharge, [Payment Methods] INNER JOIN (Orders INNER
JOIN
Payments ON Orders.OrderID = Payments.OrderID) ON [Payment
Methods].PaymentMethodID = Payments.PaymentMethodID
GROUP BY Orders.OrderID, Orders.JobName, Payments.PaymentAmount,
Payments.PaymentDate, [Payment Methods].PaymentMethod,
Orders.Condominium,
tblCCardCharge.CCardCharge, Payments.PaymentMethodID
HAVING (((Payments.PaymentDate) Like "*" & "/" & "*" & "/"

&
[Enter

Year:])
AND ((Orders.Condominium)=No));

Hope this helps
Thanks again, for your patience.
Emilio


:

I'm still not clear. Are you trying to use the

expression in
a

table?
If
so, where/how?

I'd recommend using a query to do this kind of

calculation.
If

you
join
your "?main transaction?" table to your "?payment

method?"
table,

you
can
use the CCCharges column as part of your calculation.

"Problem with syntax" doesn't give us much to go on --

can
you

post
the
exact error message?

Please post your SQL statement.

--
Regards

Jeff Boyce
<Access MVP>

"Wind54Surfer" <[email protected]>

wrote
in

message
Thanks Jeff,

You are correct to enter the value in a table, the

problem
I am

having
is
to
enter that value in my expression:

=IIf([PaymentMethod]="Visa",([Sum Of

PriceSold]*0.9825-[Sum
Of

PriceSold]),IIf([PaymentMethod]="MasterCard ",([Sum Of
PriceSold]*0.9775-[Sum Of
PriceSold]),IIf([PaymentMethod]<>"Visa"
Or
[PaymentMethod]<>"Mastercard",([Sum Of
PriceSold]*1.02040816-[Sum
Of
PriceSold]))))

The table is "tblCCardCharge" and is as follows:

CCardChargeID CreditCard CCardCharge
1 Visa 0.9825
2 MasterCard 0.9775


I tried including the table in the query that the report is
based
on,
but
when I try to enter the value of CCardCharge for Visa to
replace
"0.9825"
in
the expression can't do it, problem with syntax.
I think it is more complicated than that, I am missing steps.

Any ideas?

Thanks again,
Emilio