Return a date range based on today's date

I need to build a query (to be used for eternity) to return records for dates 
between 01 July (the year prior) and 30 Jun (the current year) based on the 
date when the query is run.  
Is this possible?
0
Utf
12/1/2009 4:08:01 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
1082 Views

Similar Articles

[PageSpeed] 32

Take a look at the DateSerial() function in Access HELP.  It should provide 
you the mechanism for doing what you're describing.

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"BABs" <BABs@discussions.microsoft.com> wrote in message 
news:6233DDE7-CAB7-451B-A9F9-DD741A1F6656@microsoft.com...
>I need to build a query (to be used for eternity) to return records for 
>dates
> between 01 July (the year prior) and 30 Jun (the current year) based on 
> the
> date when the query is run.
> Is this possible? 


0
Jeff
12/1/2009 4:59:20 PM
If you have a driver table, a table with one field, with values for 0 to 366 
(at least).  Say that table is called Iotas, and its field, iota, also its 
primary key.

--- untested---
SELECT  DateSerial( Year( DateAdd("m", -6, now( ) )), 7, 1) + Iotas.Iota
FROM Iotas
WHERE iota<= iif(DateSerial( 1+Year( DateAdd("m", -6, now()) ), 2, 29 ) =29 
, 366, 365)




The idea is to check if this is a leap year or not (to take 366 or 365 
days), and to add all of them to the first of July of the proper year.



Vanderghast, Access MVP



"BABs" <BABs@discussions.microsoft.com> wrote in message 
news:6233DDE7-CAB7-451B-A9F9-DD741A1F6656@microsoft.com...
>I need to build a query (to be used for eternity) to return records for 
>dates
> between 01 July (the year prior) and 30 Jun (the current year) based on 
> the
> date when the query is run.
> Is this possible? 

0
vanderghast
12/1/2009 5:08:16 PM
Yes it is possible.  The criteria would be

Between DateSerial(Year(Date())-1,7,1) and DateSerial(Year(Date()),6,30)

So for today (December 1, 2009) that will generate records for
July 1, 2008 to June 30, 2009

On Jan 1, 2010 that will generate records for
    July 1,2009 to June 30, 2010

If you want Jan 1, 2010 to return
    July 1, 2008 to June 30, 2009
you can adjust that and use
   Between DateSerial(Year(DateAdd("m",-6,Date()))-1,7,1)
     AND DateSerial(Year(DateAdd("m",-6,Date())),6,30)
Which should shift year ranges on July 1.



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

BABs wrote:
> I need to build a query (to be used for eternity) to return records for dates 
> between 01 July (the year prior) and 30 Jun (the current year) based on the 
> date when the query is run.  
> Is this possible?
0
John
12/1/2009 5:13:18 PM
On Tue, 1 Dec 2009 08:08:01 -0800, BABs <BABs@discussions.microsoft.com>
wrote:

>I need to build a query (to be used for eternity) to return records for dates 
>between 01 July (the year prior) and 30 Jun (the current year) based on the 
>date when the query is run.  
>Is this possible?

Not just possible but easy:

>= DateSerial(Year(Date()) - 1, 7, 1) AND < DateSerial(Year(Date()), 7, 1)

-- 

             John W. Vinson [MVP]
0
John
12/1/2009 5:36:28 PM
I assumed you want to GENERATE all the date; if you already have the records 
and wish to limit yourself to the interval, use one of the other 
propositions.

I missed a Day( ) in my where clause:

WHERE iota<= iif(Day(DateSerial( 1+Year( DateAdd("m", -6, now()) ), 2, 29 )) 
=29  , 366, 365)



Vanderghast, Access MVP


"vanderghast" <vanderghast@com> wrote in message 
news:79A6EF6F-FAD7-4507-9FDE-9DD9D9941781@microsoft.com...
> If you have a driver table, a table with one field, with values for 0 to 
> 366 (at least).  Say that table is called Iotas, and its field, iota, also 
> its primary key.
>
> --- untested---
> SELECT  DateSerial( Year( DateAdd("m", -6, now( ) )), 7, 1) + Iotas.Iota
> FROM Iotas
> WHERE iota<= iif(DateSerial( 1+Year( DateAdd("m", -6, now()) ), 2, 29 ) 
> =29 , 366, 365)
>
>
>
>
> The idea is to check if this is a leap year or not (to take 366 or 365 
> days), and to add all of them to the first of July of the proper year.
>
>
>
> Vanderghast, Access MVP
>
>
>
> "BABs" <BABs@discussions.microsoft.com> wrote in message 
> news:6233DDE7-CAB7-451B-A9F9-DD741A1F6656@microsoft.com...
>>I need to build a query (to be used for eternity) to return records for 
>>dates
>> between 01 July (the year prior) and 30 Jun (the current year) based on 
>> the
>> date when the query is run.
>> Is this possible?
> 

0
vanderghast
12/1/2009 6:05:43 PM
Awesome....thx

"John W. Vinson" wrote:

> On Tue, 1 Dec 2009 08:08:01 -0800, BABs <BABs@discussions.microsoft.com>
> wrote:
> 
> >I need to build a query (to be used for eternity) to return records for dates 
> >between 01 July (the year prior) and 30 Jun (the current year) based on the 
> >date when the query is run.  
> >Is this possible?
> 
> Not just possible but easy:
> 
> >= DateSerial(Year(Date()) - 1, 7, 1) AND < DateSerial(Year(Date()), 7, 1)
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
12/1/2009 9:40:17 PM
Reply:

Similar Artilces:

Range Names
I need to figure out how to automatically assign dynamic range names to each "Saturday to Friday" in a list of monthly day numbers and associated day names for an entire year. A B 1 Sat 2 Sun 3 Mon 4 Tue 5........etc. I don't know where to start. Thanks, Wes Hi Wes, Depending on what you wish to do, XL's built in WeekNum function may assist. --- Regards, Norman "Wes" <additude@texstapes.com> wrote in message news:uV1CDQweFHA.3836@tk2msftngp13.phx.gbl... >I need to figure out how to automatically assign dynamic range name...

Report of sales by month based on a cross tab query
Hi, I have a cross tab query that will provide sales by month for 2008. Right now it is sales for January08. As there are sales for future months, they will appear as well. I want to create a table that has all of the months listed out already. Right now, on the first day of the month, I go into the report and add the new month. I want it so that all month are listed which I have done. The problem is that when I run the report, I get an error that says "the Microsoft Jet does not recognize February as a valid field name. After today, there will be February data but no March ...

sales line detail window-after updating the request Ship Date-also
I am also using manufacturing, when you change the requested ship date, you get a window asking: You can update the In House due date, What is the name of this window in modifer? I would like to modify this form, so the No button is disables, I want to make sure they always click 'yes' How do I find this form in modifer? the form name is just 'Microsoft GP Dynamics' thanks -- Doug ...

Question on Returns to Vendor without PO & Receipt number
Our company switched to Great Plains Dynamics from Simply Accounting 3 months ago. We currently need to return some of the inventory items to our vendors. However, when we try to enter a transaction entry in Purchasing/Returns Transaction Entry, in the detail lines we are asked for the PO number or the receipt number. We are not allowed to go further or save without the PO number or receipt number. When we converted from Simply Accounting, all we entered was the beginning balance of the vendors and only the PO's which we expect to receive. The older PO's were not carried over. W...

Posting Date used in Revenue Expense Deferral
I have a PM Invoice with Document Date 16/11/2009 and Posting Date of 01/12/2009. Entered Deferral details starting 01/12/2009. In GL the deferral charges commence 01/12/2009 and are all as expected. The Credit entry for the full invoice is posted to GL with posting date of 01/12/2009. The problem I have is that the Debit entry for the full invoice amount is posted to the document date of 16/11/2009 and I want it to be the same as the invoice posting date (01/12/2009) Is there a way to do this? Thanks Audrey ...

Can excell notify me of due dates?
If I have due dates on my worksheet can I set something up so that I am notified on these dates? I've used this to help me. http://www.contextures.com/xlCondFormat01.html "Deion007" wrote: > If I have due dates on my worksheet can I set something up so that I am > notified on these dates? ...

Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and 1 from Table 2. All three entries are included for each day. Except somedays which may have only the one piece of data from Table 2 or none at all. How do I get the table to automatically fill in all the days whether they have information or not? =?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in news:A5EF53AB-6063-434F-BFC3-43259301751D@microsoft.com: > I have a pivot chart which pulls 2 pieces of information from one > table 1 and 1 from Table 2. All three entries are included for &...

Excel date changes
I have created a spreadsheet using excel which requires the use dates on a monthly basis (similar to a calendar). The problem I have is that every month I have to go in and change the dates on the spreadsheet to reflect those of the current month before printing. Is there a way I can make it so that excel does that for me every month. Thanks, I'd really appreciate the input. Please elaborate on what the spreadsheet looks like. "phonescoop@hotmail.com" wrote: > I have created a spreadsheet using excel which requires the use dates > on a monthly basis (similar to a cal...

Dates #12
I have a rather looooong list of dates (mm/dd/yy). In a column beside it I want just month and year so I can, for example, find all books sold in a month range. Can you help? NO need where rngA has your dates and rngB is a count of the sales =sumproduct((year(rngA)=2004)*(month(rngA)=8)*(rngB)) to sum rngC if it had the price total for each date =sumproduct((year(rngA)=2004)*(month(rngA)=8)*rngC) -- Don Guillett SalesAid Software donaldb@281.com "Lyndie" <anonymous@discussions.microsoft.com> wrote in message news:83fd01c48543$7d211680$a501280a@phx.gbl... > I have a ...

can i see the date the last time a cell was changed?
I am trying to figure out a formulla to make the date appear in one cell everytime anouther cell's data is chaged. Use a worksheet_change event to copy the cell address and put in a date stamp. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "JohnNuTek" <JohnNuTek@discussions.microsoft.com> wrote in message news:5085A544-CBF4-4B81-A244-B03DE9A0E9E6@microsoft.com... >I am trying to figure out a formulla to make the date appear in one cell > everytime anouther cell's data is chaged. There really isn't a worksheet formula to do that. Typicall...

Splitting excel cells based on content
I have an excel workbook with a worksheet created by a dump from a database (DOORS in this case). The first column is unique, the second not. The second column may contain 0 to n identifiers that I want to look up on a separate sheet. The lookup is easy when the cell in the 2nd column has either 0 or 1 identifiers in it but when there are 2 or more (separated by a carriage return I think) my lookup fails as lookup is using the enitre contents of the cell. What i'd like to be able to do is to automatically (there's some 900+ rows in the worksheet) is to automatically insert additio...

building a range for SetSourceData
I am trying to build a range object to feed a chart input. I want to move from old to new: ' OLD myChart.SetSourceData Source:=Sheets("Main Sheet").Range( _ "DF71:IS71,DF73:IS73"), PlotBy:=xlRows ' NEW myChart.SetSourceData Source:=ws , PlotBy:=xlRows Here is the way I built the robj. I use the other range objects to collect row and column info. What am I doing wrong? Dim ws As Range Set ws = myWs.Range( _ myWs.Range(myWs.Cells(Date_Week.row, FirstDataCol.Column), myWs.Cells(Date_Week.row, LastDataC...

Combo values from query based on form fields
I am setting the values for a combo box in a form(s) via a query that 'filters' the results with criteria based upon the values of other fields on the form. The combo is a field that is bound. However, this is giving all kinds of problems ranging from Access completely crashing to being asked for the parameter values of those criteria fields when closing the form. I have tried making the combo an unbound field and then setting the value of the bound field to that unbound field after update, but that still leads to the same issues. How can I do this? As example - I have a form w...

Is Auto Expansion (i.e., wrap text) of a cell possible when the cell's contents are based on another cell?
I am trying to display the results from one tab (Tab 1) in another (Tab 2). So, for example, tab 2 contains the formula "=Tab1!A2". However, when the results are too large to fit the cell in tab 2 (i.e., the cell that contains a formula that draws from a cell in tab 1), the wrap text feature does not work unless i first double click in the cell in tab 2. Is there any way around this? Can the wrap text feature work automatically somehow? Or will i need to double click in every cell that contains text that doesn't fit into the cell. Thanks for any suggestions, or VBA code, th...

if statement with dates #2
I would like to have a statement like: IF(a1>0,1/1/2004) with 1/1/2004 beint a date. EXCEL is interpreting 1/1/2004 as an arithmetic statement and giving me the result of the arithemtic. I could put in the numeric offste for the date and write IF(a1>0,37987), formatting the cell as a date, but this seems awkward. What else can I do? TIA, Sanford ...

Name range within a name range
In Excel is it possible to create a name range that contains multiple name ranges. As an example, I have created several name ranges with one cell reference, such as Sales(A2), COS(A5), ADM(A7), SELLING(A8), MRKT(A9). I would like to create a name range that I could include the all the name ranges and call it GROSS_PROFIT. If later I add another range I would like to be able to just add the range name to GROSS_PROFIT, such as R&D. Also the reason I chose to use named ranges is my cell references can change from month to month, I find it easier to create formulas using name refer...

Reverse sign of range
I have a routine that reverses the sign of a list of data. This particularly useful when entering a list of items and discovering that they're the wrong way round. However, it relies on cell A1 being available to create the -1 in the clipboard. The drawback with this is that it doesn't work on protected sheets (unless A1 is unprotected, of course). Is there a better way of putting -1 into the clipboard ? Sub Paste_Minus() ' Check that a file is open before attempting to run the procedure If IsFileOpen() = False Then Exit Sub ' cFormula is the formula c...

remove carriage return in multiple cells
Hello, I have an excel sheet with lots of data. Unfortunately lots of cells contain carriage returns. Is there a way to remove the carriage returns in those cells (more returns in a cell possible)? Thanks, Andy > Andy Select your range, then CTRL+H. In Find What, type 0010 (number keypad) while holding down ALT. Click Replace All. HTH, Andy Try running this macro: Sub RemoveCRLF() Cells.Replace Chr(10), "" Cells.Replace Chr(13), "" End Sub Afterward you may want to turn off Wrap Text (under Format, Cells, Alignment) for the affected cells. -- Jim...

Change the format of my footer {date} option?
I would like to show the date in my footer as January 25, 2005 instead of the default 01/25/05 Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = Format(Date, "mmmm dd, yyyy") End With End Sub put this in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "Tori" <Tori@discussions.microsoft.com> wrote in message news:817363F1-32AB-4528-B1D6-304F8E712EA8@microsoft.com... > I would like to show the date in my footer as January 25, 2005 instead of the &g...

lookup value based on multiple criteria
Banging my head on this one... can anyone help?? I've got several thousand rows of data in three columns, structured similar to example below. Each set of ID numbers represents a separate contact entry (person) in an address book. FIELDNAME could include one or more of about 200 fields, and VALUE may be blank. ID FIELDNAME VALUE 1 FirstName Bob 1 LastName Smith 1 Company Tech Smith, Inc. 2 LastName Johnson 2 Company <blank> 2 FirstName Jim I've got a second sheet set up with all of the 200 possible FIEL...

sorting activities for each lead/contact by actual end dates
When I view the activities associated with each lead and contacts, the actual end date are not shown. Does anyone know how I can dispaly this field? The activities list in workplace does show the actual end dates but we'd like to see that when we access the list through indivial contact/lead record. Thanks, Victoria Hi Victoria, There is no good out of the box way to do this in MSCRM today. We're working hard to fix this for v2.0. Many of our customers have worked around this issue by registering for a post call out on the activity creation, and then having a custom object wh...

Pasted fractions converted to dates
I'm trying to copy a table containing text, decimals and fractions from a word table into excel. How can i stop the fractions from becoming dates when pasted? ie. 1-5/8 becomes 1/5/2008 and 9/16 becomes 16-Sep. Its the hyphen that is causeing the trouble. A fraction in Excel has the form 1 5/8 with a space between the integer and the fractional parts Can you get rid of the hyphen in the Word doc? best wishes Bernard "Jon_C" <JonCCrouch@gmail.com> wrote in message news:1192110561.240426.147550@o3g2000hsb.googlegroups.com... > I'm trying to copy a table containin...

Auto copy dates from one cell to another
I have a multiple page worksheet. In A8 is for the Employee Name and in B8 is for the date. I would like for this information to automatically be placed at A96 and B96 which is the top of the second page within the worksheet and so on for the remainder of the pages. Position cursor in cell A96 and enter the formula: =A$8, and in B96 the formula =B$8 Copy these two cells to the appropriate cells on the other "pages" of your worksheet. Pete Hi Kelly, In cell A96 you fill out the formula =A8 In cell B96 you fill out the formula =B8 this formula tells excel to take the val...

How do i hide carriage returns?
How do i hide carriage returns? (the "boxes" behind a line of text) Format the cells to Wrap Text. Or get rid of the linefeeds. Edit>Replace what: ctrl + j with: space or nothing. Gord Dibben MS Excel MVP On Fri, 28 May 2010 09:41:01 -0700, TVGuy29 <TVGuy29@discussions.microsoft.com> wrote: >How do i hide carriage returns? (the "boxes" behind a line of text) > ...

counting dates <= 7 days ago based on criteria in a diff column
I have a spreadsheet that holds all tasks for a project. Column D holds a catagory and column Q holds the date closed. I need a formula (on a separate sheet) that counts all tasks of a specific category that were closed in the past 7 days. I already have a formula that calculates all tasks that were closed in the past 7 days, just need to add the additional criterion of the category. Hi, Try this =sumproduct((sheet1!D2:D30=A2)*((today()-sheet1!Q2:Q30)=7)) A2 on sheet2 has the specific category for which you want to count the closed tasks -- Regards, Ashish Mathur...