Week at a glance calendar

[jh3016]

I have a subform that I would like to display the information as a Week-At-A-Glance. My question is two-fold:

1. I would do a query that would ask me the beginning date and then I want the query to display the next 5 days.

2. I want to create a report that would list the 5 days across the page (like a calendar).

The report should have the following fields across the top as column headings: DateTaken, CodeTaken, HrsTaken.

The report should have EEName for the rows.

Is this possible to do? Do you need more info? Thanks in advance.

 

[BillPower]

Hi Jane,

If you would like to send your latest DB with a graphic explanation of what you want (like you did before), will attempt a solution and post any suggestions here.

billpower@cwcom.net

Bill

 

[jh3016]

You're too kind. I'll send to you shortly. Thanks.

 

[judgehopkins]

If you want to see an add-in that prompts you for a date range for your report, go to the link below. Ricky Hicks has a download on his 12 March 03 post. You must register with the site before you can download it. It is in A97, but if you have a later version you can convert it.

You may be able to finagle the code to get it to print just the current work week. (See below for my try.)

(Note from Access Help file: The format of the return value for DateAdd is determined by Control Panel settings, not by the format that is passed in date argument.)

http://www.utteraccess.com/forums/access/access2773.html

For example, for the code for choosing a week, Hicks has:

Private Sub cmdThisWeek_Click()
    Select Case DatePart("w", Date, vbSunday)
        Case 1
            txtFrom = Date
            txtTo = DateAdd("d", 6, Date)
        Case 2
            txtFrom = DateAdd("d", -1, Date)
            txtTo = DateAdd("d", 5, Date)
        Case 3
            txtFrom = DateAdd("d", -2, Date)
            txtTo = DateAdd("d", 4, Date)
        Case 4
            txtFrom = DateAdd("d", -3, Date)
            txtTo = DateAdd("d", 3, Date)
        Case 5
            txtFrom = DateAdd("d", -4, Date)
            txtTo = DateAdd("d", 2, Date)
        Case 6
            txtFrom = DateAdd("d", -5, Date)
            txtTo = DateAdd("d", 1, Date)
        Case 7
            txtFrom = DateAdd("d", -6, Date)
            txtTo = Date
    End Select
End Sub

which I changed to just handle a Monday-Friday work week:

Private Sub cmdWorkWeek_Click()
    Select Case DatePart("w", Date, vbMonday)
        Case 1 'Monday
            txtFrom = Date
            txtTo = DateAdd("d", 4, Date)
        Case 2 'Tuesday
            txtFrom = DateAdd("d", -1, Date)
            txtTo = DateAdd("d", 3, Date)
        Case 3 'Wednesday
            txtFrom = DateAdd("d", -2, Date)
            txtTo = DateAdd("d", 2, Date)
        Case 4 'Thursday
            txtFrom = DateAdd("d", -3, Date)
            txtTo = DateAdd("d", 1, Date)
        Case 5 'Friday
            txtFrom = DateAdd("d", -4, Date)
            txtTo = Date
      End Select
End Sub

"In America, anyone can become president. That's one of the risks you take." - Adlai Stevenson

 

[judgehopkins]

Jane, I know how to answer part of your first question. In your query, make five columns: DateYouInput, then the next five days using something like this (1 through 5):

Expr1: DateAdd("d",1,[DateYouInput])

Hope this gets you started!






"In America, anyone can become president. That's one of the risks you take." - Adlai Stevenson

 

[judgehopkins]

Jane, I found this at:

http://www.experts-exchange.com/Databases/MS_Access/Q_20655157.html

SELECT OrderTable.Order, OrderTable.OrderDate, CalendarTable.Period
FROM OrderTable, CalendarTable
WHERE (((OrderTable.OrderDate)>=[monthstart] And (OrderTable.OrderDate)<=[monthend]));

Also, you might want to look at

http://beta.experts-exchange.com/Databases/MS_Access/Q_20178645.html#6456074

"In America, anyone can become president. That's one of the risks you take." - Adlai Stevenson

 

[jh3016]

Thanks a bunch. This gets me started.

 

[judgehopkins]

I would like to see what you came up with when you complete this part of your project.

If it's not too much trouble, post your code back here!

I've learned more about Access on the Tek-Tips forum than any other source!

"The nice thing about being a celebrity is that when you bore people, they think it's their fault." - Henry Kissinger