Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    12

    Unanswered: subtracting dates

    I am trying to subtract [Date Due Back] from [Date Rented] and give me the [Duration] in days.

    Does anyone have any easy VBA to do this?

    EDIT

    I figured that out. But I would like to put the word "days" next to the number. Where about would that go in the little equation?
    Last edited by potatoehead; 04-28-12 at 19:40.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It would be helpful if you posted your solution for others who may have the same problem. To add the text you concatenate the text with the formula:

    YourFormula & " days"
    Paul

  3. #3
    Join Date
    Mar 2012
    Posts
    12
    So i did:
    =DateDiff("d",[date rented],[date due back])

    Now when I add the &" " &"Days"

    &" " &"Days"
    to the end, I can no longer make the price for the next field, which is
    =[duration] * 1.25

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, you would only use the concatenated text for display. With the text in it, you can't make further calculations.
    Paul

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    So for Price you would simply redo your calculation for Duration and multiply by 1.25:

    =(DateDiff("d",[date rented],[date due back])) * 1.25

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you are confusing formatting of data and manipulation of data
    you are creating a string value ie xx days, and then surprised you can't use a string value later on in a calculation
    so the solution is NOT to add " days" to create a string variable, but display the " days" after the variable when used in a report or form.

    separate formatting (what you do to the data when displayed in a form or report) from manipulation (essentially keep the data in the form you need.
    essentially keep
    dates in a datetime variable
    numbers in an appropriate numeric variable
    and so on.
    VB does allow the use of varioant datatypes, but in my view that makes for sloppy thinking and doesn't alwways allow you to take advantage of all of the functions available in VB. if you use the datetime datatype then its easy enough to use the date time functions in VB. ferinstance if you have a multi user applciation then displaying the dates or numbers matters. there is nothing more confusing than displaying dates in only your own format. MS Acess defaults to mm/dd/yyyy which frankly is an abomination, the real world uses iso dates yyyy/mm/dd or dd/mm/yyyy. only in the boonies is mm/dd/yyyy used , some countries / as the date separator, some - and so on. . similarly with number punctuation many European countries use '.' as the thousands separator ',' as the decimal point
    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
  •