Where statement Question

[Trevor G]

I am building a query from form controls, where a user will select something from a list box and then also check boxes. What I am struggling with is that I also need to make sure they always get last years selected data. I have been testing some sample VBA to write the Where statement and it works Ok in creating the query but when I try and incorporate this into the actual VBA I want to use it seems to fall over.

The following code works as a straight forward Query Builder

Sub createQuery32()
Dim strSQL As String
Dim strSelect1 As String
strSelect1 = "SELECT * FROM TempImportedOld " & _
" WHERE TempImportedOld.ThisYear = Year(Date())-1"
strSQL = strSelect1
CurrentDb.QueryDefs("qryReportGenerator").SQL = strSQL
DoCmd.OpenQuery "qryReportGenerator"
End Sub

This is the extract of the code I want to use, but where every I try to add the WHERE statement above it doesn't work.

Dim frm As Form, ctl As Control
Dim i As Long
Dim LngCnt As Long
Dim dummy As Variant
Dim strSQL As String
Dim strSQL1 As String
Dim dB As Database
Dim qry As QueryDef
Dim varItm As Variant
Dim strparam As String
Dim strfrom As String

strSQL = "SELECT [tempImportedOld].Product1,[tempImportedOld].ThisYear"
strfrom = " FROM [tempImportedOld]"
strparam = " WHERE "

Set frm = Forms!frmReportCreator
Set ctl = frm!ltsProduct1

For Each varItm In ctl.ItemsSelected
strparam = strparam & "[tempImportedOld].Product1=" & "'" & ctl.ItemData(varItm) & "'" & " OR "
Next varItm


strparam = Left(strparam, Len(strparam) - 4)
On Error GoTo start

Do While i = 0
LngCnt = LngCnt + 1
dummy = Me.Controls.Item(LngCnt).Name
Loop
start:
For i = 1 To LngCnt - 1
If UCase(Left(Me.Controls.Item(i).Name, 3)) = "chk" Then
If Me.Controls.Item(i).Value = True Then
strSQL = strSQL & ", " & Right(Me.Controls.Item(i).Name, Len(Me.Controls.Item(i).Name) - 3)
End If
End If
Next i
strSQL = strSQL & strfrom & strparam

Set dB = CurrentDb

On Error Resume Next
CurrentDb.QueryDefs("qryReportGenerator").SQL = strSQL
If Err > 0 Then
MsgBox ("You must select at least one product")
Exit Sub
End If
DoCmd.OpenQuery "qryReportGenerator"

 

[bob fitz]

Have you tried stepping through the code while it's running to see where it "falls over"?
Have you tried using Debug.Print to see the SQL string that you are building?