Date Range Criteria help

[jsubby3]

Hello all, hopefully this is a quick fix

I inherited this database issue and cant seem to fix it.

what I am trying to do is at the beginning of the month run a query that forecasts the current month and 11 months out. So today I want to run it and capture and enteries from 12/1/2012 thru 11/30/2013. I keep getting some residual 11/2012 entries and do not know how to fix it.

Between DateAdd("d",-Day(Date()),Date())-5 And DateAdd("yyyy",1,Date()-Day(Date()))

Everything I have tried has limited my current months data. one or two formulas I have used dropped entries from 12/1 and 12/2 and I need them.

[RuralGuy]

Have you gone into the Immediate window and checked to see twhat those two DateAdd() formulas return?

[jsubby3]

Not sure I know what your talking about.

[orange]

I tried this, then saw Allan's post, but I'm still including the code.

Create a small routine (vba) in a sub procedure.
Make up some TestDate, and try to find the proper calculation
for first of the current testmonth to the end of last month (next year).
Usind today's date 12/3/2012 -- you want 12/1/2012 and 11/30/2013

Sub testYearOut()

Dim myDate As Date
myDate = #12/17/2012# '#3/21/2012#  ' #9/30/2011#

Debug.Print "between " & DateSerial(Year(myDate), Month(myDate), 1) & "  and " _
& DateSerial(Year(myDate) + 1, Month(myDate) - 1, Day(DateAdd("d", -1, DateSerial(Year(myDate) + 1, Month(myDate), 1))))
End Sub

If you do not know about debugging, or debug.print, then
please see this site http://www.cpearson.com/excel/debug.htm

[RuralGuy]

You get to the Immediate window by getting to a code window and then press ^G (ctrl G). You could then key in:
? DateAdd("d",-Day(Date()),Date())-5
...and see what comes back.

[jsubby3]

Thanks, it comes back with 11/25/2012 which is what I expect since it is driving back 5 days. when I take the 5 days out it seems to be ok returning enteries for 12/2. However if I change my computer date and time to say the 19th of the month and run the same logic it does not return the full months data. Is there a specific formula that I can use to say start at the first day of the current month?

[RuralGuy]

Use the DateSerial() function as Orange suggested.

[orange]

I gave the general calculation for the "Between date1 AND date2" in post #4

It will give the day1 of the current month ,current year as date1
and last day of the month, where month =current month -1, and year = current year + 1 as date2

This is the set up that would work the Between clause for an SQL query.

Good luck with your project.