Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DateValue calculations (Access97/SR2)

    There has to be a better way .... (Access 97/SR2)a


    <pre>Me.tbAge = Now() - DateValue(Format(Val(Me!LASTMOD), "####/##/##"))
    </pre>



    The database form has a text field LASTMOD. For better or worse, this IS a text field, 8 character digits, by convention arranged as YYYYMMDD. I can't change that. It is placed there by another application to represent the date Last Modified.

    The database form has a text field tbAge which is to represent the Age of the record, the staleness, if you will, being the difference between today and the date the record was last modifed.


    The code fragment above works, but it looks awfully cumbersome to me.


    Can anyone provide a more terse method of obtaining a date difference as an integer?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,496
    Thanks
    3
    Thanked 42 Times in 42 Posts

    Re: DateValue calculations (Access97/SR2)

    Not as long as you store the date as a text string. If it's stored as a date or converted to an internal date structure (as you have done) then the DateDiff function can do all sorts of interval calculations. There is another way to convert the date, using
    <font color=blue>DateSerial(Left(MeLASTMOD,4),Mid(Me!LAS TMOD,5,2),Right(Me!LASTMOD,2))</font color=blue>
    You may view that is being just as cumbersome however.
    Wendell

  4. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: DateValue calculations (Access97/SR2)

    Any chance

    M.tbAge = DateDiff("d", Date, CDate(strDateVar))

    (which returns the number of days) or some variation can help?
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  5. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateValue calculations (Access97/SR2)

    > M.tbAge = DateDiff("d", Date, CDate(strDateVar))

    Yes, although my string data id a hurdle - the CDate requires a formatted date, as in "2003/01/23" or similar, whereas my date is an unformatted string "20010123".

  6. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateValue calculations (Access97/SR2)

    It seems to me that Wendell's calculation will work all the time, whereas using the string "####/##/##" might run into problems whenever the day is <= 12. I believe Format relies on the machine Locale?

  7. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateValue calculations (Access97/SR2)

    > the string "####/##/##" might run into problems

    I quite agree. As a general solution, sticking to date-consistent formats is better.

    In this particular case I am stuck with an existing 8-digit string formatted as YYYYMMDD. If the client has screwed up in creating the data, that is going to be another problem.

    It is migration of an old DOS-based flat-file to (reletaive to me!) a modern form in Access97/SR2.

  8. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: DateValue calculations (Access97/SR2)

    Anecdotally, I thought that "YYYYMMDD" is an ISO 8601 standard date format (most significant digits first kind of logic) so most coders will probably need an "ISO date to US Date format" function in their toolkit until MS gets their act together on formats that DATEVALUE will accept. (Looks like ISO wants to charge to even view the standard, here is another Link - Appendix A)
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  9. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateValue calculations (Access97/SR2)

    > Anecdotally,

    I'm not sure what an old copper mine in Montana has to do with it (unless it's because it's near Southern Cross where I grew up in the Yilgarn of Western Australia), but .....

    I was taught by Control Data circa 1969 to use the YYYYMMDD format, as it sorted correctly on computers. I recall too that they stressed a four-digit year, even back in those days of "tight" space. They weren't stupid.


    > until MS gets their act together

    I continue to be puzzled that the USA, world leader in computing etc etc hasn't bitten the bullet and moved to the Y/M/D format in general. As society moves more and more towards information (I'm thinking the last 125 years here), it just makes sense to shift our thinking to a rational form of expression.

  10. #9
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: DateValue calculations (Access97/SR2)

    US insularity and infrastructure cause failures to think globally on a lot of US fronts. Recall the abortive US attempt to convert to the Metric measurement system. But now we are getting off-topic. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

Posting Permissions

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