# Thread: Using 'Date()' in a calculation

1. Registered User
Join Date
Apr 2014
Posts
1

## Unanswered: Using 'Date()' in a calculation

So I am designing a simple inventory database. A big part of the database is that each order record will have a corresponding 'Dwell Time', which is the difference between the current date and the date that the order was received.

For instance, if an order was received 4/10/14 and the current date is 4/15/14, then the 'Dwell Time' should equal 5. I first tried doing this in a calculated field within the 'Order' table, which is essential the main table. Within the calculated field I typed, 'Date()-[DateReceived]', which gave me a syntax error saying that I couldn't use 'Date()' in a calculated field in a table.

I figured out that I can use 'Date()-[DateReceived]' if I use it in a text box within a form. The problem is, that only displays the Dwell Time instead of saving in in the 'Dwell Time' field within the 'Order' table for that corresponding record.

My question is how can I calculate 'Dwell Time' for each record and save it within the 'Order' table?

I can send you the database if necessary.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Simplest way of handling this is to store the date the order was received and calculate the dwell time as required when you extract the data NOT store the dwell time. This is whats called derived data and should not be stored unless you have good reasons to do so, such reasons could be for performance.

use the relevat dateime function to calculate the dwell time. Offhand I guess that will be datediff.

you can do thus calculation either in a query when you extract the data and ir in a form or report wgen you display the data.

ferinstance:-
SELECT my, column, list, DATEDIFF("d", date(), datereceived) AS DwellTime FROM mytable

Or set the datasource for a control
= iif(isdate(acontrol) , datediff......,"")

The ... after the second date diff us because I cant be bothered to retype the datediff exoression from the first reference.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•