Thread: Using 'Date()' in a calculation
04-15-14, 21:44 #1Registered User
- Join Date
- Apr 2014
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.
04-16-14, 01:06 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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.
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.I'd rather be riding on the Tiger 800 or the Norton