Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    12

    Unanswered: DateDiff behaves very strange

    Sorry to bother you again guys, but this is something I just dont get.

    I have a table BaseSal, giving a Empl_ID, Salary and a month.

    Query to retrieve the Salary for a empl and a month:

    INSERT INTO tmpEnvelope SELECT *FROM
    (SELECT EmplStatic.Empl_ID AS Empl_ID, EmplStatic.NameFld AS NameFld, BS.NumberFld AS BaseSal, DC.NumberFld AS DiscCons
    FROM (EmplStatic LEFT JOIN [SELECT Top 2 BaseSal.Empl_ID, BaseSal.MonthFld, BaseSal.NumberFld FROM BaseSal
    WHERE ((datediff("D",BaseSal.MonthFld, #12-02-2009# )>0)) ORDER BY BaseSal.MonthFld DESC]. AS BS ON EmplStatic.Empl_ID = BS.Empl_ID) LEFT JOIN
    [SELECT DiscountConcession.Empl_ID, DiscountConcession.MonthFld,DiscountConcession.Num berFld FROM DiscountConcession WHERE DiscountConcession.MonthFld BETWEEN #3-01-2010# AND #3-31-2010# ]. AS DC ON EmplStatic.Empl_ID = DC.Empl_ID
    WHERE ((EmplStatic.Empl_ID) =104));

    Its about the bold statement.
    Last Salary date for empl#104 is 15nov09. If I use #12/1/2009#, the query, does find the Salary. If I use #12/2/2009# ( 1 day later ) it does not find it. I took a query to calculate the differences and it works fine. I tested this on various dates, of course, Salary date of 1/1/2008 is found by 15 nov 2009, not by 16 nov 2009.

    I used datediff, but > gives the same starnge results.

    Any ideas?

    Many thans,

    Arnold

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If that part of the query is in question I would do some testing in the Immediate window of the Visual Basic editor. You can type:

    ?datediff("D",BaseSal.MonthFld, #12-02-2009# )

    Substitute BaseSal.MonthFld for several values to see how that function gets evaluated. What kind of data is in the MonthFld? Is it a date? Or text? If Text then make sure to enter the first date as a string and see how it gets converted to a date.

    For example:

    ?datediff("D","15nov09", #12-02-2009# )

  3. #3
    Join Date
    Apr 2009
    Posts
    12
    Hi DC,

    ?datediff("D",#09-11-2009#, #12-02-2010# )
    447

    Looks good, for different values

    BaseSal.MonthFld is indeed a Date/Time type

    Arnold

Posting Permissions

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