I have finlly got my code to work but I am trying to implement the final piece which is AND ((tblacq.Acqdate) Between #" & _
Format(Me.Startdate, "mm/dd/yyyy") & "#" & _
" and #" & Format(Me.EndDate, "mm/dd/yyyy") & "#"

Eveything works fine until I add this in. I have added to controls to the form start date and end date when I select the dates and execute the code I get the following error Run-time error '3075':
Missing).], or Item in query expression '(((Products.productsgroup in ('GEM')) AND ((tblacq.acqdate) Between #10/10/2006# and #12/04/2007#'.

Can anyone help me out here I am at a loss as to why this is happening.

Private Sub Command2_Click()

' Declare variables
   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef
   Dim varItem As Variant
   Dim strCriteria As String
   Dim strSQL As String
' Get the database and stored query
   Set db = CurrentDb()
   Set qdf = db.QueryDefs("Newquery18")
' Loop through the selected items in the list box and build a text string
   For Each varItem In Me!ListFilter.ItemsSelected
       strCriteria = strCriteria & ",'" & Me!ListFilter.ItemData(varItem) & "'"
   Next varItem
' Check that user selected something
   If Len(strCriteria) = 0 Then
       MsgBox "You did not make a selection from the Labels list!" _
           , vbExclamation, "No Labels Found!"
       Exit Sub
   End If
' Remove the leading comma from the string
   strCriteria = right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
 strSQL = "SELECT PRODUCTS.Itemcode, PRODUCTS.DESCRIPTION, PRODUCTS.PRODUCTGROUP, PRODUCTS.RRPRICE, PRODUCTS.SALEPRICE, PRODUCTS.BUYPRICE, PRODUCTS.ManufacturerName, PRODUCTS.Manufacturer, tblmanufacturers.ManufacturerName, tblAcq.AcqDate " & _
"FROM tblAcq.AcqDate, tblmanufacturers INNER JOIN PRODUCTS ON tblmanufacturers.ManufacturerID = PRODUCTS.Manufacturer " & _
"WHERE (((PRODUCTS.PRODUCTGROUP) In (" & strCriteria & ")) AND ((tblacq.Acqdate) Between #" & _
Format(Me.Startdate, "mm/dd/yyyy") & "#" & _
" and #" & Format(Me.EndDate, "mm/dd/yyyy") & "#"

 
 

Debug.Print strSQL

         
 
' Apply the new SQL statement to the query
   qdf.SQL = strSQL
' Open the query
   DoCmd.OpenReport "STReportsByPG", acViewPreview
   
   
   
   
' Empty the memory
   Set db = Nothing
   Set qdf = Nothing
End Sub