I need a formula to return data for the month based on week ending criteria

Hello,

I have a query that is based on date criteria.  For example if someone enters the date 10/12/03 it will bring all records from 1/1/2000 - 10/12/03.  I would like to create a formula so if someone enters a date, it will bring records for that month.  For example if someone enters the date 10/12/03 records will show for the month of October.

Any help would be appreciated.  Thank you!
smiethAsked:
Who is Participating?
 
Mustak_ShaikhConnect With a Mentor Commented:
select datediff("h","01/" & month(@dateparm) & "/" & year(@dateparm),myfield)
 from myTable

I think that is the thing which u r looking for.
Regards,
Mustak_Shaikh
0
 
lauszCommented:
If you are using sql server ..

create proc test (@datex datetime)
as

select * from yourtable
where month(yourdatefield ) = month(@datex)
and year(yourdatefield ) = year(@datex)


then ..

exec test '2003-10-01' --will give you all information for this month/year

0
 
smiethAuthor Commented:
Sorry - I should have included that information.  I'm using Access 2000.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Benjamin_LukCommented:
Try this:

Select * From myTable Where Format([myField],"MMM-YY") = Format ([Please Enter Date Format MM/DD/YYYY],"MMM_YY");

This will help you

Regards

Ben
0
 
Benjamin_LukCommented:
It should be:

Select * From myTable Where Format([myField],"MMM-YY") = Format ([Please Enter Date Format MM/DD/YYYY],"MMM-YY");

Sorry for type error

Regards

Ben
0
 
smiethAuthor Commented:
Hi there,

I don't think that I explained what I need to do correctly.  I have a query that is already based on date criteria.  I need to setup a formula within that query that will show the hours for the month selected.   So if they enter 10/30/03 for the criteria, the formula will know to bring the hours field over for those hours based on the month of October.  I hope that makes sense.


Thanks,

Susan
0
 
smiethAuthor Commented:
Thanks!  Your formula got me back on track.  I used a modified version and it worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.