Change in Query - very difficult

[Mike Hughes]

I'm building this for my wife and she keeps changing what she needs.........so........

SELECT tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.REPORTS, Avg(DateDiff("d",[DATE RECEIVED],[DATE OF DECISION])) AS DAYS, DateAdd("yyyy",-1,Date())+1 & " - " & Date() AS [REPORT PERIOD], Format(Int([Days]/365),"00-") & Format(Int((([Days] Mod 365)/30)),"00-") & Format(([Days]-(Int([Days]/365)*365+Int(([Days] Mod 365)/30))*30),"00") AS MyDays
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.[DATE OF DECISION])>=DateAdd("yyyy",-1,Date())+1))
GROUP BY tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.REPORTS;

If I run this query today the REPORT PERIOD would come back as
03/13/2002 - 03/12/2003


I would like to have the query changed to return the REPORT PERIOD if I ran it today
of 04/01/2002 – 03/31/2003.

In other words,

The first date should be the FIRST DAY of the Month, of the Month that follows the Month the report is run, less 1 year, 04/01/2002.

The second date should be the LAST DAY of the Month in which the report is run. 03/31/2003.

Can someone tell me if this can be done and if it can would you please give an example of what the query would be.
Thanks

 

[jfgambit]

Mike:

Why not change the Query to Prompt for the Dates, that way it can be run for anytime period. In other words change the Criteria to be "Between [What is first Date?] and [What is Last Date?]". When the Query is run it will prompt the user for the dates they wish to view. Then you could run it for a year, 6 month, 3 month, bi-weekly, weekly, (etc.) period.

This can also be done in a form by having two unbound textboxes, the criteria would then be "Between [Forms]![FormName]![Textbox1] and [Forms]![FromName]![Textbox2]". You could then create a command button to run the query.

HTH

 

[Mike Hughes]

That might be the only way that I can get what I want , thanks

 

[Paul_Bricker]

Mike, this should get you close.

WHERE (((tblAPPLICATIONS.[DATE OF DECISION]) Between DateSerial(Year(Date())-1, Month(Date()) + 1,1) And DateSerial(Year(Date()), Month(Date()) + 1,1)-1

Paul

 

[Mike Hughes]

Paul,
I tried this and I a get :

Syntax error missing operator in query expression:
WHERE (((tblAPPLICATIONS.[DATE OF DECISION]) Between DateSerial(Year(Date())-1, Month(Date()) + 1,1) And DateSerial(Year(Date()), Month(Date()) + 1,1)-1
GROUP BY tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.REPORTS;

With the word GROUP highlighted

Mike

 

[Paul_Bricker]

Try pasting this back in.
WHERE (((tblAPPLICATIONS.[DATE OF DECISION]) Between DateSerial(Year(Date())-1, Month(Date()) + 1,1) And DateSerial(Year(Date()), Month(Date()) + 1,1)-1))
GROUP BY tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.REPORTS;

I added a couple )) at the end of the Between statement. See if that does it.

Paul

 

[Jon K]

First day of next month less 1 year:-
DateSerial(Year(Date())-1,Month(Date())+1,1)

Last day of current month:-
DateSerial(Year(Date()),Month(Date())+1,0)