Syntax error

[topdesk123]

Hi!

I'm trying to run a report that is filtered by a form that contains several text fields and multi-select list boxes. I am working on one section right now and I'm getting a syntax error. If I run the sql by itself, I get correct data. Here's the code:

 Variant
Dim sSql As String

Set ctlList = [Forms]![FReportParams]!Salespeople
Set ctlList2 = [Forms]![FReportParams]!ProdGrp
Set ctlList3 = [Forms]![FReportParams]!MFGS
Set ctlList4 = [Forms]![FReportParams]!Bidders
Set ctlList5 = [Forms]![FReportParams]!status




   'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strwhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

'    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull([Forms]![FReportParams]![BidStartDate_BeginR]) Then
        strwhere = strwhere & "([biddate] >= " & Format([Forms]![FReportParams]![BidStartDate_BeginR], conJetDate) & ") AND "
 '       Debug.Print bidate & " " & BidStartDate_BeginR
    End If
    If Not IsNull([Forms]![FReportParams]![BidstartDate_EndR]) Then
        strwhere = strwhere & "([biddate] <= " & Format([Forms]![FReportParams]![BidstartDate_EndR]) & ") AND "
 '               Debug.Print bidate & " " & BidstartDate_EndR

    End If

'sSql = "SELECT ProjectItems.TOPersonID, ProjectItems.Person, Persons.PersonID, Projects.FollowUpDate, Projects.QuoteCompleted, Projects.ProjectNo, Projects.ProjectName, Projects.BidDate, ProjectNotes.Note, Projects.ProjectID, ProductGrps.ProductGroup, ProjectItems.ItemBid " & vbCrLf & _
'"FROM Persons INNER JOIN (((Projects INNER JOIN ProjectItems ON Projects.ProjectID = ProjectItems.ProjectID) LEFT JOIN ProjectNotes ON Projects.ProjectID = ProjectNotes.ProjectID) INNER JOIN ProductGrps ON ProjectItems.ProductGrpID = ProductGrps.ProductGrpID) ON Persons.PersonID = ProjectItems.TOPersonID " & vbCrLf & _
'"WHERE (((ProjectItems.TOPersonID) In (28,8)));"

sSql = "SELECT * FROM masterquery WHERE topersonid IN ("
For Each Lmnt In ctlList.itemsselected
    sSql = sSql & ctlList.ItemData(Lmnt) & ","
Next
sSql = Left(sSql, Len(sSql) - 1) & ")" 'Remove Last comma and single quote and add closing bracket

Debug.print gives me: 
?ssql
SELECT * FROM masterquery WHERE topersonid IN (8,28)

If I use IN(8,28) in the query it works. Please help me, what am I missing? Thank you in advance!!

DoCmd.Openreport Forms![FMainMenu]![FReports].Form![ReportName].Column(3), acViewPreview, , sSql
DoCmd.SetWarnings False


Forms("freportparams").SetFocus
    DoCmd.Minimize
    DoCmd.SetWarnings True
    
End Sub

 

[plog]

You found the issue:

SELECT * FROM masterquery WHERE topersonid IN (8,28)

If I use IN(8,28) in the query it works. Please help me, what am I missing? Thank you in advance!!

What's the difference between the one that is broken and the one that works?

 

[topdesk123]

When I click the button to run the report, I get a syntax error highlighting this line:
DoCmd.Openreport Forms![FMainMenu]![FReports].Form![ReportName].Column(3), acViewPreview, , sSql

Thanks!

 

[Gasman]

If that sSQL is meant to be the filter, then you only use everything after the WHERE ?

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport

 

[topdesk123]

Well this works: (note, I removed a comma between acviewpreview and ssql.

sSql = "SELECT * FROM masterquery WHERE topersonid IN ("
For Each Lmnt In ctlList.itemsselected
    sSql = sSql & ctlList.ItemData(Lmnt) & ","
Next
sSql = Left(sSql, Len(sSql) - 1) & ")" 'Remove Last comma and single quote and add closing bracket

DoCmd.Openreport Forms![fMainMenu]![FReports].Form![ReportName].Column(3), acViewPreview, sSql

Thank you all for your time and suggestions! You're all ROCKSTARS in my book! I'll probably be back soon.

 

[Gasman]

Really?

 

[topdesk123]

I wouldn't kid Gasman, I have no idea why though!

 

[topdesk123]

Onto the next issue. I need to grab the items selected from the other listboxes and create a final criteria string. I have no idea where to start. This is what works for the first listbox:

sSql = "SELECT * FROM masterquery WHERE topersonid IN ("
For Each Lmnt In ctlList.itemsselected
    sSql = sSql & ctlList.ItemData(Lmnt) & ","
Next
'Remove Last comma and single quote and add closing bracket
sSql = Left(sSql, Len(sSql) - 1) & ")"
DoCmd.Openreport Forms![fMainMenu]![FReports].Form![ReportName].Column(3), acViewPreview, ssql

How should I go about this? Create 4 more ssqls? (ssql2, ssql3, ssql4, ssql5)?

Thank you for any pointers!

 

[plog]

First, I would rewrite your code so that you don't add unnecessary commas inside the IN. You do this by ading a comma prior to a value not after it. You do that by adding a value that will not effect the results:

sSql = "SELECT * FROM masterquery WHERE topersonid IN (0"
For Each Lmnt In ctlList.itemsselected
    sSql = sSql & ", " & ctlList.ItemData(Lmnt)
Next

Then for 4 other criterias, I would make a function. Move the above code to a new function, change it to so ctlList.itemselected is passed to it, then have it compile and return a string which contains its part of the IN().

 

[Gasman]

I wouldn't kid Gasman, I have no idea why though!

Nor I. I know you can use the name of a query, but I did not know it could be the actual sql string.
Plus when I tried it, it made no difference to the report?