Help me understand this criteria

[Chip Piehl]

Hello

A while back someone posted this solution for a search
criteria...(I believe it was Steve Schapel)

Date()-Weekday(Date())+1

What criteria did was take the vaule from your computer
system's clock/date and showed a range of dates of the
days only in that week.

I was wondering if anyone knew how to modify this so that
it prompts me for a date and then shows a range of dates
of the days in the week for the date that I supplied.

For example...

Monday = 1/5
Tuesday = 1/6
Wednesday = 1/7
Thursday = 1/8
Friday = 1/9

So the critera would prompt me with a question like [What
date?].

And if I put in 1/5 it would give me the range of all
dates from 1/5 to 1/9.

If I put in 1/7 it would still give me the range of all
dates from 1/5 to 1/9.

Can someone please help me?

Thanks!!

 

[Kelvin]

Your criteria will give you the current week as the criteria. Date()
returns the current date. Weekday(Date()) will return which day of the week
from 1 to 7. If today was wednesday (4), this criteria takes todays date
subtracts 4, this will always give you the end of the previous week, then
adds back one which will give the first day of the current week. If you
want to match a date with a specific week use the Datepart function to get
the week of the dates in your data then use datepart again to return the
week of the date that is entered. Setting the criteria for the datepart in
the query to match the datepart of what was entered will give you the
results for that entire week.

Kelvin

Hello

A while back someone posted this solution for a search
criteria...(I believe it was Steve Schapel)

Date()-Weekday(Date())+1

What criteria did was take the vaule from your computer
system's clock/date and showed a range of dates of the
days only in that week.

I was wondering if anyone knew how to modify this so that
it prompts me for a date and then shows a range of dates
of the days in the week for the date that I supplied.

For example...

Monday = 1/5
Tuesday = 1/6
Wednesday = 1/7
Thursday = 1/8
Friday = 1/9

So the critera would prompt me with a question like [What
date?].

And if I put in 1/5 it would give me the range of all
dates from 1/5 to 1/9.

If I put in 1/7 it would still give me the range of all
dates from 1/5 to 1/9.

Can someone please help me?

Thanks!!

 

[Chip]

Do you mean to place "datepart()" in place of "date()"?

Thanks,
-Chip

-----Original Message-----
Your criteria will give you the current week as the criteria. Date()
returns the current date. Weekday(Date()) will return which day of the week
from 1 to 7. If today was wednesday (4), this criteria takes todays date
subtracts 4, this will always give you the end of the previous week, then
adds back one which will give the first day of the current week. If you
want to match a date with a specific week use the Datepart function to get
the week of the dates in your data then use datepart again to return the
week of the date that is entered. Setting the criteria for the datepart in
the query to match the datepart of what was entered will give you the
results for that entire week.

Kelvin

Hello

A while back someone posted this solution for a search
criteria...(I believe it was Steve Schapel)

Date()-Weekday(Date())+1

What criteria did was take the vaule from your computer
system's clock/date and showed a range of dates of the
days only in that week.

I was wondering if anyone knew how to modify this so that
it prompts me for a date and then shows a range of dates
of the days in the week for the date that I supplied.

For example...

Monday = 1/5
Tuesday = 1/6
Wednesday = 1/7
Thursday = 1/8
Friday = 1/9

So the critera would prompt me with a question like [What
date?].

And if I put in 1/5 it would give me the range of all
dates from 1/5 to 1/9.

If I put in 1/7 it would still give me the range of all
dates from 1/5 to 1/9.

Can someone please help me?

Thanks!!

.

 

[Kelvin]

Instead of putting the criteria under the date column, create a new field at
the end of your query.

WeekOfDate: datepart("ww",[myDate])

Then for the criteria of this column set to

datepart("ww",[Enter Search Date])

This will convert the dates in your table to the corresponding week. Then
search these weeks to see which ones match the week of the date that you
entered. If your dates span several years you can also add another field to
match the year.

YearOfDate: datepart("yyyy",[myDate]) with a criteria of
datepart("yyyy",[Enter Search Date])

Kelvin

 

[John Spencer (MVP)]

Try something like

[Enter Date] - Weekday([Enter Date]) + 1

I'm not sure what you are doing with this, but it should work. It would seem
that at least part of the criteria is missing since this will give you the start
date and if you want to get a week you need an end date also. That would
probably be something like

Between [Enter Date] - Weekday([Enter Date]) + 2
And [Enter Date] - Weekday([Enter Date]) + 6

That would usually get Monday to Friday. If you want Sunday to Saturday change
+2 to +1 and +6 to +7

Hello

A while back someone posted this solution for a search
criteria...(I believe it was Steve Schapel)

Date()-Weekday(Date())+1

What criteria did was take the vaule from your computer
system's clock/date and showed a range of dates of the
days only in that week.

I was wondering if anyone knew how to modify this so that
it prompts me for a date and then shows a range of dates
of the days in the week for the date that I supplied.

For example...

Monday = 1/5
Tuesday = 1/6
Wednesday = 1/7
Thursday = 1/8
Friday = 1/9

So the critera would prompt me with a question like [What
date?].

And if I put in 1/5 it would give me the range of all
dates from 1/5 to 1/9.

If I put in 1/7 it would still give me the range of all
dates from 1/5 to 1/9.

Can someone please help me?

Thanks!!

 

[Chip]

Thanks Kelvin!!

I solved our problem thanks to you Kelvin!

They are carrying me around on their shoulders. There is
a parade in my honor! Everyone is cheering here!

Thank you!!

-----Original Message-----
Instead of putting the criteria under the date column, create a new field at
the end of your query.

WeekOfDate: datepart("ww",[myDate])

Then for the criteria of this column set to

datepart("ww",[Enter Search Date])

This will convert the dates in your table to the corresponding week. Then
search these weeks to see which ones match the week of the date that you
entered. If your dates span several years you can also add another field to
match the year.

YearOfDate: datepart("yyyy",[myDate]) with a criteria of
datepart("yyyy",[Enter Search Date])

Kelvin

Do you mean to place "datepart()" in place of "date()"?

Thanks,
-Chip

.