Selected criteria not appearing on query/report

Help! After countless attempts to fix this, I'm at dire straits.

I have a form to print reports with the following options in combo boxes, with the last being a Multi-select List:

Type of Report
Fiscal Year
Category
Budget Line (BL)

The selected options are unbound and are populated in SQL based on the options selected sequentially on the form. Upon previewing the report, all criteria is selected with success except for the Fiscal Year (FY). The information extracted for the FY, the Category and the BL come from tblRCN. When the database is launched, it will select FY criteria based on what logged user last selected, reflected in the SQL Statement below for drop-down for FY:

SELECT tblRCN.FY FROM tblRCN GROUP BY tblRCN.FY HAVING (((tblRCN.FY)>=StartFYFunction()));

Don't know if the multi-select list is the problem, but here is what I have:

Dim db As DAO.Database
Dim qdr As DAO.QueryDef
Dim varItem As Variant
Dim StrCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
For Each varItem In Me!TempRCN.ItemsSelected
StrCriteria = StrCriteria & ",'" & Me!TempRCN.ItemData(varItem) & "'"
Next varItem
If Len(StrCriteria) = 0 Then
MsgBox "Select at least one item from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
StrCriteria = Right(StrCriteria, Len(StrCriteria) - 1)
strSQL = "SELECT * FROM qryforMultiSelect " & _
"WHERE qryforMultiSelect.RCN IN(" & StrCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryMultiSelect"
Set db = Nothing
Set qdf = Nothing

Thanks in advance to anyone who can help me with this.

Tish

Hi Tish,

Is StartFYFunction() a function that is called when you open your form? Don't quite understand the syntax for that sql statement and where this sql is used. Is it the recordsource for your FY combobox? Something doesn't appear correct with this sql.

The code for your QueryDef is showing "qryMultiSelect" and the sql for this query is all records from another query "qryforMultiSelect" with a WHERE condition. Was this correct and does this query work correctly. I didn't see the relationship between the Select Query at the beginning of your post for your Fiscal Year and this section of your code. There probably is, but couldn't put it together visually. I gather that qryforMultiSelect has the Fiscal Year as part of your selection criteria but wasn't sure.

lwells

The StartFYFunction() is a function that is called when the database is first opened and when the form is opened. It is the recordsource for the FY combobox, recalling the range of FYs the user last worked with based on their login which reduces the amount of records viewed (currently has about 10 yrs of data). The qryMultiSelect combines data from two separate tables (due to one to many relationships) and qryforMultiSelect only uses the Budget Line (in the DB as RCN) as its criteria, which carries over into the qryMultiSelect. Is there something missing/wrong or do you see a work-around on this? Thanks.

Hi Tish,

Then where is the FY in the criteria? If the qryMultiSelect is from two tables and the qryforMultiSelect only uses the Budget Line...where exactly have you entered the criteria for the FY. I don't see it in the sql criteria in the code.

strSQL = "SELECT * FROM qryforMultiSelect " & _
"WHERE qryforMultiSelect.RCN IN(" & StrCriteria & ");"
qdf.SQL = strSQL

Example:
strSQL = "SELECT * FROM qryforMultiSelect " & _
"WHERE qryforMultiSelect.RCN IN(" & StrCriteria & ") AND [Fiscal Year] = [FYComboBox] ;"

Above syntax in example not correct, but you get what I am asking.

Just can't figure out where you have put the criteria for the FY.

lwells

You're correct. I've inserted something similar into the where clause and it worked out just fine.

Thanks for your help!