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

    Using Datediff (2002)

    Hi,

    I'm trying to determine the number of days between two dates and am using the following formula in a query:

    DaysOpen: DateDiff("d",[todaydate],[soc_dt])

    The number being returned doesn't seem to take into consideration the year. For example, when calculating the number of days between
    6/6/2004 and 3/1/2005, the number returned is 6. I've tried reversing the position of [todaydate] and [soc_dt] but the number returned is still wrong. What am I missing in the formula?

    Thanks,
    Leesha

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Datediff (2002)

    If I try
    Print datediff("d",#6/6/2004#,#3/1/2005#)
    in the immediate window I get 268.

    If I use the following SQL statement in a query :
    SELECT Table1.Field1, Table1.Field2, DateDiff("d",[field1],[field2]) AS Expr1 FROM Table1;
    I get the following result (see attachment)
    Are you sure about your dates in your fields ?
    Francois

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

    Re: Using Datediff (2002)

    Hi Francois,

    I've double checked and both fields are formatted as short dates. The only difference I can see between your example and my query is that yours pulls data directly from a table and mine is pulling the information from another query. When I duplicated it from the table directly, it produced the right number of days. Is the fact that I was using a query to build the query vs a table a factor?

    Leesha

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Datediff (2002)

    Not that you are using a query, but the fact that you use the format function. The format function returns a string (text) and not a date.
    Francois

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

    Re: Using Datediff (2002)

    Thanks for the info how the data is returned. I had no idea (obviously). Please bear with me. I took off the short date format for each field in the query and still the answer is wrong. I also looked to see the the original query does not have a short date format. To confuse myself more, I gave the two fields a short date format in the query that is based on the table and that still returns the correct number.

    Leesha

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Datediff (2002)

    You've lost me. Do you have your correct number or not ?
    Francois

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

    Re: Using Datediff (2002)

    Sorry. I have the correct number in query based on the table but not the original query. I'll keep playing with it.

    Thanks,
    Leesha

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Datediff (2002)

    If you don't find it, you can still attach a sample db of what you have. We'll have a look at it.
    Francois

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

    Re: Using Datediff (2002)

    One way to get around the problem in your original is to wrap each date in the DateDiff expression in a CDate() function, which will convert a date-formatted string back to a real date.

    DateDiff("d", CDate([date1]), CDate([date2]))
    Charlotte

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

    Re: Using Datediff (2002)

    Thanks Charlotte! That did it!

    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
  •