WHERE clause issues

[Abraxus]

I have a query that currently gets its WHERE clause from the values of 4 text boxes on a form.

Code: Select all

In (Eval("[forms]![frmMain]![cboPeriod1]"),Eval("[forms]![frmMain]![cboPeriod2]"),Eval("[forms]![frmMain]![cboPeriod3]"),Eval("[forms]![frmMain]![cboPeriod4]"))

I am needing more than 4 periods now, so I've built a table of the periods and a True/False of SELECTED to indicate which ones to include (could be 1, could be 100).

I then modified the criteria on my query to pull in the listing of selected periods using this code

Code: Select all

Function GetReportingPeriods() As String
    Dim strToReturn As String
    strToReturn = ""
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rst As DAO.Recordset
    Set rst = db.OpenRecordset("SELECT * FROM tblPeriods WHERE Selected = TRUE", dbOpenForwardOnly)
    While Not rst.EOF
        strToReturn = strToReturn & "'" & rst![Period Description] & "',"
        rst.MoveNext
    Wend
    rst.Close

    Set rst = Nothing
    Set db = Nothing
    strToReturn = Left(strToReturn, Len(strToReturn) - 1)
    GetReportingPeriods = strToReturn
End Function

The criteria looks like this

Code: Select all

In (GetReportingPeriod())

unfortunately, nothing returns in the query.

If I put the actual string that gets returned by GetReportingPeriod, however, it works just fine.

Any ideas?

Thanks!

[agibsonsw]

In (GetReportingPeriod()) - should this be ..Periods() ?

Added: I think you may also be able to write: In(SELECT * FROM tblPeriods WHERE Selected = TRUE) 'or =Yes.

Andy.

[HansV]

It won't work with SELECT * becaus tblPeriods contains more than one field.

Set the Criteria line to

In (SELECT [Period Description] FROM tblPeriods WHERE Selected)

[Abraxus]

Hans,

Your solution works (THANKS!) but I'm curious to know why mine does not...I see no reason why it shouldn't.

[HansV]

Your solution doesn't work because In (...) expects an expression, not a string (even if that string looks like an expression).