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]

Calculations do not belong in tables. That is spreadsheet thinking and not database thinking. Calculations can be done in queries and you can output the data and it will look like a table. In your query, add an additional column (filed). Place your expression (formula) in the first line of the field. Use the expression builder, if you are unsure of the syntax. It will guide you correctly.

Alan

 

[Will_B]

If you simply need to display the result of this date calculation in a report or form, you can create a SELECT query and insert an "immediate-if statement" (IIF). Here's how...

Let's say your "further date" field is called "Further_Date" and your original date field is called "My_Date". Create a query with your table(s) and in one of the columns in the query design grid, put this formula (changing the field names appropriately):

IIF([Further_Date]>0,[Further_Date]-[My_Date],Date()-[My_Date])

Translated, this says if some date was entered in the Further_Date field, subtract My_Date from Further_Date and display the result; otherwise, subtract My_Date from today's date. Everything before the first comma is criteria, what comes after it is what to do if the criteria is TRUE, and what comes after the second comma is what to do if the criteria is FALSE. Date() is Access' command for getting today's date.

This won't change any data in your table(s), but you can use the SELECT query in a report or to display the result on a form. There is a way to use it in a special query that changes data in tables, but you'll need some formal Access classroom instruction before you attempt that (you can easily destroy your data).

This formula should work for display and reporting purposes so long as Further_Date and My_Date are of data type "Date" in the table. If you get an error message, post it here.

In your query, this formula will be titled "Expr1:". Change that title to whatever you like, but you must leave the colon : at the end.

 

[deke]

Hello. I am sort of stuck on the same problem. If I have a table with UnitsSold and another with UnitPrice and in my query I have the Sum of UnitsSold and Avg of UnitPrice can I build [Avg Of UnitPrice]*[Sum Of UnitSold] ?

 

[alansidman]

In your query, create a new field. In the field name put the formula =[Avg Of UnitPrice]*[Sum Of UnitSold] . Access will put Expr1: in the field name in front of the formula and drop the = sign. This should get what you are looking for.
Alan

 

[deke]

In your query, create a new field. In the field name put the formula =[Avg Of UnitPrice]*[Sum Of UnitSold] . Access will put Expr1: in the field name in front of the formula and drop the = sign. This should get what you are looking for.
Alan

Ok, do I need to set the parameters? I get a column but no values in it.
Thanx

 

[alansidman]

Are [Sum of UnitsSold] and [Avg of UnitPrice] both fields in your query?

 

[deke]

Yes. I tried modifying the query a bit. Thought maybe if I kept each as an individual line, then maybe doing something on the report side.

Btw, Red Sox bullpen looks incredible this year!

 

[alansidman]

Will you post your SQL statement for your query so that we can better understand what it is that you are doing and find out what is not right.

Alan

 

[deke]

Is this what you mean by SQL statement?

SELECT Products.ProductName, ItemsTable.Items, Products.UnitPrice, [Inventory Transactions].TransactionDate, [Inventory Transactions].TransactionDescription, [Inventory Transactions].UnitsSold, [Inventory Transactions].UnitsShrinkage
FROM Products INNER JOIN (ItemsTable INNER JOIN [Inventory Transactions] ON ItemsTable.Items = [Inventory Transactions].Species) ON Products.ProductID = [Inventory Transactions].ProductID;