Reusing SQL Select Statement

[VbaBob]

Is there a way to reuse a SQL criteria string on the corresponding ADO recordset?

I have created a query for all employees who have either a 10, 20, 25, 30, or 40 year anniversary in the calendar year. I created seperate SQL criteria strings for each of the anniversary dates which I incorporated into my SQL Select statement. I have succesfully created an ADO recordset of all the above employees.

My problem is when I go to compile the report (using Excel 2003), I only want to show those anniversary dates in the corresponding columns e.g. 10, 20, 25, 30 or 40.

If I already have a SQL criteria string for each of the anniveraries, is there a way to say..."If sCriteria10Yr is TRUE then print anniversary date in this column (the 10 year anniversary column) otherwise leave blank".

Nor sure if you can reuse a criteria string within an If/Then in VBA.

I appreciate any insight anyone has to offer.
Thanks

[westconn1]

Nor sure if you can reuse a criteria string within an If/Then in VBA.

of course, but may be slow

you may be able to filter the recordset for each annivesary and process in turn

[VbaBob]

WestConn1, thanks for the reply.

If it can be done, how would it be done in vba?
My current code does not want to cooperate e.g. "If sCriteria10Yr then"
As far as speed is concerened, the If/Then will be performed on the existing ADO Recordset, so the speed issue should be unnoticeable to the naked eye.

Thanks again for all your help.

[westconn1]

what is your complete sql query to produce the recordset?
what is the content of scriteria10yr?
is it a boolean, date or other?

[VbaBob]

rsEmp.Open "SELECT * FROM UPEMPL WHERE " + sEmpCriteria, cnEmp, adOpenStatic, adLockBatchOptimistic

sEmpCriteria = (CLASS1='S1') 
AND (STATUS=1) 
AND (((substring(cast(HIREDATE as CHAR(8)),5,2)>='01') 
AND (substring(cast(HIREDATE as CHAR(8)),5,2)<='12') 
AND (substring(cast(HIREDATE as CHAR(8)),7,2)>='01') 
AND (substring(cast(HIREDATE as CHAR(8)),7,2)<='31') 
AND (substring(cast(HIREDATE as CHAR(8)),1,4)+10>='2011') 
AND (substring(cast(HIREDATE as CHAR(8)),1,4)+10<='2011')) 
    OR ((substring(cast(HIREDATE as CHAR(8)),5,2)>='01') 
AND (substring(cast(HIREDATE as CHAR(8)),5,2)<='12') 
AND (substring(cast(HIREDATE as CHAR(8)),7,2)>='01') 
AND (substring(cast(HIREDATE as CHAR(8)),7,2)<='31') 
AND (substring(cast(HIREDATE as CHAR(8)),1,4)+20>='2011') 
AND (substring(cast(HIREDATE as CHAR(8)),1,4)+20<='2011')))

sEmpCriteria is a concatenation of sCriteria1 through sCriteria8 representing all the various possiblities (user report selections).

sCriteria4 = (substring(cast(HIREDATE as CHAR(8)),5,2)>='01') 
AND (substring(cast(HIREDATE as CHAR(8)),5,2)<='12') 
AND (substring(cast(HIREDATE as CHAR(8)),7,2)>='01') 
AND (substring(cast(HIREDATE as CHAR(8)),7,2)<='31') 
AND (substring(cast(HIREDATE as CHAR(8)),1,4)+10>='2011') 
AND (substring(cast(HIREDATE as CHAR(8)),1,4)+10<='2011')

sCriteria4 represents 10 year anniversary criteria (hence the "+10"). I want to use this same criteria (sCriteria4) in my VBA code to decide whether or not to insert the 10 year anniversary date into the 10 Year Column on my report. My code is simply.."If sCriteria4 then (insert date into column) Else (leave balnk)...", but it doesn't want to cooperate.

I would repeat the same process for each of the anniversary dates only inserting those dates which meet the criteria e.g. sCriteria4, sCriteria5, ...etc.

I appreciate your help.
Thanks