VBA to query Access DB

[u742884]

I have written/recorded code to pull selected fields from an Access table based on two criteria: BuyerNo and CalYear. This code works fine to pull for one BuyerNo where CalYear = 2006. It is pulling in a row for each month of the year.

"SELECT tblBuyerPlans.BUYERNO, tblBuyerPlans.DEPTNO, tblBuyerPlans.CLASS, tblBuyerPlans.CALYEAR, tblBuyerPlans.MONTHNAME, tblBuyerPlans.MONTHNO, tblBuyerPlans.BOMSTOCK, tblBuyerPlans.SALES, tblBuyerPla" _
, _
"ns.MD, tblBuyerPlans.MDPCT, tblBuyerPlans.NETREC, tblBuyerPlans.INVADJUST, tblBuyerPlans.EOMSTOCK, tblBuyerPlans.AVGSTOCKYT, tblBuyerPlans.TURNYTD" & Chr(13) & "" & Chr(10) & "FROM `" & MyPath & "Plan" _
, "`.tblBuyerPlans tblBuyerPlans" & Chr(13) & "" & Chr(10) & "WHERE (tblBuyerPlans.BUYERNO=" & Buyer2Query & ") AND (tblBuyerPlans.CALYEAR=" & Year2Query & ")")

Problem is that we use a fiscal year running from Feb to Jan. Jan 2007 is part of fiscal 2006, but its CalYear is 2007. MonthNo for Jan is 12. I need to change this part of code:
WHERE (tblBuyerPlans.BUYERNO=" & Buyer2Query & ") AND (tblBuyerPlans.CALYEAR=" & Year2Query & ")

to something like:
WHERE (((tblBuyerPlans.BUYERNO=" & Buyer2Query & ") AND (tblBuyerPlans.CALYEAR=" & Year2Query & ")) OR ((tbllBuyerPlans.BUYERNO=" & Buyer2Query & ") AND (tblBuyerPlans.CALYEAR=" & Year2Query+1 & ") AND tblBuyerPlans.MONTHNO=12)))

but I cannot seem to get the syntax correct for the query to run. Any ideas?

 

[SydneyGeek]

It's possible to transform the data using SQL but you may be better off going into the db and creating a query where you define Fiscal Year and Fiscal Month.
Then pull that through to Excel and your code will be much easier to work with.

Denis