Hi all,

I'd like to retrieve a value from an open form so I can limit the rows in my query.

Here is the query:
SELECT tblProjects.intProjectID FROM tblProjects
WHERE (((tblProjects.ClosedDate)=LimitProjects()))
ORDER BY tblProjects.intFiscalYear, tblProjects.IntProjectNo;

Below is my VBA code:
Public Function LimitProjects() As String
Select Case [Forms]![mfrmReportSelectionforOneProject]![optProjects].Value
Case 1
LimitProjects = vbNullString
Case 2 '
LimitProjects = "Is Null"
Case 3
LimitProjects = "Is Not Null"
End Select
End Function

When I check the value of LimitProjects before exiting the function, the correct value is there - yet the query doesn't return any rows.

When I manually key in Is Null or Is Not Null into the query design, I get the correct rows.

Any ideas why I can't use this scenario?

Thanks!

You scenario doesn't work because when executed the query reads:

SELECT tblProjects.intProjectID FROM tblProjects
WHERE (((tblProjects.ClosedDate)="Is Null"))
ORDER BY tblProjects.intFiscalYear, tblProjects.IntProjectNo;

or

SELECT tblProjects.intProjectID FROM tblProjects
WHERE (((tblProjects.ClosedDate)="Is Not Null"))
ORDER BY tblProjects.intFiscalYear, tblProjects.IntProjectNo;

or

SELECT tblProjects.intProjectID FROM tblProjects
WHERE (((tblProjects.ClosedDate)=""))
ORDER BY tblProjects.intFiscalYear, tblProjects.IntProjectNo;

What you want is probably:

SELECT tblProjects.intProjectID FROM tblProjects
WHERE (((tblProjects.ClosedDate) Is Null))
ORDER BY tblProjects.intFiscalYear, tblProjects.IntProjectNo;

or

SELECT tblProjects.intProjectID FROM tblProjects
WHERE (((tblProjects.ClosedDate) Is Not Null))
ORDER BY tblProjects.intFiscalYear, tblProjects.IntProjectNo;

or

SELECT tblProjects.intProjectID FROM tblProjects
WHERE (((tblProjects.ClosedDate)=""))
ORDER BY tblProjects.intFiscalYear, tblProjects.IntProjectNo;



strSql = "SELECT tblProjects.intProjectID FROM tblProjects " & _
"WHERE (((tblProjects.ClosedDate) " & _
Choose([Forms]![mfrmReportSelectionforOneProject]![optProjects].Value & _
, " = " & vbNullString, "Is Null", "Is Not Null") & ")) " & _
"ORDER BY tblProjects.intFiscalYear, tblProjects.IntProjectNo;"

When you store this query, it will run the way you want.


HTH:D