Find next Monday after given date

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello All

I need an expression that will give me the earliest date that is after
[res_date] and
which falls on a day of the week that is specified at runtime.

So given [res_date] values of:

18 November 2005
30 November 2005
28 December 2005

and a runtime value (for the day of the week) of 2 (i.e. I want Mondays) the
expression should return:

21 November 2005
5 December 2005
2 January 2005

I have tried all sorts but cannot get this right.
Hope someone can help.

Many thanks
Leslie Isaacs
 
TBDayAfterRes:[res_date] + (7-WeekDay([res_date])) + [Enter Numeric Day Of
Week]

When the prompt "Enter Numeric Day Of Week" appears, the user needs to enter
a number 1 through 7 in order to get a meaningful date. As you can imagine,
this method is quite dangerous, as the user is just as apt to enter the
string "Monday" (which will make your query bomb) as to realize it's the
number "2" that's needed.

The finer way to do this is to put a combo box on a form. Use the wizard, and
do the following:
Click on "I will type in the values that I want" (Next)

Columns: 2

In Col1 and Col2, type in 7 lines, col1 could be "1" thru "7" (no quotes),
and col2 could be "Sunday" through "Saturday" (no quotes) (next)

As Col1 is the numeric column, and the info that's necessary to drive your
query, accept Col1 as the binding column (next)

Type in a user-friendly introduction ("Enter Weekday", for example) (Finish)

One simple tweak: In the combo box's Property sheet, change the Column Width
property to read 0";1"

This will prevent the user from seeing the numbers 1 through 7, only the days
of the week. Access, however, will pick up the number.

Give the combo box an appropriate name and save your form, giving it an
appropriate name.

In your query, then, change the above

TBDayAfterRes:[res_date] + (7-WeekDay([res_date])) + [Enter Numeric Day Of
Week]

to read

TBDayAfterRes:[res_date] + (7-WeekDay([res_date])) + [Forms]!
[YourFormNameHere]![ComboBoxNameHere].Value

In the combo box's AfterUpdate event, you will need to put code to control
your query execution.

HTH
p.s. If the user runs this on a Sunday or Monday, the next Monday (7-8 days
later) will be selected. If you want the same/next day instead, the formula
will be somewhat more complicated.

Leslie said:
Hello All

I need an expression that will give me the earliest date that is after
[res_date] and
which falls on a day of the week that is specified at runtime.

So given [res_date] values of:

18 November 2005
30 November 2005
28 December 2005

and a runtime value (for the day of the week) of 2 (i.e. I want Mondays) the
expression should return:

21 November 2005
5 December 2005
2 January 2005

I have tried all sorts but cannot get this right.
Hope someone can help.

Many thanks
Leslie Isaacs
Click to expand...
 
Hello "OfficeDev18 via AccessMonster.com"

Many thanks for that - it works perfectly (and I have learned a bit too!)

Les





OfficeDev18 via AccessMonster.com said:
TBDayAfterRes:[res_date] + (7-WeekDay([res_date])) + [Enter Numeric Day Of
Week]

When the prompt "Enter Numeric Day Of Week" appears, the user needs to
enter
a number 1 through 7 in order to get a meaningful date. As you can
imagine,
this method is quite dangerous, as the user is just as apt to enter the
string "Monday" (which will make your query bomb) as to realize it's the
number "2" that's needed.

The finer way to do this is to put a combo box on a form. Use the wizard,
and
do the following:
Click on "I will type in the values that I want" (Next)

Columns: 2

In Col1 and Col2, type in 7 lines, col1 could be "1" thru "7" (no quotes),
and col2 could be "Sunday" through "Saturday" (no quotes) (next)

As Col1 is the numeric column, and the info that's necessary to drive your
query, accept Col1 as the binding column (next)

Type in a user-friendly introduction ("Enter Weekday", for example)
(Finish)

One simple tweak: In the combo box's Property sheet, change the Column
Width
property to read 0";1"

This will prevent the user from seeing the numbers 1 through 7, only the
days
of the week. Access, however, will pick up the number.

Give the combo box an appropriate name and save your form, giving it an
appropriate name.

In your query, then, change the above

TBDayAfterRes:[res_date] + (7-WeekDay([res_date])) + [Enter Numeric Day Of
Week]

to read

TBDayAfterRes:[res_date] + (7-WeekDay([res_date])) + [Forms]!
[YourFormNameHere]![ComboBoxNameHere].Value

In the combo box's AfterUpdate event, you will need to put code to control
your query execution.

HTH
p.s. If the user runs this on a Sunday or Monday, the next Monday (7-8
days
later) will be selected. If you want the same/next day instead, the
formula
will be somewhat more complicated.

Leslie said:
Hello All

I need an expression that will give me the earliest date that is after
[res_date] and
which falls on a day of the week that is specified at runtime.

So given [res_date] values of:

18 November 2005
30 November 2005
28 December 2005

and a runtime value (for the day of the week) of 2 (i.e. I want Mondays)
the
expression should return:

21 November 2005
5 December 2005
2 January 2005

I have tried all sorts but cannot get this right.
Hope someone can help.

Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
Back
Top