Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107

    Syntax Check

    Hello,

    I seem to be getting an error with this code. I think it has to do with & "WHERE (((tblProjectStatus.DateClosed) Is Null))" " _



    I appreciate the help!


    Private Sub Command433_Click()

    Dim SQL As String

    SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderStatus.AssignUsername, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton, tblProjectStatus.DateClosed, tblOrderStatus.Project FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblProjectStatus RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblProjectStatus.ProjectNum = tblOrderStatus.Project) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _
    & "WHERE (((tblProjectStatus.DateClosed) Is Null))" " _
    & "Or (tblOrderDetails.OrderNum) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.WBS) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.Sortfield) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblObjectStatus.Status) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderNotifications.Notification) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.Revision) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.OrderType) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderNotifications.CreatedBy) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.PlannerGroup) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderStatus.AssignUsername) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Order BY tblOrderNotifications.CreatedOn DESC"
    Me.RecordSource = SQL
    Me.Requery

    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Remove the extra quote mark.

    "WHERE (((tblProjectStatus.DateClosed) Is Null)) " _
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Thank you June7!

  4. #4
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    The error is gone. But when I enter a value for TxtKeywords or enter null it runs through the code but overrides the & "WHERE (((tblProjectStatus.DateClosed) Is Null)) " _ Should it not always maintain the Where and filter down the Or's?




    Private Sub Command433_Click()

    Dim SQL As String

    SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderStatus.AssignUsername, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton, tblProjectStatus.DateClosed, tblOrderStatus.Project FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblProjectStatus RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblProjectStatus.ProjectNum = tblOrderStatus.Project) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _
    & "WHERE (((tblProjectStatus.DateClosed) Is Null)) " _
    & "Or (tblOrderDetails.OrderNum) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.WBS) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.Sortfield) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblObjectStatus.Status) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderNotifications.Notification) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.Revision) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.OrderType) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderNotifications.CreatedBy) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.PlannerGroup) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderStatus.AssignUsername) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Order BY tblOrderNotifications.CreatedOn DESC"
    Me.RecordSource = SQL
    Me.Requery

    End Sub

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not sure what you want. If you want only records where DateClosed Is Null regardless of the other criteria, maybe:

    DateClosed Is Null AND (all the OR wildcard criteria here)

    Instead of setting RecordSource, I prefer to build just the filter criteria with VBA and apply to form Filter property or the WHERE argument of OpenForm or OpenReport. Review http://allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You can simplify it like this, plus be able to see how the txtkeywords string looks with debug.print:
    Define sSearch as the comparison string including the single quotes and asterisks.
    Code:
    Private Sub Command433_Click()
        Dim sSearch As String
        Dim SQL As String
        sSearch = "'*" & Me.txtKeywords & "*'"
        'debug.print sSearch
        SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderStatus.AssignUsername, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton, tblProjectStatus.DateClosed, tblOrderStatus.Project FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblProjectStatus RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblProjectStatus.ProjectNum = tblOrderStatus.Project) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _
        & "WHERE (((tblProjectStatus.DateClosed) Is Null)) " _
        & "Or (tblOrderDetails.OrderNum) LIKE " & sSearch _
        & " Or (tblOrderDetails.WBS) LIKE " & sSearch _
        & " Or (tblOrderDetails.Sortfield) LIKE " & sSearch _
        & " Or (tblObjectStatus.Status) LIKE " & sSearch _
        & " Or (tblOrderNotifications.Notification) LIKE " & sSearch _
        & " Or (tblOrderDetails.Revision) LIKE " & sSearch _
        & " Or (tblOrderDetails.OrderType) LIKE " & sSearch _
        & " Or (tblOrderNotifications.CreatedBy) LIKE " & sSearch _
        & " Or (tblOrderDetails.PlannerGroup) LIKE " & sSearch _
        & " Or (tblOrderStatus.AssignUsername) LIKE " & sSearch _
        & " Order BY tblOrderNotifications.CreatedOn DESC"
        Me.RecordSource = SQL
        Me.Requery
    End Sub

  7. #7
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    This is what I want. DateClosed is Null and any or all of the remaining criteria. Right now it seems to overlook the dateclosed when I do the remaining filters.


    If I was to build this with VBA could you help me with this please?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You are building with VBA. Try the this AND (this OR this OR this) syntax suggested. The parens surrounding all the OR criteria is critical. Probably read my previous post before I edited it. Might review again.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    For some reason this does not filter down the list.

  10. #10
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    I have tried all of the combinations.

    What this is suppose to do is.

    I type a keyword in the text box TxtKeywords - For example - T117

    It should filter the list down to any of the columns that have T117 and where tblProjectStatus.DateClosed) Is Null

    But it does not work.


    If I remove the & "WHERE (((tblProjectStatus.DateClosed) Is Null)) " _ The filter works. But if I add it back it does not filter at all.



    Private Sub Command433_Click()

    Dim SQL As String

    SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderStatus.AssignUsername, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton, tblProjectStatus.DateClosed, tblOrderStatus.Project FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblProjectStatus RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblProjectStatus.ProjectNum = tblOrderStatus.Project) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _
    & "WHERE (((tblProjectStatus.DateClosed) Is Null)) " _
    & "Or (tblOrderDetails.OrderNum) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.WBS) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.Sortfield) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblObjectStatus.Status) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderNotifications.Notification) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.Revision) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.OrderType) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderNotifications.CreatedBy) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.PlannerGroup) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderStatus.AssignUsername) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Order BY tblOrderNotifications.CreatedOn DESC"
    Me.RecordSource = SQL
    Me.Requery

    End Sub

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use AND between the date and the other OR criteria.

    & "WHERE tblProjectStatus.DateClosed Is Null " _
    & "AND (tblOrderDetails.OrderNum LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or tblOrderDetails.WBS LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or tblOrderDetails.Sortfield LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or tblObjectStatus.Status LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or tblOrderNotifications.Notification LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or tblOrderDetails.Revision LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or tblOrderDetails.OrderType LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or tblOrderNotifications.CreatedBy LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or tblOrderDetails.PlannerGroup LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or tblOrderStatus.AssignUsername LIKE '*" & Me.TxtKeywords & "*') " _
    & "Order BY tblOrderNotifications.CreatedOn DESC"
    Last edited by June7; 09-26-2018 at 12:11 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The problem is that you are comparing a string (TxtKeywords) to a number (OrderNum or perhaps others also).
    In addition, the LIKE operator only works with strings.
    The comparison fails due to the incompatible field types and the whole criteria fails with no error indication.

    You might get around this with some elaborate IIF constructs, but it would be a tedious chore.

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Found an easy workaround. If a field is numeric, use Cstr function to convert it to string so that compare is compatible.
    Code:
    Private Sub Command433_Click() Dim sSearch As String Dim SQL As String sSearch = "'*" & Me.txtKeywords & "*'" SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderStatus.AssignUsername, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton, tblProjectStatus.DateClosed, tblOrderStatus.Project FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblProjectStatus RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblProjectStatus.ProjectNum = tblOrderStatus.Project) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _ & "WHERE (((tblProjectStatus.DateClosed) Is Null)) " _ & " Or (Cstr(tblOrderDetails.OrderNum)) LIKE " & sSearch _ & " Or (tblOrderDetails.WBS) LIKE " & sSearch _ & " Or (tblOrderDetails.Sortfield) LIKE " & sSearch _ & " Or (tblObjectStatus.Status) LIKE " & sSearch _ & " Or (tblOrderNotifications.Notification) LIKE " & sSearch _ & " Or (tblOrderDetails.Revision) LIKE " & sSearch _ & " Or (tblOrderDetails.OrderType) LIKE " & sSearch _ & " Or (tblOrderNotifications.CreatedBy) LIKE " & sSearch _ & " Or (tblOrderDetails.PlannerGroup) LIKE " & sSearch _ & " Or (tblOrderStatus.AssignUsername) LIKE " & sSearch _ & " Order BY tblOrderNotifications.CreatedOn DESC" Me.RecordSource = SQL Me.Requery End Sub

    Last edited by davegri; 09-26-2018 at 07:29 AM. Reason: clarif

  14. #14
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    Hi Davegri,

    Thank you for the example. The issue with my code is not the numeric. It works perfectly until I add & "WHERE tblProjectStatus.DateClosed) Is Null " _

    Then the query fails. I would like to solve this problem because I think there is a need for a multiple field search box for forms.

    Maybe we are approaching this the wrong way with a query? Is there a better way to handle multiple field search in VBA?





  15. #15
    sparker75 is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    107
    This seems to be stable now. The only issue I have now is when I do a search with blank txtkeywords. Is there a way to add a error check?




    Private Sub Command433_Click()
    Dim SQL As String
    SQL = "SELECT tblOrderNotifications.CreatedOn, tblOrderDetails.WBS, tblOrderNotifications.Notification, tblOrderDetails.Revision, tblOrderDetails.OrderType, tblOrderDetails.OrderNum, tblOrderDetails.Sortfield, tblOrderNotifications.CreatedBy, tblOrderStatus.AssignUsername, tblOrderDetails.PlannerGroup, tblObjectStatus.Status, tblObjectPriority.Descripton, tblProjectStatus.DateClosed, tblOrderStatus.Project FROM tblOrderNotifications LEFT JOIN (tblObjectStatus RIGHT JOIN (tblObjectPriority RIGHT JOIN (tblProjectStatus RIGHT JOIN (tblOrderDetails LEFT JOIN tblOrderStatus ON tblOrderDetails.OrderNum = tblOrderStatus.OrderNum) ON tblProjectStatus.ProjectNum = tblOrderStatus.Project) ON tblObjectPriority.PrioID = tblOrderStatus.PRIOID) ON tblObjectStatus.SID = tblOrderStatus.SID) ON tblOrderNotifications.Notification = tblOrderDetails.Notification " _
    & "WHERE (((tblProjectStatus.DateClosed) Is Null)) " _
    & "And (tblOrderDetails.OrderNum) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.WBS) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.Sortfield) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblObjectStatus.Status) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderNotifications.Notification) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.Revision) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.OrderType) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderNotifications.CreatedBy) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderDetails.PlannerGroup) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Or (tblOrderStatus.AssignUsername) LIKE '*" & Me.TxtKeywords & "*' " _
    & "Order BY tblOrderNotifications.CreatedOn DESC"
    Me.RecordSource = SQL
    Me.Requery
    End Sub

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. CHECK CONSTRAINT causing Syntax Error?
    By AishlinnAnne in forum SQL Server
    Replies: 18
    Last Post: 09-13-2016, 12:42 PM
  2. Replies: 5
    Last Post: 06-26-2014, 12:52 PM
  3. Replies: 4
    Last Post: 02-03-2014, 12:16 PM
  4. Replies: 13
    Last Post: 04-17-2013, 04:17 PM
  5. Replies: 3
    Last Post: 10-25-2012, 10:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums