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.
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.