Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2014

    Question 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.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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

Posting Permissions

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