Hi all, this is my first post so apologies if I don't format this correctly or if I'm not doing things properly. Please give me any tips you think would help!
I've got a decent enough working knowledge of Access but I'd still class myself as a bit of a beginner and I'm picking stuff up as I go along. I do most of my building using Design View whilst throwing in a bit of work in SQL view when I'm feeling brave. A lot of the time I'll create objects in Design View and then look at the SQL behind it to try and learn and understand the workings of it better.
Onto my question...
What I'm asking is quite complex to explain (for me at least) so please bear with me...
I am creating a case management database, and am trying to create a query that, when run, will display all the cases that were 'open' during a certain time period e.g. all cases that were open at any point during Quarter 1 (April 1st - June 30th).
To do this the query takes three fields as criteria: DateOpened, Status (Open/Closed), and DateClosed. I have managed to get the query to display the results I want when specific dates are entered. For example purposes I will use the dates for Quarter 1 as I have above. This is an overview of my query:
What I'm hoping to do is be able to have the dates be flexible, rather than having set dates that the query runs for, so I would normally do this with a parameter query where the dialogue box would pop up with [Enter Date A] and [Enter Date B], but due to the fact that there are several lines of query criteria I don't know whether this would work.
My ultimate question is, is there a way of the user entering two dates and the database then assigning some sort of reference to them so that the same two dates are used for the rest of the lines of the criteria query?
In an ideal world, I'm looking for something that I'm guessing would look like this:
Is anything like this possible? If not, I'm very sorry for making you read all this for nothing but thank you anyway!
I've got a decent enough working knowledge of Access but I'd still class myself as a bit of a beginner and I'm picking stuff up as I go along. I do most of my building using Design View whilst throwing in a bit of work in SQL view when I'm feeling brave. A lot of the time I'll create objects in Design View and then look at the SQL behind it to try and learn and understand the workings of it better.
Onto my question...
What I'm asking is quite complex to explain (for me at least) so please bear with me...
I am creating a case management database, and am trying to create a query that, when run, will display all the cases that were 'open' during a certain time period e.g. all cases that were open at any point during Quarter 1 (April 1st - June 30th).
To do this the query takes three fields as criteria: DateOpened, Status (Open/Closed), and DateClosed. I have managed to get the query to display the results I want when specific dates are entered. For example purposes I will use the dates for Quarter 1 as I have above. This is an overview of my query:
Code:
DISPLAY ALL RECORDS WHERE:
DateOpened <#01/04/2016# AND Status = "Closed" AND DateClosed Between #01/04/2016# And #30/06/2016#
**OR**
DateOpened <#01/04/2016# AND Status = "Open"
**OR**
DateOpened >=#01/04/2016# AND Status = "Closed" AND DateClosed <=#30/06/2016#
**OR**
DateOpened >=#01/04/2016# AND Status = "Open"
**OR**
DateOpened Between #01/04/2016# And #30/06/2016# AND Status = "Closed" AND DateClosed >#30/06/2016#
**OR**
DateOpened <#01/04/2016# AND Status = "Closed" AND DateClosed >#30/06/2016#
What I'm hoping to do is be able to have the dates be flexible, rather than having set dates that the query runs for, so I would normally do this with a parameter query where the dialogue box would pop up with [Enter Date A] and [Enter Date B], but due to the fact that there are several lines of query criteria I don't know whether this would work.
My ultimate question is, is there a way of the user entering two dates and the database then assigning some sort of reference to them so that the same two dates are used for the rest of the lines of the criteria query?
In an ideal world, I'm looking for something that I'm guessing would look like this:
Code:
DISPLAY ALL RECORDS WHERE:
DateOpened <[Enter 'Date A'] AND Status = "Closed" AND DateClosed Between 'Date A' And [Enter 'Date B']
**OR**
DateOpened <'Date A' AND Status = "Open"
**OR**
DateOpened >='Date A' AND Status = "Closed" AND DateClosed <='Date B'
**OR**
DateOpened >='Date A' AND Status = "Open"
**OR**
DateOpened Between 'Date A' And 'Date B' AND Status = "Closed" AND DateClosed >'Date B'
**OR**
DateOpened <'Date A' AND Status = "Closed" AND DateClosed >'Date B'
Is anything like this possible? If not, I'm very sorry for making you read all this for nothing but thank you anyway!