none
Excel RRS feed

  • Question

  • I am an intern for my uncle, and I am trying to learn basic/advanced techniques for Microsoft office.

    He has a long list of tasks he accomplished each day with a given date in Excel (which I have an example of pictured). I was wondering if I could format the information so it just shows the dates, and if the date is clicked on, the list of tasks pops up for that given day either in a text box or table.

    If this is possible, is there a way to automate it so it can create a new date and area/popup table each day so it fits his convenience. I'd assume that I would have to use a macro to accomplish this, but I would like to try my best to avoid this.

    I'd like to stay in excel for this, but if there is another Microsoft Office application that is better suited for what I need, I will be willing to change.

    Friday, May 26, 2017 12:30 PM

All replies

  • This is something you want to do in Excel?  You posted in a forum devoted to Microsoft Access, not Excel.  This would be very easy to do in Access, but if you want to do it with Excel you should probably post to an Excel forum.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, May 26, 2017 12:49 PM
  • My apologies. I had been playing around with both Excel and Access.

    How could I do this in Access? I don't know how to format the fields to allow multiple lines of "Things done today" Should I set up multiple tables?

    What would be the best to do it?

    Friday, May 26, 2017 2:21 PM
  • There are many ways to do this. Use a form bound to the Tasks table. In the form header put an unbound combobox with a RowSource like:

    SELECT DISTINCT TaskDate FROM tblTasks ORDER BY TaskDate;

    Then in the After Update event of the combobox use clode like:

    Me.Fillter = "TaskDate = " & Me.cboDatelist

    DoCmd.RunCommand acCmdApplyFilterSort

    So when a date is select from the combo, the form filters for tasks for that date.


    Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

    Friday, May 26, 2017 3:45 PM
  • How could I do this in Access? I don't know how to format the fields to allow multiple lines of "Things done today" Should I set up multiple tables?

    What would be the best to do it?

    If you included a picture in your original post, I didn't see it, so I'm just giving general outlines.  But I would expect that you would have a table of tasks with a structure along these lines:

        Tasks
        --------
        TaskID (autonumber, PK)
        TaskDate (date/time, required, indexed (duplicates OK))
        TaskDescription
        Completed (boolean)  (if you want to track this)

    Then you could have an unbound form with text boxes for a "from date" and a "to date", and a list box with a RowSource query that selects just the unique TaskDate values between those dates from the Tasks table:

    SELECT DISTINCT TaskDate FROM Tasks
    WHERE TaskDate Between [Forms]![frmTaskDates]!txtFromDate] And [Forms]![frmTaskDates]![txtToDate]


    You could then create an AfterUpdate event procedure for the list box to open a popup form in continuous-forms view that would show the details of the tasks that are scheduled on the  selected date.  For example, the procedure might look like this:

    Private Sub lstTaskDates_AfterUpdate()
    
        If Not IsNUll(Me.lstTaskDates) Then
           DoCmd.OpenForm "frmTaskList", WhereCondition:="TaskDate = #" & Me.lstTaskDates & "#"
        End If
    
    End Sub

    The continuous form named "frmTaskList" could have the table Task as its RecordSource, or its RecordSource could be a query that selects the desired fields from all records in Tasks.  The OpenForm call in the above event procedure will filter it to just the tasks on the selected date.

    There are other ways to do this, of course, and I may not have correctly understood what you need, but this is one simple approach.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, May 26, 2017 4:06 PM
  • Hi LoganAccess,

    you had mentioned that,"He has a long list of tasks he accomplished each day with a given date in Excel (which I have an example of pictured). I was wondering if I could format the information so it just shows the dates, and if the date is clicked on, the list of tasks pops up for that given day either in a text box or table.

    If this is possible, is there a way to automate it so it can create a new date and area/popup table each day so it fits his convenience. I'd assume that I would have to use a macro to accomplish this, but I would like to try my best to avoid this."

    so overall it looks like you want to manage the tasks.

    I suggest you to use inbuilt Access Template called "Task Management".

    you will get many functionalities already developed to add new tasks , view tasks, find tasks, manage tasks, export data. I think that this template can fulfill your all the requirements and also provide some new and extra features and functionalities that you would like to use with attractive design.

    if you want to work with SharePoint then there also a template available that work with SharePoint.

    Reference:

    Video: Use the Desktop Task Management Database Template

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 29, 2017 1:50 AM
    Moderator
  • Hi LoganAccess,

    is your issue is solved?

    I can see that you did not responded to this thread after creating this thread.

    if you issue is solved then try to update the status of this thread and post your solution and mark it as an answer.

    if your issue is still exist then try to refer the suggestion given by community members. it can solve your issue.

    if you have any further questions then let me know about that.

    I will try to provide you further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 28, 2017 9:44 AM
    Moderator