Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Total Days increase each day (2002)

    Hi,

    I need a formula that will allow me show the number of days a database has been active each time the user opens it. They will have a date that they go live [EffectiveDate] which will be in short date format. I need to be able to show the total days open based on the current date. So, if the database has been open 3+ years it would show 1095+ days. At this point accounting for Leap Year hasn't been requested but if there is a way to build it in I might as well do it now.

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total Days increase each day (2002)

    Dates are stored as a number of days (since December 30, 1899 to be precise). So you can calculate the number of days between two dates simply by subtracting them. The expression for the number of days since the date the database went live is Date()-[EffectiveDate]. In a query that includes EffectiveDate, you can use that expression. If you have to retrieve EffectiveDate from another table, you can use DLookup to get it.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total Days increase each day (2002)

    Hi Hans,

    This worked great! I did use Dlookup as the date is stored in another table so that suggestion was a huge help. While I'm on this thread, the user is going to want similar information based on the length of time that an account is active (has to do with royalties fees etc.) I have a table that stores the [account name] and the [start date]. There will be numerous accounts in this table. Is it possible to use dlookup for a specific [account name] and then do the calculation of now()-[start date]. I tried a variety of syntax but kept getting errors.

    Thanks,
    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total Days increase each day (2002)

    DLookup has an optional third argument to specify a where-condition.

    =Date() - DLookup("Start Date", "tblSomething", "[Account Name] = " & Chr(34) & [Account Name] & Chr(34))

    The Chr(34)s place double quotes around the value of [Account Name] (I have assumed that it is a text value).

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total Days increase each day (2002)

    Hi Hans,

    Great assumption, [Account Name] is a test field.

    Now for what I'm sure is a no brainer but I don't get! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I'm still in the creation/thinking end of this one and so I'd rather have you point me in the right direction insread of having to correct my mess <img src=/S/angel.gif border=0 alt=angel width=15 height=21> How do I use the code or indicate which [Account Name] I need the number of days for? If the user has a form and enters the [account name] into an undbound field [txtAccountName] and I put the code behind a command button to run for the [account name] in [txtaccountname] ------------- is that even feasible?

    Thanks,
    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total Days increase each day (2002)

    If possible, I would use a combo box from which the user can select one of the available account names. That way you avoid the spelling errors that are likely to occur if you let the user type in the account name. Say you have a combo box named cboAccountName, then the where-condition part of the DLookup would become

    "[Account Name] = " & Chr(34) & cboAccountName & Chr(34)

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total Days increase each day (2002)

    Great idea on the combo box to cut down on typos! Thanks!

    I put his behind a command button but am getting an error message stating there is a syntax error. Needless to say I haven't a clue!

    Me.txtDaysOpen = Date - DLookup("Start Date", "tblAccountName", "[Account Name]= " & Chr(34) & cboAccountName & Chr(34)

    [txtdaysopen] refers to the unbound textbox control.

    Thanks,
    Leesha

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total Days increase each day (2002)

    You need another closing parenthesis ) at the end. The one you have is from Chr(34), the one you need from DLookup(...)

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total Days increase each day (2002)

    Thanks for the explanation!! I put in the ) and got another error message but this one at least pointed me to "start date". I was about to send it back to you for input and said "no, think like Hans". I put [ ] around "start date" and it worked!!!! Someday I'll get all thes [ ] "",, etc and where they go!

    Thanks again,
    Alicia

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total Days increase each day (2002)

    Good for you!

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total Days increase each day (2002)

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> More often than not these days I "think like Hans" or Francois and I'm getting it. It does help though if the error message gives me some sort of clue, otherwise I'm clueless as to where to start!

    Thanks again for all the help and patience,
    Leesha

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Total Days increase each day (2002)

    It's a lot easier with the formulas, poring through code is torture! (I find myself doing the same thing...asking myself "What would Hans do?", or, for a better example, see <post#=513,804>post 513,804</post: >) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Total Days increase each day (2002)

    Thanks! Will take a look. It's nice to know I'm not the only one trying to think like Han's. I have days where I go t o hit send and my finger won't touch the key because my head and saying STOP!!! Give it one more shot !!! So then I go back to the drawing board and try to think what would he do! Too funny!
    Thanks again,
    Leesha

Posting Permissions

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