Formula using nearest time before a calculated value?

[Betsy]

Hi there!

I have question and response data from students for which I use ID numbers (or lack thereof) as information, specifically to calculate the number of times they asked for our service to stop. I'm trying to see if specific questions lead to subsequent stop outs.

The relevant data tables/columns look something like this:

A. Students

Student ID; Active

1; TRUE

2; FALSE

3; TRUE

B. Questions

Student ID; Question ID; Question Date

1; 200; 9/8/16

1; 201; 9/9/16

2; 200;  9/8/16

3; 200; 9/8/16

3; 201; 9/10/16

C. Responses

Student ID; Question ID; Text; Response Date; Question Date

1; 200; Yes; 9/8/16; 9/8/16

1; 201; 5; 9/9/16; 9/9/16

2; [blank]; Stop; 9/8/16

3; 201; 4; 9/11/16; 9/10/16

3; [blank]; get more sleep; 9/11/16

When students make a stop response to opt out of the system, there is no Question ID or Question Time directly associated with it. Students do make other random responses that also don't have Question IDs or Question Times associated with them. I calculate stops with a measure which counts when Question ID is 0 (I converted blank Question IDs to 0) from the Responses table and Active = FALSE from the Students table.

What I am trying to do is now tie stops to the previous question. So for whichever Question Date is closest to (on or before) a stop date (so a Response Date with a 0 Question ID for an inactive student), what is the Question ID?  In the above examples, Student 2s reponse would be counted as a stop, and the Question ID closest to that date would be 200. Student 3s reponse on 9/11/16 would not be a stop. Question Dates associated with Question IDs are not fixed. One student could get Question 200 on a different day than another student. On the other hand, students almost never receive more than one question on a given day.

Another way to say it: If student is stopped, what was last question ID? (this might be simpler).

I'm stumped as to how to set up a formula to give me these Question IDs that come closest prior to a stop calculcation. 

Thanks for any help and I'd be more than happy to clarify.

Betsy