Code Help

[SG]

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

 

[Dennis]

Yeah, your last ")" is in the wrong place.

 

[Dennis]

Oops! My bad. You are MISSING your last ")"

 

[Dale Fye]

Actually, I think SG is missing his last three quotes.

Personally, when I write my SQL in code, I leave out all of the unnecessary
parenthesis. I also tend to break up the SQL to make it easier to read, so
I think it should read:

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") & "#"

However, this confuses me because what it is going to return is a recordset
which contains a list of products, and each product will potentially have
multiple records (one for every AcqDate between StartDate and EndDate. Is
that what you are looking for?

Dale

Oops! My bad. You are MISSING your last ")"

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