Query week, month, quarter

[Guest]

I am trying to figure out the code to add to my where clause a statement that
only selects those records that are between a specific date.
Here is what i have for the current year:
WHERE (((Year([DateWorked]))=2004))

But i want to set it up so its not equal to 2004 but to the current year
(I'm guessing this somehow uses DatePart, but i do not know how.

I also want to do this for week, month, and quarter. Here is what i tried
for month, but this did not work:
WHERE (((Month([DateWorked]))=(Month(Date()))

 

[Rick B]

WHERE (((Year([DateWorked]))=Year(Date())))

 

[Guest]

I think you had one to many ( in there, so i took one out. However, how come
this does not work for month?
((Month([DateWorked]))=Month(Date()))

And this for week:
((Week([DateWorked]))=Week(Date()))

And this for quarter:
((Quarter([DateWorked]))=Quarter(Date()))

WHERE (((Year([DateWorked]))=Year(Date())))

I am trying to figure out the code to add to my where clause a statement that
only selects those records that are between a specific date.
Here is what i have for the current year:
WHERE (((Year([DateWorked]))=2004))

But i want to set it up so its not equal to 2004 but to the current year
(I'm guessing this somehow uses DatePart, but i do not know how.

I also want to do this for week, month, and quarter. Here is what i tried
for month, but this did not work:
WHERE (((Month([DateWorked]))=(Month(Date()))

 

[John Vinson]

I am trying to figure out the code to add to my where clause a statement that
only selects those records that are between a specific date.
Here is what i have for the current year:
WHERE (((Year([DateWorked]))=2004))

But i want to set it up so its not equal to 2004 but to the current year
(I'm guessing this somehow uses DatePart, but i do not know how.

I also want to do this for week, month, and quarter. Here is what i tried
for month, but this did not work:
WHERE (((Month([DateWorked]))=(Month(Date()))

Begging the question (discussed in another thread) of what the name of
the DateWorked field in your table actually IS...

Year([DateWorked]) = Year(Date())
and
Month([DateWorked]) = Month(Date())

should work fine as criteria.

DatePart("q", [DateWorked]) = [Enter quarter:] AND Year([DateWorked])
= Year(Date())

will prompt the user for which quarter of the current year they want
to see.

[DateWorked] BETWEEN [Enter start date:] AND [Enter end date:]

gives you complete flexibility on the date range.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

OK Thank you very much for all your help. You have been of extreme use for
me, helpingme construct this app. One final question deals with the week. The
paramter to enter the quarter is fine, however for week. I want to use BETWEEN

So DateWorked Between (First day of the current week) and Date()

I am trying to figure out the code to add to my where clause a statement that
only selects those records that are between a specific date.
Here is what i have for the current year:
WHERE (((Year([DateWorked]))=2004))

But i want to set it up so its not equal to 2004 but to the current year
(I'm guessing this somehow uses DatePart, but i do not know how.

I also want to do this for week, month, and quarter. Here is what i tried
for month, but this did not work:
WHERE (((Month([DateWorked]))=(Month(Date()))

Begging the question (discussed in another thread) of what the name of
the DateWorked field in your table actually IS...

Year([DateWorked]) = Year(Date())
and
Month([DateWorked]) = Month(Date())

should work fine as criteria.

DatePart("q", [DateWorked]) = [Enter quarter:] AND Year([DateWorked])
= Year(Date())

will prompt the user for which quarter of the current year they want
to see.

[DateWorked] BETWEEN [Enter start date:] AND [Enter end date:]

gives you complete flexibility on the date range.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

OK Thank you very much for all your help. You have been of extreme use for
me, helpingme construct this app. One final question deals with the week. The
paramter to enter the quarter is fine, however for week. I want to use BETWEEN

So DateWorked Between (First day of the current week) and Date()

There is no Week function like Month. You can get tricky using the VBA
dateadd function to find the most recent Sunday:

BETWEEN DateAdd("d", 1 - DatePart("w", Date())) AND Date()


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps