Exclude years in front from query

[Ruegen]

I have as a criteria which I assume is showing the bookings for this year and 2 years back using the criteria below.

Between Year(Now()) And Year(Now())-2





however I would like the query to exclude any bookings above the current year....

any suggestions?

[June7]

What do you mean by 'above the current year' - 2014 and on? I don't see the query capturing 2014 and on.

[Ruegen]

after, anything after the current year. So if year is 2013 then 2014, if year is 2014 then 2015 and nothing in 2015 can show.

BookingDate is the date field.

[June7]

Still don't understand. The parameters shown should not capture any future years. However, I would put the earlier parameter first just because it's easier to visualize the construct, although either should work:

Between Year(Date())-2 And Year(Date())

[Ruegen]

Still don't understand. The parameters shown should not capture any future years. However, I would put the earlier parameter first just because it's easier to visualize the construct, although either should work:

Between Year(Date())-2 And Year(Date())

For some reason if I don't format BookingDate into year first then Between Year(Date())-2 And Year(Date()) won't work as a where

so I had to make calc: year([tblBookings]![BookingDate]) first then use Between Year(Date())-2 And Year(Date())

That gives me the same results as before - it lists the schools with bookings within those dates (2012, 2013) but doesn't exclude the schools with bookings that are in 2014...

[Ruegen]

I guess what I am saying is yes it is only showing the results of that school from between this date and this date but it isn't excluding the school that has dates after that period.

so ideally a criteria would be

between this date and this date

AND

no school id with a date after this date

[June7]

I don't know why that would be the case. Only records meeting all 3 criteria should display.

[Ruegen]

I don't know why that would be the case. Only records meeting all 3 criteria should display.

yes that is correct only those do. However there are schools with bookings that have dates after the dates that are displayed - so even though I am showing the schools with bookings within that date period I am not excluding any schools that have bookings after that date period because they happen to have dates in the within date (and thus displayed).

The idea is that I want to show schools that have had bookings for this year however as soon as they get a booking for next year as well then they are automatically removed from the list by query.

[June7]

Okay, means criteria based on data in other records of same table. That is tricky.

A subquery or DLookup might work.

Calculate another field in the query with:

DLookup("BookingDate","tblBookings","Year([BookingDate])>Year(Date()) And NewSchoolsID=" & [NewSchoolsID])

Then criteria under that constructed field: Is Null

[Ruegen]

Okay, means criteria based on data in other records of same table. That is tricky.

A subquery or DLookup might work.

Calculate another field in the query with:

DLookup("BookingDate","tblBookings","Year([BookingDate])>Year(Date()) And NewSchoolsID=" & [NewSchoolsID])

Then criteria under that constructed field: Is Null

I honestly thought of using a subform but I figured it could be replicated on a continuous form. I will give your dlookup suggestion a try as I would prefer that.

[Ruegen]

I honestly thought of using a subform but I figured it could be replicated on a continuous form. I will give your dlookup suggestion a try as I would prefer that.

It works with where instead of group in the query but because there are so many records it does take some time to load.... might limit to those that have over 5 bookings or something.

Thanks June7

[June7]

Yes, domain aggregate functions can slow down queries. Might be able to use subquery, like:

SELECT * FROM tablename WHERE NewSchoolsID Not In (SELECT NewSchoolsID FROM tablename WHERE Year(BookingDate)>Year(Date));

However, I don't have much experience with the In/Not In operators, not sure if you can have other filter criteria along with it.

[Ruegen]

Sorry June I have to go back to this one.

I had to delete NewSchoolsID from tblBookings (which used to work with the above dlookup) because that is unused and not an updated field. The formula relied on it.

Is there another way around? I tried pulling only the NewSchoolsID from tblTeacher and left out the table for the schools, then placed as a where in the BookingDate <#1/1/2014# just to test anything before that date would be excluded but for some strange reason in the grouped query the schools with bookings (BookingDate) above 1/1/2014 are still showing.

I can't use the dlookup because that field is missing and it can't find tblteacher!newschoolsID for some reason.

updated query

[June7]

Sorry, this has gotten stale for me and I can't even recall what led me to the previous suggestions. I would have to start over from scratch and don't know when I will be able to. Even if I do try, don't think I know enough about your db.

[Ruegen]

Sorry, this has gotten stale for me and I can't even recall what led me to the previous suggestions. I would have to start over from scratch and don't know when I will be able to. Even if I do try, don't think I know enough about your db.

all good I will try fresh start