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

VBA SQL statement help

  1. #16
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,111
    Place the 2 new (*) Dim statements with the others, followed by the Set statements after the Dim stamements. Then try inserting the part between **** where you commented out your date checking and read the notes at the end.


    Code:
    Dim intCondition As Integer '*
    Dim ctlB As Control, ctlE As Control '*
    Dim strSql As String
    
    Set ctlB = Me.txtCalcSSBegin
    Set ctlE = Me.txtCalcSSEnd
    '***************
     'Check for a blank value
    If Nz(ctlB, "") = "" And Nz(ctlE, "") = "" Then intCondition = 1 'both are blank
    If Nz(ctlB, "") = "" And Not Nz(ctlE, "") = "" Then intCondition = 3 'one is blank the other not
    If Not Nz(ctlB, "") = "" And Nz(ctlE, "") = "" Then intCondition = 3 'one is blank the other not
    
    If intCondition = 3 Then
        MsgBox "Both dates must be entered if one is entered."
    '    Cancel = True
        Exit Sub
    End If
    
    If Not Nz(ctlB, "") = "" And Not Nz(ctlE, "") = "" Then
        If Not IsDate(ctlB) Or Not IsDate(ctlE) Then
            MsgBox "Only valid dates can be entered into date fields."
            'Cancel = True
            Exit Sub
        End If
        If ctlB > ctlE Then
            MsgBox "Begin date cannot be greater than end date."
            'Cancel = True
            Exit Sub
        End If
        intCondition = 2 'If nested IF's pass then dates are OK and both are provided
    End If
    
    '********
    'If Len(strCriteriaPhase) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.SSPhase IN (" & strCriteriaPhase & ")" 'REMOVED THE "AND" FROM HERE
    If intCondition = 2 Then strSql = strSql & " AND dbo_tblPrintCenter.CalcSS BETWEEN #" & ctlB & "# AND #" & ctlE & "#"
    NOTES
    This is tested as much as I can subject to the limitations of me being able to replicate what you might have.
    Date sql is to be appended only if intCondition = 2. If 1, it isn't used but is there in case you ever need it to tweak code further.
    I had to comment out the line where I removed the And from because of not having certain variables. You need to remove the And as noted but allow the line to run. The And gets shifted to the portion that includes the dates. This date addition to the sql must come last as it was before.
    I don't know if you need IsDate validation or not. If the field is formatted as a date then I'd say no as Access won't allow text there. Won't hurt to leave it in I guess. However I think validating that end date comes after begin.
    I had to remove Cancel = True because a button event has no Cancel option. Make sure I put it back where you need it.
    BETWEEN #" & ctlB & "# AND #" & ctlE & "#" I swapped control reference for the variable. Figured might as well use it. If a problem, put the control reference back in.
    I also added Cancel = True (but had to comment out) for date validation lines.

    Hope that helps.
    Last edited by Micron; 08-16-2019 at 09:08 AM. Reason: added to notes
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    (humor about talking to yourself apparently was too much for some)

  2. #17
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    28
    Thank you Micron, this works like a charm!!!

    Sorry for the late response, got put on another project before I could finish this.

  3. #18
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,111
    Glad to hear it worked and was of some help! Been so long that I forgot what this was about.

  4. #19
    Miked1978 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2019
    Posts
    28
    So the form isn't working quite like i want it to. I believe it's the WHERE clause in the strSQL thats causing the issue. The form allows me to select a hull and 1 more paramater whether its LeadDept, WS, SSphase, Or the Dates. However if I select say Hull, WS, and SSPhase it throws Syntax error Missing Operator.

    The Debug.Print shows:
    Code:
     SELECT * FROM dbo_tblPrintCenter WHERE dbo_tblPrintCenter.hull IN ('4019') AND dbo_tblPrintCenter.WS IN ('680','700')  dbo_tblPrintCenter.SSPhase IN ('005')
    and as you can see there is no AND after the second parameter. If I do put the AND in there it will force the user to select the 3rd parameter. I only want hull to be the only required parameter and the rest are optional.

    Code:
    Private Sub btnSearch_Click()
    'Apply Filter button
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteriaHull As String
    Dim strCriteriaWS As String
    Dim strCriteriaLeadDept As String
    Dim strCriteriaPhase As String
    Dim CalcSSBegin As Date
    Dim CalcSSEnf As Date
    Dim strSQL As String
    Dim qryDef As QueryDef
    Dim intCondition As Integer
    Dim ctlB As Control
    Dim ctlE As Control
    
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryform")
    Set ctlB = Me.txtCalcSSBegin
    Set ctlE = Me.txtCalcSSEnd
    
    'get selections from multiselect listbox
    'Hull ListBox
    For Each varItem In Me!lstHull.ItemsSelected
       strCriteriaHull = strCriteriaHull & ",'" & Me!lstHull.ItemData(varItem) & "'"
    Next varItem
    'get selections from multiselect listbox
    'WS ListBox
    For Each varItem In Me!lstWS.ItemsSelected
       strCriteriaWS = strCriteriaWS & ",'" & Me!lstWS.ItemData(varItem) & "'"
    Next varItem
    'get selections from multiselect listbox
    'LeadDept ListBox
    For Each varItem In Me!lstLeadDept.ItemsSelected
       strCriteriaLeadDept = strCriteriaLeadDept & ",'" & Me!lstLeadDept.ItemData(varItem) & "'"
    Next varItem
    'get selections from multiselect listbox
    'SS Phase ListBox
    For Each varItem In Me!lstSSPhase.ItemsSelected
       strCriteriaPhase = strCriteriaPhase & ",'" & Me!lstSSPhase.ItemData(varItem) & "'"
       Next varItem
    
       
    If Len(strCriteriaHull) > 0 Then
      strCriteriaHull = Right(strCriteriaHull, Len(strCriteriaHull) - 1)
    End If
    If Len(strCriteriaWS) > 0 Then
      strCriteriaWS = Right(strCriteriaWS, Len(strCriteriaWS) - 1)
    End If
    If Len(strCriteriaLeadDept) > 0 Then
      strCriteriaLeadDept = Right(strCriteriaLeadDept, Len(strCriteriaLeadDept) - 1)
    End If
    If Len(strCriteriaPhase) > 0 Then
      strCriteriaPhase = Right(strCriteriaPhase, Len(strCriteriaPhase) - 1)
    End If
    
    'Check for a blank value
    If Nz(ctlB, "") = "" And Nz(ctlE, "") = "" Then intCondition = 1 'both are blank
    If Nz(ctlB, "") = "" And Not Nz(ctlE, "") = "" Then intCondition = 3 'one is blank the other not
    If Not Nz(ctlB, "") = "" And Nz(ctlE, "") = "" Then intCondition = 3 'one is blank the other not
    If intCondition = 3 Then
        MsgBox "Both dates must be entered if one is entered."
        Cancel = True
        Exit Sub
    End If
    If Not Nz(ctlB, "") = "" And Not Nz(ctlE, "") = "" Then
        If Not IsDate(ctlB) Or Not IsDate(ctlE) Then
            MsgBox "Only valid dates can be entered into date fields."
            Cancel = True
            Exit Sub
        End If
        If ctlB > ctlE Then
            MsgBox "Begin date cannot be greater than end date."
            Cancel = True
            Exit Sub
        End If
        intCondition = 2 'If nested IF's pass then dates are OK and both are provided
    End If
    
    strSQL = " SELECT * FROM dbo_tblPrintCenter WHERE "
        If Len(strCriteriaHull) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.hull IN (" & strCriteriaHull & ") AND "
        If Len(strCriteriaLeadDept) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.LeadDept IN (" & strCriteriaLeadDept & ") "
        If Len(strCriteriaWS) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.WS IN (" & strCriteriaWS & ")  "
        If Len(strCriteriaPhase) > 0 Then strSQL = strSQL & "dbo_tblPrintCenter.SSPhase IN (" & strCriteriaPhase & ") "
        If intCondition = 2 Then strSQL = strSQL & " AND dbo_tblPrintCenter.CalcSS BETWEEN #" & ctlB & "# And #" & ctlE & "#"
    
    Debug.Print strSQL
    DoCmd.SetWarnings False
    
    Set qryDef = db.QueryDefs("qryform")
       qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
    'assign to form recordsource
    Me.dbo_tblPrintCenter_subform.Form.RecordSource = strSQL
    'Set the object variables to Nothing to ensurethat these are cleared from the memory
    Set db = Nothing
    Set qdf = Nothing
    'Requerys Form
    Forms![Print Request Search Form]![dbo_tblPrintCenter subform].Form.Requery
    End Sub

  5. #20
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,111
    I would drop the idea of using a Select Case block to make such decisions as it can get large and confusing with many options. For this situation, I would have a main sql strSql and append to it accordingly when I make the tests for whether or not a field filled in. Variables can be used for the additional portions too. I'm not going to worry about delimiters and such as you seem to have a grasp of that, so something like

    strSql = "SELECT whatever FROM wherever WHERE myRequiredField = " & Me.someControl"

    assign the optional choices to variables, beginning with AND
    IF Not Nz(Me.someControl,"") = "" Then strWs = " AND dbo_tblPrintCenter.WS IN (" & strCriteriaWS & ")"

    When all variables have been assigned their values (or not because the option was not used), concatenate each variable to strSql if the test result is not "". Watch out for any possible extraneous characters at the end as you were doing before.

    Sorry I can't spend a lot of time on this right now as I'm trying to get a shed built. I should be out there now!
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    (humor about talking to yourself apparently was too much for some)

  6. #21
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,111
    Not out there yet!
    For each option, you could also insert the AND here because you've tested the length of the variable. If > than 0 then prepend the AND and strip the leading ",".
    If Len(strCriteriaHull) > 0 Then
    strCriteriaHull = " AND " & Right(strCriteriaHull, Len(strCriteriaHull) - 1)

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

Similar Threads

  1. if statement
    By joym in forum Access
    Replies: 3
    Last Post: 05-03-2017, 03:44 PM
  2. Replies: 11
    Last Post: 04-29-2015, 01:38 PM
  3. iif Statement Help
    By smc678 in forum Forms
    Replies: 8
    Last Post: 12-11-2012, 11:02 AM
  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