filter date from Query pass down to report

[geraldk]

Hello Everyone,

Here is my problem/issue. I have Report that I trying to find who is expire for certifications here.



This is my query:

SELECT tblEmployee.EmpID, tblEmployee.EmpLastName, tblEmployee.EmpFirstName, tblNameCert.NameCertType, tblCertification.CertDateTaken, tblCertification.CertExpire, tblEmployee.ACTID, DateDiff("d",Now(),[CertExpire]) AS Expire
FROM tblNameCert INNER JOIN (tblEmployee INNER JOIN tblCertification ON tblEmployee.EmpID = tblCertification.EmpID) ON tblNameCert.NameCertID = tblCertification.NameCertID
GROUP BY tblEmployee.EmpID, tblEmployee.EmpLastName, tblEmployee.EmpFirstName, tblNameCert.NameCertType, tblCertification.CertDateTaken, tblCertification.CertExpire, tblEmployee.ACTID, DateDiff("d",Now(),[CertExpire])
HAVING (((tblCertification.CertExpire) Is Not Null) AND ((tblEmployee.ACTID)<>6))
ORDER BY tblCertification.CertExpire;

When I am at my form I want to be able to display at my report I want to display 30, 60, 90 days when the certifications will expire. Since my column I am looking at for that I doing my calculations is not in the table I can pass do a where claus down to the report. In the CertExpire column we can have this field blank, that is why I am not interested in null values.

In my form code I have this:


Dim stDocName As String
Dim stLinkCriteria As String
Dim POSValue As Integer
Dim strLinkPos As String

If Me.frmDates = 1 Then

stLinkCriteria = "[CertExpire]=<0"

ElseIf Me.frmDates = 2 Then

stLinkCriteria = "[CertExpire]=<30"

ElseIf Me.frmDates = 3 Then

stLinkCriteria = "Expire=<60"

ElseIf Me.frmDates = 4 Then
'if user choose 90 days to expire for certifications


'I have try this one
'stLinkCriteria = "[tblCertification.CertExpire]=((DateDiff('d',Now(),[tblCertification.CertExpire]))<=90))"


stLinkCriteria = "SELECT tblEmployee.EmpID, tblEmployee.EmpLastName, tblEmployee.EmpFirstName, " _
& "tblNameCert.NameCertType, tblCertification.CertDateTaken, tblCertification.CertExpire, " _
& " tblEmployee.ACTID, DateDiff('d',Now(),[CertExpire]) AS Expire " _
& "FROM tblNameCert INNER JOIN (tblEmployee INNER JOIN tblCertification ON tblEmployee.EmpID = tblCertification.EmpID)" _
& "ON tblNameCert.NameCertID = tblCertification.NameCertID " _
& "WHERE (((tblCertification.CertExpire) Is Not Null) And ((tblEmployee.ACTID) <> 6) And ((DateDiff('d', Now(), " _
& "[CertExpire])) <= 90))" _
& "ORDER BY tblCertification.CertExpire;"


if I run this SQL in the query window I get my resolves that I wanted.



I have also include my tables here.

[geraldk]

Found a solution, used an openargs statement and works like a clarm.