Find records for last year

[Kassy]

Iv'e looked through several date functions but cant figure out which one to use or how. I want to produce all records for a period of 1 year, for 1 year ago. i.e if I run the query during any month of the 'current year' (obvously) if its after August it will show me the previous year of August to following July. My Start date is always August 1st of any year through to the following July 31st. It is relatively straight forward (I think)if it's from January to December because year part is the same but this becomes more complicated when you cover two different Year parts. I dont want to have to use a parameter Query of 'start date' and 'end date' I want to be able to run the query based on the system date now(). Any help would be appreciated thanks.

 

[Smart]

Not sure if this is what you want but here goes

aDay = Format(Date, "dd")
amonth = Format(Date, "mm")
ayear = Format(Date, "yyyy")
ayear = ayear + 1
End If

TxtDateBack = aDay & "/" & amonth & "/" & ayear

 

[Kassy]

How?

Um ..OK sorry to be so dumb but how do I use it - Do I type it into the text box?

 

[namliam]

The first day of this month: date() - day(date()) +1
The first day of this month last year: dateadd("YYYY",-1,date() - day(date()) +1)

Simply add to the query criteria... Presto...

 

[Kassy]

This is the SQL I have for the Query after adding your statements:
SELECT tblMembers.MemberID, tblMembers.FirstName, tblMembers.LastName, tblMembers.Address, tblMembers.PostCode, tblPayments.PaymentAmount, tblPayments.PaymentDate
FROM tblMembers INNER JOIN tblPayments ON tblMembers.MemberID = tblPayments.MemberID
WHERE (((tblPayments.PaymentDate)=Date()-Day(Date())+1 Or (tblPayments.PaymentDate)=DateAdd("yyyy",-1,Date()-Day(Date())+1)));
It returns no records where there should be about 150. I guess Iv'e not got it right? Any obvious errors? thanks.

 

[EMP]

I want to produce all records for a period of 1 year, ............ My Start date is always August 1st of any year through to the following July 31st.

If I read your question correctly, you can put the following in the criteria for the date field:-

BETWEEN IIf(Month(Date())<8, DateSerial(Year(Date())-2,8,1), DateSerial(Year(Date())-1,8,1)) AND IIf(Month(Date())<8, DateSerial(Year(Date())-1,7,31), DateSerial(Year(Date()),7,31))


So when the query is run between today and 31 July 2006,
it will return records from 1 August 2004 to 31 July 2005.

And when it is run between 1 August 2006 and 31 July 2007,
it will return records from 1 August 2005 to 31 July 2006.

^

 

[Kassy]

Thanks a million

EMP --yes that is what I want, will try it asap but it looks similar to code I already have for Current records which I tried modifying but couldn't get quite right! It was getting the correct 'minus' number in each case I couldn't get right. Thanks a million, hope it works! Sounds like it will.

 

[Kassy]

Yes it works and does exactly what I want- brilliant.