Access - VBA Code problem Date- problem

Question

Hey all, have managed to finish the database; but have run into a problem with something I did in the beginning. Basically, the calculations in the database are done using VBA code. Now I've run into a problem where instead of slecting just the last two years worth of data, it is now slecting and using data from more than two years ago. The data it should pick is from the Historical_Stock_Data table and the Historical_Currency table.

Now the reason I need to set the two year limit is for the index that we use at work which does some calculations based on just two years worth of data. I won't put all the code here because it might be too much, but if you want I can, I'll put the SQL part of the code here which SELECTs the tables to put into the Recordset.

    Set rst = db.OpenRecordset("SELECT Location.ID, Location.Location, CompanyLocation.StockCode, Company_Information.CompanyName, Company_Information.NOSH, " & _
" Company_Information.[CEP NAV], Company_Information.CompanyDescription, Company_Information.CurrencyCode, Historical_Stock_Data.StockCode, Historical_Stock_Data.Dates, " & _
" Historical_Stock_Data.SharePrice, Historical_Stock_Data.Volume, Historical_Currency.Rates " & _
" FROM Location RIGHT JOIN (((Historical_Currency RIGHT JOIN Company_Information ON Historical_Currency.CurrencyCode = Company_Information.CurrencyCode) " & _
" RIGHT JOIN CompanyLocation ON Company_Information.StockCode = CompanyLocation.StockCode) LEFT JOIN Historical_Stock_Data ON " & _
" Company_Information.StockCode = Historical_Stock_Data.StockCode) ON Location.ID = CompanyLocation.LocationID " & _
" where CompanyLocation.StockCode='" & strStockcode & "' AND ((Historical_Stock_Data.Dates)=[Historical_Currency].[Dates]) AND (Historical_Stock_Data.Dates) >" & Date & " - 730 " & _
" ORDER BY Historical_Stock_Data.SharePrice")

Now the thing is I tried using the DateAdd function as well but again no luck; I tried placing the SELCT query in Access and it still picks more than two years worth of data; so I'm not really sure what to do. The reason this is important is because as they start using it daily; the index has to change and the 24 month High and Low values will have to change as well, so it needs to work. Funny thing is, it was actually working before, but I can't seem to figure out what I added to the database or what happened that made it not work.

I can paste the full VBA code here if it'll help out more.

Answer 1

Date in that format will not work unless you include both delimiters and format it unambiguously. So:

(Historical_Stock_Data.Dates) >#" & Format(Date - 730, "yyyy/mm/dd") "# ... 

However, I see no reason why you should not use:

 Historical_Stock_Data.Dates > DateAdd(""yyyy"",-2,Date())

EDIT

" where CompanyLocation.StockCode='" & strStockcode & _
"' AND Historical_Stock_Data.Dates=[Historical_Currency].[Dates] " & _
" AND Historical_Stock_Data.Dates > DateAdd(""yyyy"",-2,Date()) " & _