How to run a date based query for many months

Hello,

I have one query based on a parameter "MyDate".
    I enter a full date ( dd/mm/yyyy french format but no matter )
    and the query result is expressed by the month ( yyyy_mm)
      eg : i enter   25/12/2009    and the result is expressed in
2009_12

I have to run this query for n months before the entered date and 2
months after this date.
Is it possible to get a SQL code for this ?

Can you help me ?

Thanks by anticipation.

0
le
4/6/2010 6:19:03 AM
access 16762 articles. 3 followers. Follow

9 Replies
1361 Views

Similar Articles

[PageSpeed] 6

It would help if you posted the SQL of your query.  Also, your requirement as 
stated is a bit vague.

If you enter 25/12/2009 and want 1 month before and 2 months after does that mean
== From 25/11/2009 to 25/02/2010
or
== FROM 01/11/2009 to 28/02/2010
or something else

Assuming the first result you can try:
Field: SomeDateField
Criteria: Between DateAdd("m",-1,[MyDate]) and DateAdd("m",2,[MyDate])

If the number of prior months varies, you need a second parameter.
Field: SomeDateField
Criteria: Between DateAdd("m",-[Number of Prior Months],[MyDate]) and 
DateAdd("m",2,[MyDate])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

le Nordiste wrote:
> Hello,
> 
> I have one query based on a parameter "MyDate".
>     I enter a full date ( dd/mm/yyyy french format but no matter )
>     and the query result is expressed by the month ( yyyy_mm)
>       eg : i enter   25/12/2009    and the result is expressed in
> 2009_12
> 
> I have to run this query for n months before the entered date and 2
> months after this date.
> Is it possible to get a SQL code for this ?
> 
> Can you help me ?
> 
> Thanks by anticipation.
> 
0
John
4/6/2010 1:35:21 PM
Query 1 :
SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
[madate])-6,1),"yyyy_mm") AS date_m6 INTO T_m6;

Query 2 :
SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
[madate])-4,1),"yyyy_mm") AS date_m4 INTO T_m4;

Query 3 :
SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
[madate])+4,1),"yyyy_mm") AS date_p4 INTO T_p4;

Final query :
SELECT T_m6.* FROM T_m6
UNION
SELECT T_m4.* FROM T_m4;
UNION
SELECT T_p4.* FROM T_p4;

and by typing 02/2/2010,  i get 3 recordset :  2009_08, 2009_10,
2010_06

Is it possible to write an UNION query with Query 1 to query 3, here
above ?
Something like :

SELECT MyAliases.* FROM (
SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
[madate])-6,1),"yyyy_mm"
UNION ALL
SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
[madate])-4,1),"yyyy_mm"
) AS MyAliases;

Thanks for your help.

0
le
4/6/2010 3:00:23 PM
I'm confused about what you are attempting to do.
You can write a union query like the one below to generate the dates.

I used a table with only a few records in it for performance reasons.

PARAMETERS MaDate  DateTime;
SELECT
FORMAT(DateSerial(Year([madate]),Month([maDate])-6,1),"YYYY_MM") as SomeDate
FROM TableA
UNION
SELECT
FORMAT(DateSerial(Year([madate]),Month([maDate])-4,1),"YYYY_MM")
FROM TableA
UNION
SELECT
FORMAT(DateSerial(Year([madate]),Month([maDate])+4,1),"YYYY_MM")
FROM TableA

Or you can generate date ranges.
PARAMETERS MaDate  DateTime;
SELECT DateSerial(Year(madate),Month(maDate)-6,1) as StartDate
, DateSerial(Year(madate),Month(maDate)-5,0) as EndDate
FROM TableA
UNION
SELECT DateSerial(Year(madate),Month(maDate)-4,1) as StartDate
, DateSerial(Year(madate),Month(maDate)-3,0) as EndDate
FROM TableA
UNION
SELECT DateSerial(Year(madate),Month(maDate)+4,1) as StartDate
, DateSerial(Year(madate),Month(maDate)+5,0) as EndDate
FROM TableA


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

le Nordiste wrote:
> Query 1 :
> SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
> [madate])-6,1),"yyyy_mm") AS date_m6 INTO T_m6;
> 
> Query 2 :
> SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
> [madate])-4,1),"yyyy_mm") AS date_m4 INTO T_m4;
> 
> Query 3 :
> SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
> [madate])+4,1),"yyyy_mm") AS date_p4 INTO T_p4;
> 
> Final query :
> SELECT T_m6.* FROM T_m6
> UNION
> SELECT T_m4.* FROM T_m4;
> UNION
> SELECT T_p4.* FROM T_p4;
> 
> and by typing 02/2/2010,  i get 3 recordset :  2009_08, 2009_10,
> 2010_06
> 
> Is it possible to write an UNION query with Query 1 to query 3, here
> above ?
> Something like :
> 
> SELECT MyAliases.* FROM (
> SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
> [madate])-6,1),"yyyy_mm"
> UNION ALL
> SELECT Format(DateSerial(DatePart("yyyy",[madate]),DatePart("m",
> [madate])-4,1),"yyyy_mm"
> ) AS MyAliases;
> 
> Thanks for your help.
> 
0
John
4/6/2010 4:12:44 PM
Hi John,


First of all : Thaks to spend your time on my problem.


I have this query, who give me for each typed date the count of refREA
of the month

SELECT Format([one date ?],"yyyy_mm") AS periode,
              Count(MyTable.refREA) AS nbreREA
FROM MyTable
GROUP BY Format([one date ?],"yyyy_mm");

(I discovered the PARAMETERS clause in your mail, thanks.; and will
add it)

I want to run the here above query over a 6 month period before the
month of "one date", for each complete month.
      eg : if i typed 12/02/2010   i want the result displaying from
september 2009  to feb 2010
My idea is to get "insert" automatically a date of each of the 6
months automatically







0
le
4/7/2010 6:22:48 AM
SORRY I HAVE BEEN INTERUPTED

> I want to run the here above query over a 6 month period before the
> month of "one date", for each complete month.
> =A0 =A0 =A0 eg : if i typed 12/02/2010 =A0 i want the result displaying f=
rom
> september 2009 =A0to feb 2010
My idea is to "insert" automatically a date of each of the 6  months
automatically

Something like :
my_here_above_query WITH [one date ?] IN ( myNewQuery)

myNewQuery giving a sequence of date, one per month between now and 6
months before.


Thanks

0
le
4/7/2010 7:51:08 AM
I would expect that you want a query that looks like the following, if you 
wanted to group the records by month and year and get a count of some field 
then the query would look like:

PARAMETERS [MaDate]  DateTime;
SELECT Format(SomeDateField,"yyyy\_mm") as Periode
, Count([refRea]) as nbreREA
FROM [SomeTable]
WHERE [SomeDateField] Between DateSerial(Year([MaDate]),Month([MaDate])-3,1) 
and DateSerial(Year([MaDate]),Month([MaDate])+3,0)


So if you enter December 2, 2009 you would get all the records from
   September 1, 2009 until February 28, 2010
grouped by year and month with a count.  If there were NO records at all for 
any specific month you would not get a row returned.

Your results would be something like the following
Periode : nbreREA
2009_09 : 22
2009_10 : 43
2009_11 : 2
2009_12 : 18
2010_01 : 238
2010_02 : 75

You can adjust the range by changing the -3 and +3 in the DateSerial function 
calls.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

le Nordiste wrote:
> SORRY I HAVE BEEN INTERUPTED
> 
>> I want to run the here above query over a 6 month period before the
>> month of "one date", for each complete month.
>>       eg : if i typed 12/02/2010   i want the result displaying from
>> september 2009  to feb 2010
> My idea is to "insert" automatically a date of each of the 6  months
> automatically
> 
> Something like :
> my_here_above_query WITH [one date ?] IN ( myNewQuery)
> 
> myNewQuery giving a sequence of date, one per month between now and 6
> months before.
> 
> 
> Thanks
> 
0
John
4/7/2010 1:25:46 PM
Hi John,

> PARAMETERS [MaDate] =A0DateTime;
> SELECT Format(SomeDateField,"yyyy\_mm") as Periode
> , Count([refRea]) as nbreREA
> FROM [SomeTable]
> WHERE [SomeDateField] Between DateSerial(Year([MaDate]),Month([MaDate])-3=
,1)
> and DateSerial(Year([MaDate]),Month([MaDate])+3,0)

With this adapted code i get the error message :
"You try to execute a query without the expression "
Format(SomeDateField ,"yyyy_mm") as part of agregate function


I tried also


PARAMETERS [MaDate] DateTime;
SELECT Format(SomeDateField ,"yyyy_mm") AS Periode,
Count(SomeTable.refRea) AS nbreREA
FROM SomeTable
GROUP BY Format(SomeDateField ,"yyyy_mm"), SomeTable.SomeDateField
HAVING (((SomeTable.SomeDateField ) Between
DateSerial(Year([MaDate]),Month([MaDate])-3,1) And
DateSerial(Year([MaDate]),Month([MaDate])+3,0)));



But i get be something like the following
Periode : nbreREA
2009_09 : 22
=85                  one line per working day
=85
2009_09 :54
2009_10 : 43
=85
2009_10 : 24
2009_11 : 2
.....


We are on the way but=85 a little effort more.

THANKS
0
le
4/7/2010 2:19:47 PM
Forgot the GROUP BY clause.  Sorry.

PARAMETERS [MaDate]  DateTime;
SELECT Format([SomeDateField],"yyyy\_mm") as Periode
, Count([refRea]) as nbreREA
FROM [SomeTable]
WHERE [SomeDateField] Between DateSerial(Year([MaDate]),Month([MaDate])-3,1) 
and DateSerial(Year([MaDate]),Month([MaDate])+3,0)
GROUP BY Format([SomeDateField],"yyyy\_mm")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

le Nordiste wrote:
> Hi John,
> 
>> PARAMETERS [MaDate]  DateTime;
>> SELECT Format(SomeDateField,"yyyy\_mm") as Periode
>> , Count([refRea]) as nbreREA
>> FROM [SomeTable]
>> WHERE [SomeDateField] Between DateSerial(Year([MaDate]),Month([MaDate])-3,1)
>> and DateSerial(Year([MaDate]),Month([MaDate])+3,0)
> 
> With this adapted code i get the error message :
> "You try to execute a query without the expression "
> Format(SomeDateField ,"yyyy_mm") as part of agregate function
0
John
4/7/2010 3:04:22 PM
John HURRA !

This time it's good !
THANKS A LOT


Le Nordiste - the man from the North of FRANCE-


On 7 avr, 17:04, John Spencer <spen...@chpdm.edu> wrote:
> Forgot the GROUP BY clause. =A0Sorry.
>
> PARAMETERS [MaDate] =A0DateTime;
> SELECT Format([SomeDateField],"yyyy\_mm") as Periode
> , Count([refRea]) as nbreREA
> FROM [SomeTable]
> WHERE [SomeDateField] Between DateSerial(Year([MaDate]),Month([MaDate])-3=
,1)
> and DateSerial(Year([MaDate]),Month([MaDate])+3,0)
> GROUP BY Format([SomeDateField],"yyyy\_mm")
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
0
le
4/8/2010 8:09:48 AM
Reply:

Similar Artilces:

Forthcoming Bills always give me a wrong date???
When entering in a withdrawal using the forthcoming bills option, Money always seems to enter in a wrong date on the transaction form even though it says the correct date under the forthcoming bills menu. Anyone know how to fix this?? ...

help matching a field in a query
Hello all I have a table that has the complete name for each user. I would like to run a query to match the FirstName LastName with the CurrentUser() and return a user_code. This is the query that im using now: SELECT users.user_code, users.name FROM users GROUP BY users.user, users.name HAVING users.name Like (CurrentUser()); Everything works fine when the Logon Name is the exact as the name from my table BUT the problem comes when the user forgot to type caps to its Logon Name, when this happends then my query will return the user_code. Also I want to be able to match any word from the C...

Interactive Web database with query capabilty
I see lots of questions about using Access as an interactive web database but many of the answers seem to dance all around the question. Let me be as precise with the question as possbile... Here is a online database that is exactly like what I want to do with Access http://www.osalt.org/events/osalt http://www.osalt.org/events/submit This interface was created with something called Ruby on Rails. (1) Can this same thing be done with Access and asp? (2) Can you provide an example of an access database that offers the same query capability and submission capability? I have done a si...

publisher 2000 won't run under xp
I had to delete and reinstall Office 2000 under Windows XP and am having difficulties with Publisher. As the Administrator, the cd must be in place and I get an installation/configuration window briefly before Publisher runs. If I log on as user, it can't even find the cd. When I first upgraded 98 to XP, I had no problem. Has anyone else run into this? ...

2005 dates
I am working on a reservation list for next year..... how can I get excel to enter 2005 when I put in dates and NOT 2004....... thanks Dave Type it in, enter for xmas day 25/12/5 and it will take it as next year "Dave" <post@site.com> wrote in message news:%23rZKnwewEHA.1260@TK2MSFTNGP12.phx.gbl... > I am working on a reservation list for next year..... > how can I get excel to enter 2005 when I put in dates and NOT 2004....... > thanks > > Dave > > You could right click sheet tab>view code>insert this. Now, when you enter a date below row 4 a...

determine if date range falls within date
Ok, this one is driving me crazy! Seems like it should be simple, but no luck so far. Here is what I've got: a2 start date 2/10/06 b2 end date 1/29/06 c1 - aa1 dates by month ex 1/1/06 2/1/06, 3/1/06 etc... Example 12 months exist between the start and end date, whenever the date listed in cell c1-aa1 falls within the start and end date range the value true is returned. end result there will be 12 months with true and false whenever before the start date and after the end date. Dylan, I assume that you have typo's/mixed upt your start/end dates. Try in C2: =AND(C1>$A$2,...

Need to permanently changing a row source after running code
Hello! I have added code to a form object's Not In List event to add the item to the row source automatically. While I'm in the form, the item is still there to select, but when I close the form & re-enter, the new items that I just had code add, are not there anymore & it defaults back to my original row source entries. It is not a lookup to another table or query. Just a simple value list. Any help will be extremely appreciated... I'm stumped & desperately need to wrap up this project? Thanks to everyone! Melinda S wrote: > Hello! > I have added code...

Inbox - Sort
The inbox is divided into "Date: Today", Date: Last Week", etc. Is it possible to delete that separation so that everything shows under "Inbox" instead of different "sections"? Jan Groshan <jangro@pacbell.net> wrote: > The inbox is divided into "Date: Today", Date: Last Week", etc. Is it > possible to delete that separation so that everything shows under > "Inbox" instead of different "sections"? Click View>Arrange By and uncheck Show in Groups. -- Brian Tillman Your wonderful....thanks. "Bria...

Query help for splitting departments
We want to restructure our categories and I wonder if someone can help me with a query to make this a more efficient process. We want to split the existing department into department and category. For example we have Art Glass/Bohemian as department with some minor categories that we won't be using anymore, and we now want the department to be Art Glass and the Category to be Bohemian. We have over 50 departments set up like the one above with over 22000 items, so one by one is not an option. Any help please would be so greatly appreciated. Thank you! diana Are you able to list all y...

Automaticly ad date by select
Can i automaticly fill in the date by selecting a cel? And when possible automaticly a button or something to change the date + and - at least for the day count and if possible also to change the month. For example a button left and a button right from the cel with the date to change the value. -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ Hoi Geert See this page http://www.rondebruin.nl/calendar.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Geert" <veilingsitesssNO@spamhotmail.com> wrote in message news:op.tilzuqrrm1sb3e@...

Auto filter and protection with many sheets
Hi all, The following procedure allows auto filter on protected sheets. If I have many thus sheets, do I need to replicate all these lines or can I include all their names somehow within this procedure? With Worksheets("Tax Invoice records") .EnableAutoFilter = True .Protect Password:="password", _ Contents:=True, UserInterfaceOnly:=True End With Thanks, Rob If it's all the worksheets in the workbook, you won't need the names of any: dim wks as worksheet for each wks in activeworkbook.worksheets with wks .enableautofilter = true .protect.... end w...

Sort multiple query results
I have a query based on 3 separate tables. The query calculates inventory results for 13 Die sets. One table holds the total pallets counted. The second table provides the number of panels per pallet and the third table provides the shift usage of each part. The query provides 13 calculated results, i.e. (DieSet47 * Pallet47)/ ShiftUsage47 = and (DieSet43*Pallet43)/ShiftUsage47 and so on.... These results tell us which Die Set needs to be run first -- i.e the results tell us which part we will run out of first if we don't set it up to run. I need to be able to sort these 13 results f...

sum only if a certain cell contains a date
I am trying to include a figure in a sum but I only want to count it if a date appears in another cell. How about providing some details? -- Biff Microsoft Excel MVP "mimsly17" <mimsly17@discussions.microsoft.com> wrote in message news:AD685823-85F7-408C-8407-6B7FD351DEF5@microsoft.com... >I am trying to include a figure in a sum but I only want to count it if a > date appears in another cell. > > Something like: =a1+if(b1>0,c1,0) ? Regards, Fred. "mimsly17" <mimsly17@discussions.microsoft.com> wrote in mes...

Do Not Download from Server based on Rules
Hi Do Not Download from Server exists on as an option in the rules. This means that two people can share the same email account w/two aliases I don't see this on Outlook 2003. Does anyone know of a third-party Add-On Thanks Frank ...

Getting the current date/time
Hello, I'm trying to access a date field of a particular database and the code generated by MFC defines the date variable as "DATE". I believe this DATE referes to COleDateTime and I don't know how to get the current date/time to populate this field. I'm tried the following already, but none of these worked: ----------- Example 1 ----------- DATE test; test = COleDateTime::GetCurrentTime() --------------------------------- VC says that COleDateTime is not defined. ----------- Example 2 ----------- DATE test; test = ::GetCurrentTime() -------------------------------...

HELP: Conditional Formatting Based On A Sub String
Hello, I need some help on trying to apply some conditional formatting based on sub string within string contained within a given cell. For example; CELL A1 contains "cat.dog.elephant.parrot.girrafe.duck" I want the cell to be Green if the string contains the sub string "dog". I haven't a clue how to archive this. Looking at the excel functions i don't see a InStr. Any ideas? Hardeep. In the conditional format box change the "Cell value is" to "Formula is and type this formula: =search("dog",A1)>0 then set your format to gree...

Crosstab Query for Beginning Date and Ending Date
I have several crosstab queries were the end user will be selecting a report by month. I have declared the parameters of [Beginning Date] and [Ending Date] in the query and it works great but upon opening the report, it asks for the dates twice. I have read the threads on creating a form for this. In my case, the date is coming from a SQL Server database for Remedy HelpDesk that I link to. Also, the field that I want to use [Arrival_Time] is in the Unix timestamp format and I have successfully converted this timestamp to a date field. Thus, the form would not be used to input dates...

Office 11
I was recommended by John McGhie (MVP) to wait for Office 11 instead of getting Offixe X now. Can anyone tell me then, when Office 11 is due? cheers In article <70d401c3444b$7038ea40$a401280a@phx.gbl>, "Annick C." <annickcbl@aol.com> wrote: > I was recommended by John McGhie (MVP) to wait for Office > 11 instead of getting Offixe X now. Can anyone tell me > then, when Office 11 is due? AFAIK, MS has not released an expected date for Office 11. As usual, until it's officially announced, those that know can't tell you, and those that tell you alm...

Excel 2000 date format cannot be set to Australian date format
-- Brian Jones Our Lady of the Sacred Heart College Bentleigh Victoria Australia Hi Brian, Check your Regional settings within Control Panel (Start > Settings > Control Panel) and make sure your location and other options are set to Australia and necessary formats. cheers, Nadia "Brian Jones" wrote: > > -- > Brian Jones > Our Lady of the Sacred Heart College > Bentleigh > Victoria > Australia ...

Email Folder Storage By Date
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Lately, the emails that I move from my sent folder to a customer folder end up in the category of &quot;Older&quot;. <br><br>They are not sorting themselves by date but grouping themselves in an &quot;older&quot; category. How do I set it so that they are chronological? <br><br>Thank You for your help! <br><br>Mike On 3/31/10 6:46 AM, in article 59bb644c.-1@webcrossing.JaKIaxP2ac0, "Mike1464@officeformac.com" <Mike1464@officeformac.com> wrot...

Graphing calender chart with combined relative amounts for each date
Hi there, I would like to know if it is possible to let excel draw a graph with on the x-axis dates and on the y-axis the corresponding relative changes in account balance. In my case it is possible that there are multiple account changes on an individual date but also there are dates without changes which are not included in the excel sheet. I'll try to make it a bit more clear by a short example: 1-1-2009, $200 1-1-2009, -$150 3-1-2009, $500 4-1-2009, -$350 I would like this to result in a graph like this: x-axis: 1-1, 2-1, 3-1, 4-1 y-axis: $50, $0, $500, -$...

hide rows based on cell value
I'm new to Excel and VBA. I try to hide rows based on whether the user enters a specific word(s)/phrase into a specific cell. For example, if cell M49 has a value of "i love lucy", then unhide rows 50 through 55, if cell M49 is empty or has any other value than "i love lucy", then hide the rows. How do I implement that? ' Sheet 1 Class Module Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("M49")) Is Nothing Then Exit Sub If LCase(Range("M49").Value) = "i love lucy" Then Range("A50:A55"...

How to convert 2007-001 this year and day to a normal date?
I want find out how to convert a julain to a normal date format: for example:- Julian date is : 2007001 convert to "01 Jan 2007" Regards, You should be able to use the DateSerial function. DateSerial(Left([Julian],4),1,Right([Julian],3)) IF Julian is a number field then DateSerial([Julian]\1000,1,[Julian] Mod 1000) You should check that Julian is a numeric value before trying this -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "Julian Date Conversion" <Julian Date Conv...

Change the date format and now no calendar pop-up appears
Hi there I had to change the date format from mm/dd/yyyy to the medium date of Mmm-dd-yyyy because I can't convince my database users to change their date format to mm/dd/yyyy. As a result, the pop-up calendar in Access 2007 no longer appears in my database. How do I fix it so that it appears? Thank you in advance for your help. ...

converting months to years and months???
I have used the following formula to work out the difference between two days and it provides me an answer in months. =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3) However if the difference is 15 months, how do i convert this to read 1 year and 3 months? Thank you With the date in A2: =DATEDIF(A1,a2,"y")&" Years "&DATEDIF(A1,a2,"ym")&" Months" You can find lots of information about =datedif() at Chip Pearson's site: http://www.cpearson.com/excel/datedif.htm Marty wrote: > > I have used the following formula to work out the diff...