Retrieve value from form for query

[Magster]

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!

 

[Guus2005]

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