Select a set of data based off two criteria

[erfigge]

I don't even know how to search this to see if there is a faq already made on this, but let me explain my situation and then maybe it will make sense to somebody. I have two tables, One with instrument numbers, and then another table joined with instrument numbers that has all the data for each instrument. Right now I pass a date from a user form to the query to pull up the data that is after this date:

>=(Date()-[Forms]![Query Builder]![days])

What I want to do is if a checkbox is checked: IIf([Forms]![Query Builder]![incal]=True,true,false)
Then if true, find if an instrument number has data from the past 6 months. If it has, get all data from that instrument same as above. If not, don't display any data for that instrument and move on. I hope that makes sense....

 

[lupins46]

Depends on exactly what you mean by 'the past 6 months'.
This test will do the last 183 days.

>=Date()+1 -IIf([Forms]![Query Builder]![incal]=True,184,0))

 

[erfigge]

Well, by last 6 months I want to see if an instrument number has an entry from the past 6 months. If is has I want to display the last years worth of data. If it hasn't had an entry in the last 6 months, reguardless if it has data in the past year I just want to skip it by and not display any data.

Eric.

 

[lupins46]

You will need to do 2 queries.
One to identify which instrument numbers qualify for selection (using the 6 months test) and then a second query which includes a join to the first query on instrument number and which selects all records for the last year.