Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    May 2008
    Posts
    24

    Unanswered: Option group and date

    Hello,
    I have an idea to made option group what will have two way of filter by
    date:
    1. list of job of present month (1.4.2011-30.4.2011)
    2. list of job of last month (1.3.2011-31.3.2011)

    I have long list of work with date and look best way to search it.
    Any help? Anybody try?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    To me an Option Group control is far from being the best tool for performing this kind of duties, moreover when dealing with dates that are subject to changes by definition.

    You'll have to modify both the number of option buttons and the code to process them as time goes by.
    Have a nice day!

  3. #3
    Join Date
    May 2008
    Posts
    24
    I believe to not be clear.
    My idea of option group is to have two option.
    Option 1 list of job of present month
    Option: 2 list of job of last month

    Date I write was just for example.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Something like this?
    Code:
    Private Sub Frame_DateRange_AfterUpdate()
    '
    ' Frame_DateRange is the name of the Option Group control (Default Value = 0).
    '
    ' It contains 3 Option Buttons:
    '
    ' - Option_AllDates     (Option Value = 0)
    ' - Option_LastMonth    (Option Value = 1)
    ' - Option_CurrentMonth (Option Value = 2)
    '
        Dim dteStart As Date
        Dim dteEnd As Date
        Dim dteLMStart As Date
        Dim dteLMEnd As Date
        
        dteStart = DateSerial(Year(Now), Month(Now), 1)
        dteEnd = DateAdd("m", 1, dteStart)
        dteEnd = DateAdd("s", -1, dteStart)
        dteLMStart = DateAdd("m", -1, dteStart)
        dteLMEnd = DateAdd("s", -1, dteStart)
        Select Case Me.Frame_DateRange.Value
            Case 0  ' All dates
                Me.Filter = ""
            Case 1  ' Last month
                Me.Filter = "[Some_Date] Between #" & Format(dteLMStart, "mm/dd/yyyy") & "# And #" & Format(dteLMEnd, "mm/dd/yyyy") & "# "
            Case 2  ' Current month
                Me.Filter = "[Some_Date] Between #" & Format(dteStart, "mm/dd/yyyy") & "# And #" & Format(dteEnd, "mm/dd/yyyy") & "# "
        End Select
        Me.FilterOn = True
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    May 2008
    Posts
    24
    Fantastic! Thanks you very much!
    I adapet to my data and tested. Only one wrong result.
    When I click Last month I receive list od job od present year (1.1.2011-31.3.2011) and not just last month. I go around and not see what is wrong

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Check the values of the Option buttons.
    2. Set a breakpoint on the line Me.FilterOn = True and check the value of Me.Filter at that moment. To check the value, select the expression with the mouse and type Shift+F9 (Quick watch).
    Have a nice day!

  7. #7
    Join Date
    May 2008
    Posts
    24
    >1. Check the values of the Option buttons.

    Correct, option Value 1

    >2. Set a breakpoint on the line Me.FilterOn = True and check the value of >Me.Filter at that moment. To check the value, select the expression with the >mouse and type Shift+F9 (Quick watch).

    Value= False
    And this in both case, last mont and corrent month

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I meant the value of Me.Filter, not the value of Me.FilterOn. Even easier: when the code stops on the breackpoint, open the immediate window (Control+G) and in the immediate windows type ? Me.Filter + Enter. This should print the value we're looking for on the next line.
    Have a nice day!

  9. #9
    Join Date
    May 2008
    Posts
    24
    It's give Null

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It cannot be Null if the second or the third section of the SELECT CASE instruction (CASE 2 OR CASE 3) was executed. Set a breack point on the SELECT CASE instruction (F9), restart the form and go step by step (F8) into the code to see what happens.
    Have a nice day!

  11. #11
    Join Date
    May 2008
    Posts
    24
    Hello,
    find in attach part of my datbase connected with Dynamically search multiple fields. With example will be definitve more simple.
    I add two different data filter.
    First one, Scegli le date is to filter jobs between two date, datainizio (start date) and datafine (end date). This work perfectly.
    Second possiblity is to filter by month, Scegli il mese.
    Two possiblity: filter by scorso (job of last month) and attuale (this month).
    Here are your code Sinndho. I made litte change but it give wrong answer and I not see why.
    If in option group select Scorso, so value 1, I receive all job of this year (1.1.2011-today) and not just job between 1.3.2011-30.3.2011.
    Also wrong "answer" for Attule, I receive nothing, but one job is enter and have date 1.4.2011.
    Can somebody help me?
    Attached Files Attached Files

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I cannot open the file you uploaded (archive corrupted).
    Have a nice day!

  13. #13
    Join Date
    May 2008
    Posts
    24
    Let try again.
    Attached Files Attached Files

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's the modified code:
    Code:
    Private Sub Frame97_AfterUpdate()
    '
    ' Frame97 is the name of the Option Group control (Default Value = 0).
    '
    ' It contains 3 Option Buttons:
    '
    ' - Option_AllDates     (Option Value = 0)
    ' - Option_LastMonth    (Option Value = 1)
    ' - Option_CurrentMonth (Option Value = 2)
    '
        Const c_strSelect As String = "SELECT [Lavoro Search].LavoroID, [Lavoro Search].Data, [Lavoro Search].Edizione, " & _
                                      "[Lavoro Search].TipoLavoro FROM [Lavoro Search] "
        Const c_strOrderBy As String = "ORDER BY [Lavoro Search].Data DESC;"
    
        Dim dteStart As Date
        Dim dteEnd As Date
        Dim dteLMStart As Date
        Dim dteLMEnd As Date
        Dim strFilter As String
        
        dteStart = DateSerial(Year(Now), Month(Now), 1)
        dteEnd = DateAdd("m", 1, dteStart)
        dteEnd = DateAdd("s", -1, dteEnd)
        dteLMStart = DateAdd("m", -1, dteStart)
        dteLMEnd = DateAdd("s", -1, dteStart)
        Select Case Me.Frame97.Value
            Case 0  ' All dates
                strFilter = ""
            Case 1  ' Last month
                strFilter = "WHERE [data] Between #" & Format(dteLMStart, "mm/dd/yyyy") & "# And #" & Format(dteLMEnd, "mm/dd/yyyy") & "# "
            Case 2  ' Current month
                strFilter = "WHERE [data] Between #" & Format(dteStart, "mm/dd/yyyy") & "# And #" & Format(dteEnd, "mm/dd/yyyy") & "# "
        End Select
        Me.SearchResults.RowSource = c_strSelect & strFilter & c_strOrderBy
        Me.SearchFor.SetFocus
    
    End Sub
    I did not realised that you were actually using a ListBox control. The Filter property is used with a Form or a SubForm (both have a RecordSet DAO class that has a Filter property). With a ListBox or a ComboBox you have to act on the RowSource property.

    I did not removed the 0 (All dates) case but it will never be used as there is no Option Button with that value.

    Never change the "mm/dd/yyyy" format when using date with SQL (except in the Query Builder): it's the only format Access and its SQL engine use whatever the international settings of Windows can be. That's the reason why I used the DateSerial() function to compute the initial date: it's the only function that garantee a correct date independently of the local date format (I also live in a country whit a "dd/mm/yyyy" date format).
    Have a nice day!

  15. #15
    Join Date
    May 2008
    Posts
    24
    Thanks for all.
    I just copy paste it, but for some reason it give zero result in both case, last month and actual month. Also I see that after this Reset now work any more.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •