Solved

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

Posted on 2003-10-29
7
298 Views
Last Modified: 2006-11-17
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!
0
Comment
Question by:smieth
7 Comments
 
LVL 6

Expert Comment

by:lausz
ID: 9645013
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
 

Author Comment

by:smieth
ID: 9645482
Sorry - I should have included that information.  I'm using Access 2000.
0
 
LVL 8

Expert Comment

by:Benjamin_Luk
ID: 9648102
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 8

Expert Comment

by:Benjamin_Luk
ID: 9648108
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
 

Author Comment

by:smieth
ID: 9650521
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
 
LVL 1

Accepted Solution

by:
Mustak_Shaikh earned 125 total points
ID: 9650850
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
 

Author Comment

by:smieth
ID: 9654042
Thanks!  Your formula got me back on track.  I used a modified version and it worked perfectly.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
export Oracle diagram from Oracle DB including VIEWS 8 130
query returning everything 11 107
Fixing Non-Standard characters in text 8 104
Trigger usage 2 69
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question