formula in table

[sbaily]

hi

can anyone help me with this?
i am wanting to put a formula in access but to be in the table rather than the form.
I want to create and If formula to look at one date in one cell and minus it against todays date. but if a further date has been entered into a different cell then use that date rather than todays date.

is this possible?

cheers

 

[alansidman]

You are not getting any results because the [Avg Of UnitPrice] and [Sum Of UnitSold] are not fields in your query. In order for the formula to work, these must be fields in your query. Where are they located?

Alan

 

[deke]

Sorry, let me try this again.

SELECT DISTINCTROW Products.ProductName, ItemsTable.Items, [Inventory Transactions].TransactionDate, [Inventory Transactions].TransactionDescription, Avg(Products.UnitPrice) AS [Avg Of UnitPrice], Sum([Inventory Transactions].UnitsSold) AS [Sum Of UnitsSold], Sum([Inventory Transactions].UnitsShrinkage) AS [Sum Of UnitsShrinkage], [Avg Of UnitPrice]*[Sum Of UnitSold]
FROM Products INNER JOIN (ItemsTable INNER JOIN [Inventory Transactions] ON ItemsTable.Items = [Inventory Transactions].Species) ON Products.ProductID = [Inventory Transactions].ProductID
GROUP BY Products.ProductName, ItemsTable.Items, [Inventory Transactions].TransactionDate, [Inventory Transactions].TransactionDescription;

 

[alansidman]

What happens when you change the Distinctrow to a select query, ie. drop Distinctrow from the query.

Alan

 

[deke]

What happens when you change the Distinctrow to a select query, ie. drop Distinctrow from the query.

Alan

I am not sure how to do this. I just usually start with the Wizard, then select what I think I need and go from there.
Would it be easier to try and do this from the reports side? Also, if some of the values (UnitPrice) are not filled yet would this affect the outcome?
Thanx

 

[alansidman]

I am not sure how to do this. I just usually start with the Wizard, then select what I think I need and go from there.

While in SQL, just remove the Distinct Row.

Would it be easier to try and do this from the reports side? Also, if some of the values (UnitPrice) are not filled yet would this affect the outcome?
Thanx

If there are null values, then Access doesn't know what to do in a calculation. You will have to add the NZ function to the calculation to tell Access what to do if the field it is calculating on is not populated.

Here is a link to look at how to use the NZ function
http://www.techonthenet.com/access/functions/advanced/nz.php

Alan

 

[deke]

While in SQL, just remove the Distinct Row.



If there are null values, then Access doesn't know what to do in a calculation. You will have to add the NZ function to the calculation to tell Access what to do if the field it is calculating on is not populated.

Here is a link to look at how to use the NZ function
http://www.techonthenet.com/access/functions/advanced/nz.php

Alan

Ok, this is what I did. Instead of summing/avg on the query, I simplified it to UnitPrice and UnitsSold.
Then I put the expression Expr1: (Products.UnitPrice)*[UnitsSold] as it seems that UnitPrice is on 2 of the tables
Then I was able to add the values in my Report. As for the Null value, for now I just put in a value and will research the null value thing on the weekend. (I did not want my head to explode!)
Thank you very much Alan.