Equation Syntax

[Shoo]

What's wrong with this query equation? it prompts me to enter a value for order details.totalunitprice instead of retrieving it from the appropriate table. I guess I am missing a marker.

"LessDiscount: CCur([customers].[discount%]*[Invoices].[TotalUnitPrice])"

[customers] denotes customer table
[discount%] denotes a value within that table
[Invoices] denotes Invoices query
[totalUnitPrice] denotes a value within invoices query

Thanks.

 

[Shoo]

NB this field is within the Invoices query itself.

 

[Mile-O]

Post the complete SQL of the query.

 

[Shoo]

Here it is, thanks.

SELECT DISTINCTROW Orders.DeliveryName, Orders.DeliveryAddress, Orders.DeliveryCity, Orders.DeliveryRegion, Orders.DeliveryPostcode, Orders.CustomerID, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.Postcode, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, [FirstName] & " " & [LastName] AS Salesperson, [Order Details].ProductID, Products.ProductName, [Order Details].Quantity, [Order Details].UnitPrice, Customers.[Discount%], CCur([Order Details].[UnitPrice]*[Quantity]) AS TotalUnitPrice, CCur([customers].[discount%]*[invoices].[TotalUnitPrice]) AS Discount, Employees.[Commission%]
FROM Products INNER JOIN ((Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
GROUP BY Orders.DeliveryName, Orders.DeliveryAddress, Orders.DeliveryCity, Orders.DeliveryRegion, Orders.DeliveryPostcode, Orders.CustomerID, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.Postcode, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, [FirstName] & " " & [LastName], [Order Details].ProductID, Products.ProductName, [Order Details].Quantity, [Order Details].UnitPrice, Customers.[Discount%], CCur([Order Details].[UnitPrice]*[Quantity]), CCur([customers].[discount%]*[invoices].[TotalUnitPrice]), Employees.[Commission%];

 

[Shoo]

^

Any suggestions are appreciated.

 

[Shoo]

Looks like all I simply needed to do was:

"LessDiscount: CCur(([Order Details].[UnitPrice]*[Quantity])*[discount%])"