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

VBA SQL statement help

  1. #16
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,021
    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.
    Sometimes I talk to myself - like when I need expert advice.

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