Code Help


S

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
 
Ad

Advertisements

Ad

Advertisements

D

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

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

SG said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top