Specific Date Selection Query Help!

[alwayshouston]

Hi All!
I have a table in below format:
Dept_ID Expenses Date
ACCTING 12000000 6/30/2000
ACCTING 10000000 7/31/2000
ACCTING 20000000 8/31/2000
ACCTING 30000000 9/30/2000
ACCTING 50000000 10/31/2000
ACCTING 60000000 11/30/2000
ACCTING 70000000 12/31/2000
ACCTING 10000000 1/31/2001
......
......
......
ACCTING 20000000 12/31/2003

I would like to write a query that would give me an output based on either Quarterly basis or Semi Annual basis or Annual basis from the date that the user selected. When the query is executed it would ask for the parameter of selection of records on Quarterly or Yearly or Semi Annually. For example, if the user selects date of 7/31/2000 and input quarterly in the parameter, it would return following:
ACCTING 10000000 7/31/2000
ACCTING 50000000 10/31/2000
ACCTING 10000000 1/31/20001

If the users select date of 7/31/2000 and select Yearly, it would return the following:
ACCTING 10000000 7/31/2000
ACCTING 90000000 7/31/2001
ACCTING 70000000 7/31/2002
ACCTING 40000000 7/31/2003

How could I write a query that would give me above output. I tried using DateDiff("q",[Date]) function but it does not give the right output.
Any help or feedback is appreciated!

Thanks in Advance!!!!

 

[lespaul]

how does the user select the time frame parameter and enter the date?

Leslie

 

[alwayshouston]

User selects the time frame via a parameter. User also selects the periodic time frame(Quarterly or Yearly or Semi Annually) via a parameter. In summary, there are two parameters for the query:
First Parameter selects the Date
Second Parameter selects the time frame (quarterly, Annually, or semi-annually)

Thanks!

 

[lespaul]

do you mean that you have created forms that get this information or you just have the following in a query?

Where date between [Enter a Date] And TimeFrame = [Enter Time Frame]

If I was trying to get this information, I would create a form that allows the user to select a date and have a radio group (Quarterly, Semi-Annually, Annually) and a button. When the user presses the button, I would build the SQL dynamically depending on which radio button was selected.

HTH

Leslie

 

[alwayshouston]

Thanks for your prompt feedback! Leslie
I could create the dynamic SQL, but my table contains more than 80,000 records with different dates (starting from 1968 to 2004) and departments. It would be not efficient to create a dynamic SQL. Is there any function that I can use in SQL in which I can simply increment the output of the query by 3 months or 6 months or 1 year from a given specific date? (e.g. DateDiff function)
Thanks!

 

[lespaul]

What do you mean it wouldn't be efficient to dynamically build the SQL?

Create a form, add a control where the user can select/enter a date, add the radio group so the user can select the time frame, add a button. In the onClick event of the button:

[psuedocode]

if rgAnnually then
  SQL = "SELECT * FROM TblName WHERE SOMEDATE BETWEEN " + frmName.DateControl + " AND " + DateAdd(frmName.DateControl, y, 1)
if rgQuarterly then
  SQL = ""SELECT * FROM TblName WHERE SOMEDATE BETWEEN " + frmName.DateControl + " AND " + DateAdd(frmName.DateControl, q, 1)
if rgSemiAnnual then
  SQL = "SELECT * FROM TblName WHERE SOMEDATE BETWEEN " + frmName.DateControl + " AND " + DateAdd(frmName.DateControl, m, 6)

[psuedocode]

then run the query. (I'm not sure on the DateAdd - it may be a Delphi function, not Access, but I know there's an equivalent in Access)







Leslie

 

[alwayshouston]

Thanks for your prompt feedback!
Your solution does not work. It only gives me record within one year or semi annual or quarterly range. For example, if use below SQL, I get three records (within the range of a single quarter)
SQL = ""SELECT * FROM TblName WHERE SOMEDATE BETWEEN " + frmName.DateControl + " AND " + DateAdd("q",1,frmName.DateControl)
It does not give me a single record for each quarter as desired.
I hope my above description makes sense. Let me know if I am not clear on what I wanted to get.
Again THANKS!

 

[lespaul]

umm,that SQL doesn't do anything if your table isn't named TblName and your field isn't named SOMEDATE and your form isn't named frmName.

Did you check the Access help? Does the DateAdd function exist and is that the syntax?

Did you build a form? What are the control names? What is the OnClick code that you built? What are your table names and field names?

If you want me to solve this problem for you, you are going to have to supply a LOT more details beyond:

Your solution does not work.

I did not provide you with a solution, I provided you with an idea on how to solve your problem.

Leslie

 

[alwayshouston]

I apologize if I come across two harshly by stating solution rather than your idea. I did'nt mean that. By reading my post again I feel like that the situation that I have explained earlier was vague and unclear. Lets make the situation a bit easier/clearer and forget about all the forms. Let say we have a Dept_Expense table as below:
Dept_ID Expenses Date
ACCTING 12000000 6/30/2000
ACCTING 10000000 7/31/2000
ACCTING 20000000 8/31/2000
ACCTING 30000000 9/30/2000
ACCTING 50000000 10/31/2000
ACCTING 60000000 11/30/2000
ACCTING 70000000 12/31/2000
ACCTING 10000000 1/31/2001
......
......
......
ACCTING 20000000 12/31/2003
How would you write a query that would simply give me an output of every three months starting from 7/31/2000 (adding three months and skipping the months in between). Below is the ideal output:
ACCTING 10000000 7/31/2000
ACCTING 50000000 10/31/2000
ACCTING 10000000 1/31/2001
ACCTING 60000000 4/30/2001
......
If I get the query working, I can work the problem easily in my forms.
Thanks!