Results 1 to 9 of 9

Thread: Fetch Data From Access Database Using SQL Only If All The Where Criteria Is Selected

  1. #1
    Junior Member
    Join Date
    Jul 2013
    Posts
    12
    Rep Power
    0

    Fetch Data From Access Database Using SQL Only If All The Where Criteria Is Selected

    hi ,

    i have a useform in excel to pull out the data from access. but however, when user select the first option in the combobox, it pulls out all the data. but consequent option appear fine.

  2. #2

  3. #3
    Junior Member
    Join Date
    Jul 2013
    Posts
    12
    Rep Power
    0
    HTML Code:
    Public strAccessDatabaseName As String ' = "C:\Users\tsubasa\Downloads\RahBreth.accdb" 'Path of the database
    
    Private Sub cmdCancel_Click()
    
        Unload Me
        
    End Sub
    
    Private Sub cmdFetch_Click()
    
        Dim strSql As String
        Dim lng As Long
        Dim strALMSource As String
        Dim strALMID As String
        For lng = 1 To lstALMSource.ListCount - 1
            If lstALMSource.Selected(lng) Then
                strALMSource = strALMSource & "'" & lstALMSource.List(lng) & "',"
            End If
        Next lng
        If strALMSource <> "" Then
            strALMSource = Left(strALMSource, Len(strALMSource) - 1)
        End If
        
        strSql = "SELECT [DAILY ALARM].* FROM [DAILY ALARM] WHERE"
        strSql = strSql & vbNewLine
        If Len(strALMSource) Then
            strSql = strSql & vbNewLine & "([DAILY ALARM].ALMSOURCE) In (" & strALMSource & ")"
            strSql = strSql & vbNewLine & ""
            strSql = strSql & vbNewLine & "AND"
        End If
        If cboALMID.Text <> "" Then
            strSql = strSql & vbNewLine & ""
            strSql = strSql & vbNewLine & "(([DAILY ALARM].ALMID)=" & cboALMID.Text & ")"
            strSql = strSql & vbNewLine & ""
            strSql = strSql & vbNewLine & "AND"
        End If
        strSql = strSql & vbNewLine & ""
        strSql = strSql & vbNewLine & "(([DAILY ALARM].ALMTM)>=#" & Me.Controls("txtDateFrom").Value & " " & FormatDateTime(Me.Controls("txtTimeFrom").Value, vbLongTime) & "#) AND (([DAILY ALARM].ALMTM)<=#" & Me.Controls("txtDateTo").Value & " " & FormatDateTime(Me.Controls("txtTimeTo").Value, vbLongTime) & "#);"
        Worksheets("DAILY ALARM").UsedRange.Offset(1).ClearContents
        Call SQLJuicer(strSql, strAccessDatabaseName, Worksheets("DAILY ALARM").Cells(2, 1))
        
    End Sub
    
    
    
    Private Sub txtDateFrom_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        
        Cancel = True
        frmCalendar.strControlName = "txtDateFrom"
        frmCalendar.Show
        
    End Sub
    
    Private Sub txtDateFrom_Enter()
        
        frmCalendar.strControlName = "txtDateFrom"
        frmCalendar.Show
        
    End Sub
    
    Private Sub txtDateTo_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
        Cancel = True
        frmCalendar.strControlName = "txtDateTo"
        frmCalendar.Show
        
    End Sub
    
    Private Sub txtDateTo_Enter()
        
        frmCalendar.strControlName = "txtDateTo"
        frmCalendar.Show
        
    End Sub
    
    Private Sub txtTimeFrom_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
        Cancel = True
        frmTime.strControlName = "txtTimeFrom"
        frmTime.Show
        
    End Sub
    
    Private Sub txtTimeFrom_Enter()
        
        frmTime.strControlName = "txtTimeFrom"
        frmTime.Show
        
    End Sub
    
    Private Sub txtTimeTo_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
        Cancel = True
        frmTime.strControlName = "txtTimeTo"
        frmTime.Show
        
    End Sub
    
    Private Sub txtTimeTo_Enter()
    
        frmTime.strControlName = "txtTimeTo"
        frmTime.Show
        
    End Sub
    
    Private Sub UserForm_Activate()
    
        Dim strSql As String
        Dim strAccessDestinationTableName As String
        Dim strExcelFieldNames As String
        Dim strExcelRangeName As String
        Dim lng As Long
        Const blnDropTableAndCreateNewTable As Boolean = False 'Set to true if you need to drop the table
        strAccessDatabaseName = Sheet1.txtDBPath.Text
        txtDateFrom.Text = FormatDateTime(Date, vbShortDate)
        txtDateTo.Text = FormatDateTime(Date, vbShortDate)
    '    With Me.Controls.Add("MSComCtl2.DTPicker", "dtpFromDate", True)
    '        .Top = 222
    '        .Left = 156
    '        .Height = 18
    '        .Width = 110.25
    '        .Format = 1
    '    End With
    '
    '    With Me.Controls.Add("MSComCtl2.DTPicker", "dtpFromTime", True)
    '        .Top = 222
    '        .Left = 270
    '        .Height = 18
    '        .Width = 110.25
    '        .Format = 2
    '    End With
    '
    '    With Me.Controls.Add("MSComCtl2.DTPicker", "dtpToDate", True)
    '        .Top = 240
    '        .Left = 156
    '        .Height = 18
    '        .Width = 110.25
    '        .Format = 1
    '    End With
    '
    '    With Me.Controls.Add("MSComCtl2.DTPicker", "dtpToTime", True)
    '        .Top = 240
    '        .Left = 270
    '        .Height = 18
    '        .Width = 110.25
    '        .Format = 2
    '    End With
        
        strAccessDestinationTableName = "[DAILY ALARM]"
        Me.lstALMSource.List = Application.Transpose(SQLJuicer("SELECT [ALMSOURCE] FROM " & strAccessDestinationTableName & " GROUP BY [ALMSOURCE] ORDER BY [ALMSOURCE]", strAccessDatabaseName))
        Me.cboALMID.List = Application.Transpose(SQLJuicer("SELECT [ALMID] FROM " & strAccessDestinationTableName & " GROUP BY [ALMID] ORDER BY [ALMID]", strAccessDatabaseName))
    
    End Sub
    
    Private Sub UserForm_initialize()
        Me.Top = ActiveSheet.Cells(1).Top
        Me.Left = ActiveSheet.Cells(1).Left
    End Sub
    Untitled.jpgUntitled1.jpg

  4. #4
    Junior Member
    Join Date
    Jul 2013
    Posts
    12
    Rep Power
    0
    ALMSOURCE for the first option filter everything. Besides that, the rest works perfectly.

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    The code is designed specifically to give you the option to decide whether or not to use both the combo-box and the list-box options, or either one, or neither. The codes

    Code:
    If strALMSource <> "" Then
    .....
    .....
    If cboALMID.Text <> "" Then
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Junior Member
    Join Date
    Jul 2013
    Posts
    12
    Rep Power
    0
    Where am I suppose to put the codes . Cause for now my combobox not working . For the first one , filter everything . But the rest is working perfectly fine

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Nothing actually. Just change the line
    Code:
    For lng = 1 To lstALMSource.ListCount - 1
    to
    Code:
    For lng = 0 To lstALMSource.ListCount - 1
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    By the way, the first item in the list box was being missed because of the starting variable being 1 instead of 0. Correct it like this..
    Code:
    For lng = 1 To lstALMSource.ListCount - 1
    to
    Code:
    For lng = 0 To lstALMSource.ListCount - 1
    Check the video
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Junior Member
    Join Date
    Jul 2013
    Posts
    12
    Rep Power
    0
    Thanks alot

Similar Threads

  1. Compact and Repair MS Access Database
    By LalitPandey87 in forum MS-Access Tips And Tricks
    Replies: 8
    Last Post: 04-17-2017, 03:41 PM
  2. Replies: 4
    Last Post: 07-29-2013, 08:25 AM
  3. Fetch multiple values based on criteria
    By Lucero in forum Excel Help
    Replies: 8
    Last Post: 04-07-2012, 12:35 PM
  4. Replies: 7
    Last Post: 03-06-2012, 07:49 AM
  5. Checking Table Exist in Access Database or Not
    By littleiitin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 11-16-2011, 04:32 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
  •