Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DateDiff function problem (Access 97)

    I am using this function to calculate a patient's length of stay in hospital:
    =DateDiff("d",[Admission Date],[Discharge Date])
    It works perfectly for some records, not for others - roughly speaking , for the first 3 years of data the field shows the correct value, the recent years show a blank value for that field.
    Any ideas on how to fix it?
    Thanks

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

    Re: DateDiff function problem (Access 97)

    Not sure what you mean by recent years, but if it means 2000, 2001 or 2002 is it possible that you are missing some of the Y2K fixes for Access 97. As I recall there were some know issues with certain date calculations and Access 97 that were resolved either with a service pack or with patches. The only other thing that comes to mind is what happens if you don't have a Discharge Date - you obviously can't calculate a meaningful length of stay in that case.
    Wendell

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DateDiff function problem (Access 97)

    Remembering that dates are just numbers, what happens if you just subtract one date from the other.

    Lengthof Stay = [Discharge Date]-[AdmissionDate]

    if either of the dates could have a time component attached you could strip that out first if needed.
    Lengthof Stay = Date([Discharge Date])-Date([AdmissionDate])

    I don't know whether this will be any better than your original, but it is worth a try.
    Regards
    John



  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DateDiff function problem (Access 97)

    As Wendell suggests, you may be bitten by the Y2K bug. The sliding window for dates changed between versions, and unless you are passing 4 digit years into your datediff expression, it may be interpreting discharge date as being earlier than the admission date.
    Charlotte

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DateDiff function problem (Access 97)

    Mary

    I have not tested this idea but I had a similar problem with a database I was creating using date functions. How about trying to format the dates as dd/mm/yyyy or mm/dd/yyyy like follows:

    =DateDiff("d",Format(Admission Date, "dd/mm/yyyy"),Format(Discharge Date, "dd/mm/yyyy"))

    As I say not tested but worth a try.
    Jerry

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateDiff function problem (Access 97)

    Thanks for all the ideas. I'll check them out when I am back at work next week and let you know how it goes. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateDiff function problem (Access 97)

    I now realize my problem is somewhat different to what I thought. In fact the correct Length of Stay is being calculated and displayed on the form, the value is just not being stored with the table, nor is it available to any queries being done. The value was being stored properly before 2 March 2001.
    Any suggestions to fix this would be most welcome. <img src=/S/please.gif border=0 alt=please width=31 height=23>

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DateDiff function problem (Access 97)

    If you have the admission date and the discharge date, why do you need to store length of stay? It can be recalculated when you need it, and the general rule of thumb is that you don't store data that can be calculated unless you have a *really* good reason for it ... such as storing a vale in a temporary table to speed up report printing.

    Edited immediately

    Sorry, I forgot to answer your question. If it worked before and doesn't now, then you changed something. If you were using code or a macro to write the value, your code or macro changed or maybe you changed something about the control on the form. Calculated controls (those with a formula in their control source) can't be bound to a field, so to store the value, you would have to use a bound control and calculate the value in code, then insert that value into the field by setting the control's value in code.
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DateDiff function problem (Access 97)

    Thanks, Charlotte, most helpful.

Posting Permissions

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