Dates

WackyWaterGuy · Jun 3, 2003

HI there! Just a quick question that i was hoping someone could help me out with. I have a form, and at the top of the form (in a text box or something of the sort) I would like to post the date range for a week (For example - June 4 - 11, 2003). Not only that, but I would like these weeks to go from Wednesday to Tuesday, and then have them update themselves every wednesday, so that it displays the week ahead. Is there any way that this can be done. Access Help doesnt do a great job of covering date ranges - or how to have them automatically update. Any help would be greatly appreciated!

Thank you!

WWG

R. Hicks · Jun 4, 2003

I hope I understand your question.

The format will have to be different due to the range can fall into two different months.

Here is an example that will return ... May 28, 2003 - June 03, 2003 based on todays date (06/03/2003)

I assume this is for display purposes only ...
Place the following in the Control Source of your txtbox:

=Format(Date()-Weekday(Date(),4)+1,"mmmm dd"", ""yyyy") & " - " & Format(Date()-Weekday(Date(),4)+7,"mmmm dd"", ""yyyy")

The above will return the date range based on the system date of the computer ... starting on Wednesday and endin on Tuesday.

HTH
RDH

raskew · Jun 4, 2003

Here’s a little function. Copy/paste it to a new module.

Code:

Function LastNDay2(byVal pMyDate As Date, pLastNDay As Integer) As Date
'*******************************************
'Name:      LastNDay 2(Function)
'Purpose:   Display the start date of the week in which pMyDate falls.
'Reference: [url]http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=46623[/url]
'                   [url]http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=48771[/url]
'Goal:      Six or less working lines of code
'Note:      pLastNDay: 1 (Sunday) to 7 (Saturday)
'Inputs:    from debug (immediate) window
'           ? lastNday2(#4/25/03#, 2)<enter>
'Output:    4/21/03
'*******************************************

LastNDay2 = IIf(WeekDay(pMyDate) = pLastNDay, pMyDate, pMyDate - (WeekDay(pMyDate) + IIf(WeekDay(pMyDate) <= pLastNDay, 7, 0) - pLastNDay))

End Function

Once done, try the following in the debug (immediate) window:

Note: This example uses Medium-Date throughout to avoid regional date format confusion.

MyDate = #03-Jun-2003# <enter>
fmt = “dddd dd-mmm-yyyy” <enter>
x = LastNDay2(MyDate, 4) <enter>
y = LastNDay2(MyDate, 4) + 6 <enter>
? “Between “ & format(x, fmt) & “ and “ & format(y, fmt) <enter>
Between Wednesday 28-May-2003 and Tuesday 03-Jun-2003

Hopefully this will give you some ideas. The date range will upgrade based on MyDate. Additionally, you can specify any day of the week [Sunday] = 1 through [Saturday] = 6 to start your week.

Do the same thing with: MyDate = #04-Jun-2003# ...and the result will be:
Between Wednesday 04-Jun-2003 and Tuesday 10-Jun-2003

HTH

Bob

WackyWaterGuy · Jun 4, 2003

THank you very much to both of you! It really did help!

I really appreciate it!

Thanks!

WWG

Dates

WackyWaterGuy

Registered User.

R. Hicks

AWF VIP

raskew

AWF VIP

WackyWaterGuy

Registered User.

Similar threads

Users who are viewing this thread