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

[bethrah]

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.

[alansidman]

Suggest you post a copy of your spreadsheet and code behind the userform as well as a sanitized copy of your database for testing and analysis

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg. A0opm95t2XEA0q3KshmuuY
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[bethrah]

ALMSOURCE for the first option filter everything. Besides that, the rest works perfectly.

[bethrah]

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

[Excel Fox]

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

If strALMSource <> "" Then
.....
.....
If cboALMID.Text <> "" Then

[bethrah]

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

[Excel Fox]

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..

For lng = 1 To lstALMSource.ListCount - 1

to

For lng = 0 To lstALMSource.ListCount - 1

Check the video

[Excel Fox]

Nothing actually. Just change the line

For lng = 1 To lstALMSource.ListCount - 1

to

For lng = 0 To lstALMSource.ListCount - 1

[bethrah]

Thanks alot