Here it is.
For your info, now the amount in USD appears correctly but I am still asked
to enter a value parameter for "Total amount per product" when I run the
query.
Thank you a lot. You've already helped me much.
SELECT [Agreement master].[Purchase or sale], [Agreement master].Company,
[Agreement master].Region, [Agreement master].RTO, [Agreement master].[Name
of trader], [Address book].[Name supplier customer], [Agreement
master].[Reference of paper contract], [Agreement master].ID, [Type of
contracts].[Type of contract], [Status of contract].[Explanation of ID],
[Agreement master].[Paper contract signed on], [Agreement master].[Pap cont
signed by n1], [Agreement master].[Pap cont signed by n2], [Agreement
master].[Pap cont signed by n3], [Agreement master].[Check of legal dpt],
[Agreement master].[Date check of legal dpt], [Agreement master].Activity,
[Agreement master].Incoterm, [Agreement master].Volume, [Agreement
master].[Product familiy], [Agreement master].Currency, [Agreement
master].Price, Nz([Price],0)*Nz([Volume],0) AS [Total amount per product],
IIf([Currency]="USD",[Total amount per product],IIf([Currency]="EUR",[Total
amount per product]*1.3,IIf([Currency]="GBP",[Total amount per
product]*1.884165,[Total amount per product]))) AS [Total in USD per prod]
FROM [Type of contracts] RIGHT JOIN ([Status of contract] RIGHT JOIN
([Address book] RIGHT JOIN [Agreement master] ON [Address book].[Address
Number] = [Agreement master].[ID supplier]) ON [Status of contract].[ID of
contract] = [Agreement master].[Status of paper contract]) ON [Type of
contracts].[ID contract] = [Agreement master].[Type of paper contract]
GROUP BY [Agreement master].[Purchase or sale], [Agreement master].Company,
[Agreement master].Region, [Agreement master].RTO, [Agreement master].[Name
of trader], [Address book].[Name supplier customer], [Agreement
master].[Reference of paper contract], [Agreement master].ID, [Type of
contracts].[Type of contract], [Status of contract].[Explanation of ID],
[Agreement master].[Paper contract signed on], [Agreement master].[Pap cont
signed by n1], [Agreement master].[Pap cont signed by n2], [Agreement
master].[Pap cont signed by n3], [Agreement master].[Check of legal dpt],
[Agreement master].[Date check of legal dpt], [Agreement master].Activity,
[Agreement master].Incoterm, [Agreement master].Volume, [Agreement
master].[Product familiy], [Agreement master].Currency, [Agreement
master].Price, Nz([Price],0)*Nz([Volume],0), IIf([Currency]="USD",[Total
amount per product],IIf([Currency]="EUR",[Total amount per
product]*1.3,IIf([Currency]="GBP",[Total amount per product]*1.884165,[Total
amount per product])))
ORDER BY [Agreement master].[Purchase or sale], [Agreement master].Company,
[Agreement master].Region, [Agreement master].RTO, [Agreement master].[Name
of trader], [Address book].[Name supplier customer], [Agreement
master].[Reference of paper contract], [Agreement master].ID;
"Ofer" a écrit :
Can you post the full query SQL?
--
\\// Live Long and Prosper \\//
BS"D
:
Thank you for your answers.
However, when I enter the new expression you gave me the same thing happens.
I still have the same dialog box asking me to enter a value for "Total amount
per product".
Also, do you have any idea why I have no result appearing in the column of
my second expression?
Thanks a lot.
"Ofer" a écrit :
Sorry, I just noticed that you had a calculated field with this name.
Check the name if it the same
Check if you have a parameter in this name (Query > Parameter), that will
prompt you with the message
To avoid empty values if null and return 0 instead use the NZ function
Total amount per product: Nz([Price],0)*Nz([Volume],0)
--
\\// Live Long and Prosper \\//
BS"D
:
It sound like you don't have a field name "Total amount per product", in this
is why you are prompt with the message to enter a parameter.
When you run a query, and you have a name that is not a field in the table
that the query is linked to, the query will assume it's a parameter, when you
press enter you return an empty value, and this is why the resault you are
getting are empty.
Check the names of the field, and replace it to the name of the field in the
table
--
\\// Live Long and Prosper \\//
BS"D
:
Hello everybody,
I am new to this forum and hope you will be able to help me. I am using
Access 2000 and have a problem with a query.
This one is composed of fields from the same table. Among them I have 3
fields : volume, currency and price which may have null value for the moment.
I have entered the following first expression
Total amount per product: [Price]*[Volume]
When I run the query I am asked to give a value of parameter for "Total
amount per product". I click OK every time but would like to avoid clicking
each time.
Also, I have entered a second expression to convert all my amounts in USD
Total in USD per prod: IIf([Currency]="USD";[Total amount per
product];IIf([Currency]="EUR";[Total amount per
product]*1.3;IIf([Currency]="GBP";[Total amount per product]*1.884165;[Total
amount per product])))
for which not only I get the same dialog box "enter a value of parameter"
for "total amount per product" when I run the query but the result of the
query appears blank for this second expression.
I have read several answers on that same subject but couldn't find where the
problem could exactly come from.
Could anybody help me?
Thanks a lot in advance
A user from Zurich
