Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21

VBA SQL statement help

  1. #16
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,135
    Quote Originally Posted by Miked1978 View Post
    I really appreciate yall helping me out.

    I did the Debug.Print and might have spotted the problem, just not sure how to fix it even though i'm no longer getting any errors.

    If you look at the IN clause it has a comma in front of each
    SELECT * FROM dbo_tblPrintCenter_SGI WHERE dbo_tblPrintCenter_SGI.hull IN (,'2618') <--- shouldn't be a comma there???


    AND dbo_tblPrintCenter_SGI.SSPhase IN (",'001'")
    <--- shouldn't be a quotation and comma there???
    AND dbo_tblPrintCenter_SGI.CalcSS BETWEEN 1/1/2016 AND 7/10/2019
    That's what these lines were for:

    strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #17
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    13
    Quote Originally Posted by pbaldy View Post
    That's what these lines were for:

    strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)

    What should they be? When i have those lines in and the user makes the selection to ALL of the criteria, it works fine. However if the user only selects 1 or 2, i get an error.

    Invalid procedure call or argument

    Below is what i have:

    strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
    strCriteriaWS = Right(strCriteriaWS, Len(strCriteriaWS) - 1)
    strCriteriaLeadDept = Right(strCriteriaLeadDept, Len(strCriteriaLeadDept) - 1)
    strCriteriaPhase = Right(strCriteriaPhase, Len(strCriteriaPhase) - 1)

    strSQL = " SELECT * FROM dbo_tblPrintCenter_SGI WHERE "
    If Len(strCriteriaHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter_SGI.hull IN (" & strCriteriaHull & ") AND "
    If Len(strCriteriaWS) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter_SGI.WS IN (" & strCriteriaWS & ") AND "
    If Len(strCriteriaLeadDept) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter_SGI.LeadDept IN (" & strCriteriaLeadDept & ") And "
    If Len(strCriteriaPhase) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter_SGI.SSPhase IN (" & strCriteriaPhase & ") AND "
    strSQL = strSQL & "dbo_tblPrintCenter_SGI.CalcSS BETWEEN #" & txtCalcSSBegin & "# AND #" & txtCalcSSEnd & "#"
    ' strSQL = strSQL & "dbo_tblPrintCenter_SGI.CalcSS BETWEEN (" & txtCalcSSBegin & ") AND (" & txtCalcSSEnd & ")"

    'Debug.Print strSQL

    Me!qryformsubform.Form.RecordSource = strSQL
    End Sub

  3. #18
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    13
    Update:

    I added an IF statement to try and handle if the user selects anything or not however if the user leaves one of the list boxes blank, nothing is returned. I need it to assume if user leaves a list box blank then they want to show everything.

    If Me!cmboHull.ItemsSelected.Count > 0 Then strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1) Else strCriteriaHull = "dbo_tblPrintCenter_SGI.hull Like '*'"
    If Me!cmboWS.ItemsSelected.Count > 0 Then strCriteriaWS = Right(strCriteriaWS, Len(strCriteriaWS) - 1) Else strCriteriaWS = "dbo_tblPrintCenter_SGI.WS LIKE '*'"
    If Me!cmboLeadDept.ItemsSelected.Count > 0 Then strCriteriaLeadDept = Right(strCriteriaLeadDept, Len(strCriteriaLeadDept) - 1) Else strCriteriaLeadDept = "dbo_tblPrintCenter_SGI.LeadDept Like '*'"
    If Me!cmboPhase.ItemsSelected.Count > 0 Then strCriteriaPhase = Right(strCriteriaPhase, Len(strCriteriaPhase) - 1) Else strCriteriaPhase = "dbo_tblPrintCenter_SGI.Phase Like '*'"

    This is what the debug.Print is showing:
    SELECT * FROM dbo_tblPrintCenter_SGI WHERE dbo_tblPrintCenter_SGI.hull IN ('2618') AND dbo_tblPrintCenter_SGI.WS IN (dbo_tblPrintCenter_SGI.WS LIKE '*') AND dbo_tblPrintCenter_SGI.LeadDept IN ('00','01','07') And dbo_tblPrintCenter_SGI.SSPhase IN ('001') AND dbo_tblPrintCenter_SGI.CalcSS BETWEEN #1/1/2016# AND #7/11/2019#

    In my test I left the WS (cmboWS field) blank and notice how the SQL statement is including it in the WHERE clause when it probably needs to drop it or maybe select all WS listed in the list box

  4. #19
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,135
    When you conditionally add each field to the WHERE clause, also trim the comma.

    Code:
    If Len(strCriteriaHull) > 0 Then 
      strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
      strSQL = strSQL & "dbo_tblPrintCenter_SGI.hull IN (" & strCriteriaHull & ") AND "
    End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #20
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    13
    Quote Originally Posted by pbaldy View Post
    When you conditionally add each field to the WHERE clause, also trim the comma.

    Code:
    If Len(strCriteriaHull) > 0 Then 
      strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
      strSQL = strSQL & "dbo_tblPrintCenter_SGI.hull IN (" & strCriteriaHull & ") AND "
    End If
    That did the trick. Thank you!!!

  6. #21
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,135
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. If statement
    By UT227 in forum Programming
    Replies: 9
    Last Post: 03-19-2018, 11:06 AM
  2. IF Then vba statement
    By GCLIFTON in forum Queries
    Replies: 3
    Last Post: 05-23-2016, 11:01 AM
  3. Replies: 11
    Last Post: 04-29-2015, 01:38 PM
  4. if statement in sql statement, query builder
    By 54.69.6d.20 in forum Access
    Replies: 4
    Last Post: 09-11-2012, 07:38 AM
  5. Replies: 7
    Last Post: 08-17-2011, 01:49 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
  •  
Tech Forums: Microsoft Office Forums