Syntax error (1 Viewer)

topdesk123

Registered User.
Local time
Today, 00:16
Joined
Mar 28, 2013
Messages
47
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:
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

Banishment Pending
Local time
Today, 02:16
Joined
May 11, 2011
Messages
10,346
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

Registered User.
Local time
Today, 00:16
Joined
Mar 28, 2013
Messages
47
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!
 

topdesk123

Registered User.
Local time
Today, 00:16
Joined
Mar 28, 2013
Messages
47
Well this works: (note, I removed a comma between acviewpreview and ssql.

Code:
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. :)
 

topdesk123

Registered User.
Local time
Today, 00:16
Joined
Mar 28, 2013
Messages
47
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:

Code:
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

Banishment Pending
Local time
Today, 02:16
Joined
May 11, 2011
Messages
10,346
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:

Code:
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

Enthusiastic Amateur
Local time
Today, 08:16
Joined
Sep 21, 2011
Messages
7,898
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?
 

Users who are viewing this thread

Top Bottom