Results 1 to 7 of 7
  1. #1
    Paowtrick is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Nov 2018
    Posts
    10

    Filter listbox date with optionbutton.

    Hi!

    I have this listbox I want to filter the date to three different states. =<, => and *.

    But im not sure how to do this. Last thing I tried was to have a textbox connected to optionbutton to show "=<Date()" with cases. then use the texbox as source in the query of date.


    But that was a dead end. didnt work

    Any other way I could use optionbuttons?

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Easiest way probably - you would have three saved queries with the criteria in them then change the listbox row source to the appropriate query in the after update event of the option list.
    Alternative method - build the SQL for the rowsource dynamically based on the option box choice then apply it.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Paowtrick is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Nov 2018
    Posts
    10
    I see, so maybe something like this?

    Code:
    Private Sub optionbutton_Click()Select Case Me.OptionButton.Value
        Case 1
            me.Listbox.RowSource = SQL code 1
        Case 2
            me.Listbox.RowSource = SQL code 2
        Case 3
            me.Listbox.RowSource = SQL code 3
    End Select
    End Sub

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That looks about right. You can probably keep the code pretty straight forwards by simply changing the where clause - so something along the lines of;

    Code:
    Private Sub optionbutton_Click()
    
    Dim sSql as String
    Dim sWhere as string
    
    sSql = "SELECT * From Yourtable "
    
    Select Case Me.OptionButton.Value
        Case 1
            sWhere = "WHERE [DateField] >= Date()"
    
        Case 2
    
            sWhere = "WHERE [DateField] <= Date()"
    
        Case 3       
    
            sWhere = ""   ' No criteria
    
    End Select
    
    Debug.Print sSql & sWhere
    
    me.Listbox.RowSource = sSql & sWhere
    
    End Sub
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Paowtrick is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Nov 2018
    Posts
    10
    Im stuck on another thing now. Do you know whats wrong with this sql in VBA.
    Sorry for the late reply, im back at work now.

    edit: Ill change row to make it easier to read.

    Code:
    Me.Lista.RowSource = "
    SELECT tblUnderhåll.UnderhållsID, tblUnderhåll.Uppdrag, tblAvdelning.Avdelning, tblMaskin.Maskin, tblMaskindel.Maskindel, tblUnderhåll.[Datum Planerat], tblUnderhåll.[Svår uppgift] 
    FROM tblMaskin 
    INNER JOIN ((tblMaskindel 
    INNER JOIN (tblAvdelning 
    INNER JOIN tblMaskinKomboID ON tblAvdelning.Avdelning_ID = tblMaskinKomboID.AvdelningsID) ON tblMaskindel.MaskindelsID = tblMaskinKomboID.MaskindelsID) 
    INNER JOIN tblUnderhåll ON tblMaskinKomboID.MaskinKomboID = tblUnderhåll.MaskinKomboID) ON tblMaskin.MaskinID = tblMaskinKomboID.MaskinID 
    WHERE (((tblAvdelning.Avdelning) Like " * " & Forms!frmUnderhållAdmin!kmbFilterAvdelning & " * ") 
    And ((tblMaskin.Maskin) Like " * " & Forms!frmUnderhållAdmin!kmbFilterMaskin & " * ") 
    And ((tblMaskindel.Maskindel) Like " * " & Forms!frmUnderhållAdmin!kmbFilterMaskindel & " * ");"
    I need to get the sql working before I add the option button. And I cant really get a hang of SQL in access yet, ive read som many diffrent thing how to convert it so head is spinning. Change to "'*'" or % or "%" for a wild card. But the wild card is a value from a combo box if that is to any help. There might be some better ways to add that maybe. now is it *[combo box]* to sort out values from the list.

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    One way to accommodate an unselected value from a combo is to use
    Code:
     = YourCombo Or YourCombo Is Null
    in your Where clause as this returns true for a null value in the combo

    The use of the wildcard (Normally * ) is very inefficient when used at the start of the criteria as it negates the use of indexes on that field.
    You won't notice on a small data set but you will on a large one, particularly when you have two or three fields.

    Also note the use of = rather than Like "*" & YourCombo .

    If the only values that can be found are in the combo then the use of the Like wildcard syntax is redundant.
    Last edited by Minty; 11-06-2018 at 02:51 AM.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Paowtrick is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Nov 2018
    Posts
    10
    Thanks for everything, the filter is very smooth now. Did it like this.

    Code:
    Private Sub vxlDatum_Click()Dim sSql As String
    Dim sWhere As String
    
    sSql = "SELECT tblUnderhåll.UnderhållsID, tblUnderhåll.Uppdrag, tblAvdelning.Avdelning, tblMaskin.Maskin, tblMaskindel.Maskindel, tblUnderhåll.[Datum Planerat], tblUnderhåll.[Svår uppgift]
    FROM tblMaskin 
    INNER JOIN ((tblMaskindel 
    INNER JOIN (tblAvdelning 
    INNER JOIN tblMaskinKomboID ON tblAvdelning.Avdelning_ID = tblMaskinKomboID.AvdelningsID) ON tblMaskindel.MaskindelsID = tblMaskinKomboID.MaskindelsID) 
    INNER JOIN tblUnderhåll ON tblMaskinKomboID.MaskinKomboID = tblUnderhåll.MaskinKomboID) ON tblMaskin.MaskinID = tblMaskinKomboID.MaskinID 
    WHERE tblAvdelning.Avdelning Like '*' & [Forms]![frmUnderhållAdmin]![kmbFilterAvdelning] & '*' 
    AND tblMaskin.Maskin Like '*' & [Forms]![frmUnderhållAdmin]![kmbFilterMaskin] & '*' 
    AND tblMaskindel.Maskindel Like '*' & [Forms]![frmUnderhållAdmin]![kmbFilterMaskindel] & '*'"
    
    Select Case Me.vxlDatum.Value
        Case 1
            sWhere = " AND tblUnderhåll.[Datum Planerat] <=Date()"
    
        Case 2
            sWhere = " AND tblUnderhåll.[Datum Planerat] >=Date()"
    
        Case 3
            sWhere = ""   ' No criteria
    
    End Select
    
    
    Me.Lista.RowSource = sSql & sWhere
    Me.Lista.Requery
    Thanks so much for the help.

    I'll be back

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-13-2018, 09:05 AM
  2. Filter listbox with date range criteria
    By vector39 in forum Modules
    Replies: 11
    Last Post: 06-02-2017, 07:04 AM
  3. Replies: 1
    Last Post: 05-21-2017, 12:31 AM
  4. Listbox filter
    By Brightspark98 in forum Forms
    Replies: 3
    Last Post: 01-31-2017, 12:39 AM
  5. Filter a form using a listbox
    By jrosen12 in forum Forms
    Replies: 3
    Last Post: 03-23-2015, 07:44 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums