between specific dates 2

[diwin]

I need to query the field "donationPaymentDate" from Sept 1 last year to Aug 31 this year. The query SHOULD be run in early Sept, but will probably be run a few times while approaching the cutoff date as a preview of the final report.

I've searched and fiddled with dateserial and dateadd, but got nowhere. Thanks for your suggestions.

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[mp9]

... WHERE donationPaymentDate BETWEEN #09/01/2005# AND #08/31/2006# ...

 

[diwin]

Thanks mp9.

It needs to find the year dynamically, since it will be run every year in the future too.

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[jebry]

Hi!

Try this:

Between DateSerial(Year(Date()) - 1, 9, 1) And DateSerial(Year(Date()), 8, 31)

I've not tried using DateSerial in SQL but, if it doesn't work you can run the query off of a button click and do this in VBA.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[mp9]

Try this:

SELECT *
FROM YourTableNameHere
WHERE donationPaymentDate Between IIf(Month(Date())>8,DateSerial(Year(Date()),9,1),DateSerial(Year(Date())-1,9,1)) And (IIf(Month(Date())>8,DateSerial(Year(Date())-1,8,31),DateSerial(Year(Date()),8,31)));

http://www22.brinkster.com/accessory/

 

[raskew]

Hi -

To make this dynamic so that you don't have to go in and change the coding each year, place this as the criteria for your date field:

between [enter start date] and dateadd("yyyy", 1, [enter start date]) -1

HTH - Bob

 

[cmmrfrds]

Or this in the criteria of the QBE for donationPaymentDate

Between DateAdd("m",-12,"08/31/" & Year(Now())) And DateAdd("m",0,"09/01/" & Year(Now()))

 

[raskew]

And if the criteria changes you do what?? Let's go back in and rewrite the code. Splendid!

Why are you using Now() rather than Date(). Is there a time involved here. Can't find any reference to one. Lookup the Date() and Now() functions to see the differences.

Bob

 

[diwin]

Great ideas folks! I learned a lot here.
As it turns out, the date criteria is not likely to change, so for now, I went with Jeff's:

Between DateSerial(Year(Date()) - 1, 9, 1) And DateSerial(Year(Date()), 8, 31).

But I think I will have to use Bob's if I really don't want to go back and remove this hard-coding (in my opinion) of the dates when there is a reason to change the report period, which is possible.


Daniel Dillon
O o . (<--- brain shrinking at rate shown.)