By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

Searching between dates

P: 59
I have a command button which runs a macro to open a report. In the Where Condition of the macro I have the following "Year([Next Review Date])=Year(Date())"+1.

This is good if I want the following year only, however I would like it to also return Rent Reviews which are still active and have not been settled as well as ones one year in advance. Any help would be greatly appreciated.
Dec 16 '16 #1

✓ answered by PhilOfWalton

No peace for the wicked.

Try

(Year([Next Review Date])=Year(Date())) OR ([Next Review Date]< Date()) AND Archive = False

Phil

Share this Question
Share on Google+
11 Replies


PhilOfWalton
Expert 100+
P: 1,430
What is the condition where the rent has not been settled? I am expecting something like RentPaid < RentOwed

What is the "Still Active" condition, or are they one and the same?

Phil
Dec 18 '16 #2

P: 59
Phil, if the rent review has not been settled then the date of the review stays the same (some have not been settled for two years and remain dated as 2014). Once it is settled a new rent review date is placed in it. Sorry, I don't understand the question re "Still Active" condition.
Dec 19 '16 #3

PhilOfWalton
Expert 100+
P: 1,430
I think you missed my point. What do you do to indicate the rent has been settled. Something must change to indicate the change from unsettled to settled. If I can find out what this is then your original "Where Condition" will have an additional "Or" something ......

Phil
Dec 19 '16 #4

P: 59
Phil, to indicate the rent has been settled I just add in another date into the ReviewDate box. I don't have a check box or anything else to say the rent review has settled.
Dec 19 '16 #5

PhilOfWalton
Expert 100+
P: 1,430
So it seems to me that you are looking for the review date less than today's date OR any date next year.

Specifically if the Review Date < 18 December 2016 or any date in 2017

Or is it <= 18 December?

Phil
Dec 19 '16 #6

P: 59
Phil, yes - it's any date less than today's date plus any in the coming year.
Dec 19 '16 #7

PhilOfWalton
Expert 100+
P: 1,430
Well we got there in the end. Good

So try

(Year([Next Review Date])=Year(Date())) OR ([Next Review Date]< Date())

Phil
Dec 19 '16 #8

P: 59
Phil, wonderful, thank you!
Dec 19 '16 #9

P: 59
Phil, now I have it working may I ask your help with something else? When I run the report it shows tenants who have vacated who had rent reviews in 2009, 2010, etc (we keep all previous tenants details). I have a checkbox called Archive which disables a vacated tenant via conditional formatting. Is there a way for these details to be omitted from the report?
Dec 19 '16 #10

PhilOfWalton
Expert 100+
P: 1,430
No peace for the wicked.

Try

(Year([Next Review Date])=Year(Date())) OR ([Next Review Date]< Date()) AND Archive = False

Phil
Dec 19 '16 #11

P: 59
Works like a charm! Thank you so much.
Dec 19 '16 #12

Post your reply

Sign in to post your reply or Sign up for a free account.