Results 1 to 11 of 11
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Simple Syntax Error on SQL

    Good Morning all!!! I just need a second set of eyes. I am getting a syntax error in query expresion on this SQL statement:

    "SELECT * FROM WOTracking WHERE OrderNo = '" & Me.tbxItemNo & "' ORDER BY SKU;"

    The Debug.Print is returning:

    SELECT * FROM WOTracking WHERE OrderNo = '6438634' ORDER BY SKU;



    Everything looks right to me, but obviously not.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Is the "OrderNo" field a Numeric or Text/String data type?
    If numeric, get rid of the single quotes around it, i.e.
    Code:
    "SELECT * FROM WOTracking WHERE OrderNo = " & Me.tbxItemNo & " ORDER BY SKU;"

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    It is Short Text.

    I am using:

    DoCmd.OpenReport "Work Order Report", acViewReport, , strViewSQL

    to open the report.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I am confused. If you are just opening a report, where does this SQL come into play?

    Can you post your entire code?

    If you are just trying to write the "WHERE" condition of the DoCmd.Open report method, it should just be the "WHERE" clause, without the word "WHERE", not the whole SQL statement.
    See: https://docs.microsoft.com/en-us/off...cmd.openreport

  5. #5
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Here is the entire Sub. I know that some of the others need some changes. I am just working through the first one.

    Code:
    Private Sub cmdViewRec_Click()
    Dim strViewSQL As String
    
    
    strViewSQL = ""
    
    
       Select Case Me.cbxSearchFor.Value
            Case "Work Order" 'Work Order
                  Select Case Me.cbxSearchBy.Value
                    Case "Order Number" 'Order Number
                        Me.tbxItemNo.SetFocus
                        strViewSQL = "SELECT * FROM WOTracking WHERE OrderNo = '" & Me.tbxItemNo & "' ORDER BY SKU;"
                        Debug.Print strViewSQL
                        DoCmd.OpenReport "Work Order Report", acViewReport, , strViewSQL
                    Case 2 'SKU
                        strViewSQL = "SELECT * FROM WOTracking WHERE [SKU] ='" & Me.tbxItemNo & "'ORDER BY Date_Time;'"
                        DoCmd.OpenReport "WO Report", acViewReport, , strViewSQL
                    Case 3 'Employee
                        strViewSQL = "SELECT * FROM WOTracking WHERE [Employee] ='" & Me.cbxSearchBy & "'ORDER BY Date_Time;'"
                        DoCmd.OpenReport "WO Report", acViewReport, , strViewSQL
                    Case 4 'Date
                        strViewSQL = "SELECT * FROM WOTracking WHERE [Date_Time] ='" & Me.tbxDate & "'ORDER BY Date_Time;'"
                        DoCmd.OpenReport "WO Report", acViewReport, , strViewSQL
                End Select
                Debug.Print gstrViewSQL
            Case 2 'Defect Event
                Select Case Me.cbxSearchBy.Value
                    Case 1 'Customer Name
                        strViewSQL = "'Select * FROM WOTracking WHERE CustomerName = & Me.cbxSearchItem;"
                    Case 2 'SKU"
                        strViewSQL = "'Select * FROM WOTracking WHERE SKU = & Me.tbxItemNo & " '"ORDER BY Date_Time;"
                        DoCmd.OpenForm "frmDefViewEdit", , , strViewSQL
                    Case 3 'Order Number
                        strViewSQL = "'Select * FROM WOTracking WHERE OrderNo = & Me.tbxItemNo ORDER BY Date_Time;"
                        DoCmd.OpenForm "frmDefViewEdit", , , strViewSQL
                    Case 4 'Status
                        strViewSQL = "'Select * FROM WOTracking WHERE Status = & Me.cbxSearchItem ORDER BY Date_Time;"
                        DoCmd.OpenForm "frmDefViewEdit", , , strViewSQL
                    Case 5 'Defect Category
                        strViewSQL = "'Select * FROM WOTracking WHERE Category = & Me.cbxSearchItem ORDER BY Date_Time;"
                        DoCmd.OpenForm "frmDefViewEdit", , , strViewSQL
                End Select
        End Select
    End Sub
    I will try with just the Where clause, minus the Where.

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I will try with just the Where clause, minus the Where.
    Yes, try that.

    If you wanted to update the entire SQL Statement, then you would want to update the SQL code of the Query that is the source of the Report before trying to open the report, something like I did here:
    https://www.accessforums.net/showthr...053#post178053

  7. #7
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    I am trying:

    Code:
    DoCmd.OpenReport "Work Order Report", acViewReport, , [ OrderNo ] = Forms![ frmSearch ]![ tbxItemNo ]
    but nothing happens On Click. No error, but report does not open either.

  8. #8
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    So, I noticed that I had changed Case from a number to the "Text" on the first one. When I went and changed the others to text, I got an error "Cannot find the field "|1" in your expression". Probably unrelated.

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Since you said it is a TEXT field, I think it would need to look something like this:
    Code:
    DoCmd.OpenReport "Work Order Report", acViewReport, , "OrderNo = '" & Forms![frmSearch]![tbxItemNo] & "'"
    or perhaps:
    Code:
    DoCmd.OpenReport "Work Order Report", acViewReport, , "OrderNo = '" & Me.tbxItemNo & "'"

  10. #10
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    That was it. Thank you so much. Now I can just modify it for the other options. Thank you!

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-09-2015, 01:34 PM
  2. simple boolean syntax ?
    By markjkubicki in forum Forms
    Replies: 3
    Last Post: 03-05-2013, 06:26 PM
  3. simple syntax ?
    By markjkubicki in forum Queries
    Replies: 2
    Last Post: 10-09-2012, 10:31 AM
  4. Simple Syntax Help
    By cvegas in forum Programming
    Replies: 7
    Last Post: 04-20-2011, 01:46 PM
  5. simple syntax question
    By markjkubicki in forum Programming
    Replies: 6
    Last Post: 09-14-2010, 04:13 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