Question about date parameters in multiple criteria lines in a query (1 Viewer)

dal1503

Registered User.
Local time
Today, 00:15
Joined
Apr 14, 2016
Messages
34
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:


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!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:15
Joined
May 7, 2009
Messages
19,169
that can be done through form, on which case it would be query by form (qbf).
create a form with 2 unbound date control, a listbox 2 columns:
1st column the query name, 2nd column for a descriptive text of your query.
make the first column hidden.
a button that execute the selected query from the list.

docmd.openquery Me.ListBox, acViewNormal, acReadOnly

your query should include the two dates on this form, ie:

where [dateField] Between Forms!yourFormName!Date1 And Forms!yourFormName!Date2
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:15
Joined
Feb 19, 2013
Messages
16,553
not sure why you need the status field - if there is a closed date, it's closed and if it is null it is open

Graphically I think you are saying you have 6 cases, 1 still open, 1 closed before the review period, 1 opens after the review period and three within the review period plus one of which is still open which are the ones you want to capture

Case1. |-----------|
Case2.................... |-----------|
Case3.......... |-----------|
Case4............................ |-----------|
Case5...................................... |----------- (still open)
Case6.............................................. |-----------|

Review Period.......... |----------------|

if so the then your criteria would be

WHERE reviewEnd>caseStart AND reviewStart<nz(caseEnd,#12/31/2999#)

Case1..............True...............................False
Case2..............True...............................True
Case3..............True...............................True
Case4..............True...............................True
Case5..............True...............................True
Case6..............False..............................True

Note the treatment of caseEnd for null - null means it is still open, so put in a date which is way into the future
 

dal1503

Registered User.
Local time
Today, 00:15
Joined
Apr 14, 2016
Messages
34
not sure why you need the status field - if there is a closed date, it's closed and if it is null it is open

The main reason for including that field in the query could be put bluntly as 'overkill'. While I'm still figuring out how all my queries work I've been including more fields than necessary to make sure I can keep track of exactly what is happening. Once I grasp it, I remove the redundant parts - as I have now done with my Status field :p

Thanks very much for your help - I actually managed to achieve what I wanted to do a different way (which I probably should have tried before posting and wasting everyone's time :( ). This is my code:

Code:
SELECT tblFamilies.FamilyID, tblFamilies.MainChild, tblFamilies.AllocationDate, tblFamilies.AllocatedWorker, tblFamilies.Status, tblFamilies.ClosureDate, tblFamilies.ClosureReason, tblFamilies.OtherDetails

FROM tblFamilies

WHERE (((tblFamilies.AllocationDate)<[Start of Period]) AND ((tblFamilies.ClosureDate) Between [Start of Period] And [End of Period])) 

OR (((tblFamilies.AllocationDate)<[Start of Period]) AND ((tblFamilies.Status)="Open")) 

OR (((tblFamilies.AllocationDate)>=[Start of Period]) AND ((tblFamilies.ClosureDate)<=[End of Period])) 

OR (((tblFamilies.AllocationDate)>=[Start of Period]) AND ((tblFamilies.Status)="Open")) 

OR (((tblFamilies.AllocationDate) Between [Start of Period] And [End of Period]) AND ((tblFamilies.ClosureDate)>[End of Period])) 

OR (((tblFamilies.AllocationDate)<[Start of Period]) AND ((tblFamilies.ClosureDate)>[End of Period]));

This way, the user enters the starting date and ending date once when prompted, and the query then recognises "Start of Period" and "End of Period", which is exactly what I wanted.

Is this what the solution you posted would do? Sorry if I sound ignorant, but while I'm still learning it's easier for me to do things the long way round first before I can start simplifying my code, but I'm always looking for ways to things quicker and easier!

Thanks for your help :)
 

plog

Banishment Pending
Local time
Yesterday, 19:15
Joined
May 11, 2011
Messages
11,613
Correct me if I am wrong but you want to see if 1 date period (A1-A2) overlaps with another date period (B1-B2). Right?

This would do it:

A1<=B2 AND A2>=B1
 

dal1503

Registered User.
Local time
Today, 00:15
Joined
Apr 14, 2016
Messages
34
Correct me if I am wrong but you want to see if 1 date period (A1-A2) overlaps with another date period (B1-B2). Right?

This would do it:

A1<=B2 AND A2>=B1

No, what I want to do is display all cases that were 'Open' at any point during Quarter 1 (April 1st - June 30th), but I will also need to do this for future Quarters. I could make a separate query for each Quarter with set dates specified in the query which would do the job, but I thought it would be easier to have one query with interchangeable dates that could be specified by the user at the time of running the query.

Apologies if I wasn't clear in my original post, thank you for your reply anyway :)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:15
Joined
Feb 19, 2013
Messages
16,553
Is this what the solution you posted would do?
yes - and plogs suggestion is pretty much the same

your users would be prompted for reviewStart and reviewEnd dates

good luck with your solution
 

plog

Banishment Pending
Local time
Yesterday, 19:15
Joined
May 11, 2011
Messages
11,613
No, what I want to do is display all cases that were 'Open' at any point during Quarter 1 (April 1st - June 30th)

I love it when people disagree with me then go on to explain why; without realizing it, that I was correct.

...you want to see if 1 date period (A1-A2) overlaps with another date period (B1-B2).

Let's restate the above using your data instead of generic variables:

you want to see if 1 date period (4/1/2016 - 6/1/2016) overlaps with another date period (AllocationDate - ClosureDate). Right?
 

dal1503

Registered User.
Local time
Today, 00:15
Joined
Apr 14, 2016
Messages
34
I love it when people disagree with me then go on to explain why; without realizing it, that I was correct.



Let's restate the above using your data instead of generic variables:

you want to see if 1 date period (4/1/2016 - 6/1/2016) overlaps with another date period (AllocationDate - ClosureDate). Right?


Oops! My apologies, sometimes I need an example to slap me in the face before I understand them :(

Your solution seems like it would do exactly what I want. Sorry for asking what might be a simple question, but what exactly would my "WHERE" clause be, based on your solution and using my field names?

I hope I'm not asking too much for you to type it out
 

plog

Banishment Pending
Local time
Yesterday, 19:15
Joined
May 11, 2011
Messages
11,613
Give it a shot, it's simple substitution of the expression I used in my first post. When/if it doesn't work out, post back what you tried.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:15
Joined
Feb 19, 2013
Messages
16,553
I'm dropping out of this one. Provided an example and what the results would be and still the OP doesn't understand. Guess my communication skills aren't up to it
 

dal1503

Registered User.
Local time
Today, 00:15
Joined
Apr 14, 2016
Messages
34
I'm dropping out of this one. Provided an example and what the results would be and still the OP doesn't understand. Guess my communication skills aren't up to it

Apologies for not understanding your example. As a beginner I find it difficult sometimes to understand examples that don't include my field names as it gets lost in translation. I also am not familiar with the Nz function, so I struggled to grasp what the line of code you posted would do.

This is the code that I tried, based on your example:

Code:
SELECT tblFamilies.FamilyID, tblFamilies.MainChild, tblFamilies.AllocationDate, tblFamilies.AllocatedWorker, tblFamilies.Status, tblFamilies.ClosureDate, tblFamilies.ClosureReason, tblFamilies.OtherDetails
FROM tblFamilies
WHERE [End of Period]>tblFamilies.AllocationDate AND [Start of Period]<nz(tblFamilies.ClosureDate,#31/12/2999#);

One of my dummy records that had a 'closed' status should have been returned as the ClosureDate was between the two dates that I specified, but instead the query only displayed cases with an 'open' status.

Am I missing something from my code, or did I not explain what I was trying to achieve properly, meaning that the solution you posted is trying to return a different results set to what I need?

Again, thank you for your help, I appreciate it.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:15
Joined
Feb 19, 2013
Messages
16,553
my solution is intended to return all records which are open at any point during the selected period per the schematic I provided.

Suggest post the details of the one record which was not returned - start and end time plus the review period

The one change you might want to make to the code is to change the > and < to >= and <=respectively which will then also include records which closed on the first day of the review period or opened on the last day
 

dal1503

Registered User.
Local time
Today, 00:15
Joined
Apr 14, 2016
Messages
34
Give it a shot, it's simple substitution of the expression I used in my first post. When/if it doesn't work out, post back what you tried.

Correct me if I am wrong but you want to see if 1 date period (A1-A2) overlaps with another date period (B1-B2). Right?

This would do it:

A1<=B2 AND A2>=B1

This was my attempt at using your expression:

Code:
SELECT tblFamilies.FamilyID, tblFamilies.MainChild, tblFamilies.AllocationDate, tblFamilies.AllocatedWorker, tblFamilies.Status, tblFamilies.ClosureDate, tblFamilies.ClosureReason, tblFamilies.OtherDetails
FROM tblFamilies
WHERE [Start of Period]<=tblFamilies.ClosureDate AND [End of Period]>=tblFamilies.AllocationDate;

The good news: The query returned the dummy record whose ClosureDate was within the two dates I specified, and excluded the dummy record where the ClosureDate was before the two dates specified, as it should.

The not-so-good news: The query did not return any of my dummy records that had an 'open' status.

Any suggestions as to where I might be going wrong? :confused:

Thanks
 

dal1503

Registered User.
Local time
Today, 00:15
Joined
Apr 14, 2016
Messages
34
my solution is intended to return all records which are open at any point during the selected period per the schematic I provided.

Suggest post the details of the one record which was not returned - start and end time plus the review period

The one change you might want to make to the code is to change the > and < to >= and <=respectively which will then also include records which closed on the first day of the review period or opened on the last day


I changed to >= and <= but still no luck.

These are the 5 dummy records I am using, and whether they were returned or not:

FamilyID.......AllocationDate.........Status........ClosureDate
1..................01/04/2016.............Open
2..................01/04/2016.............Closed........02/06/2016
3..................01/01/2016.............Open
4..................01/01/2016.............Closed........02/02/2016
5..................01/01/2016.............Open


The review period being used is 01/04/2016 - 30/06/2016.

The query should return records 1, 2, 3 and 5, as these were all open at some point during that period.

However, the query only returns records 1, 3 and 5.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:15
Joined
Feb 19, 2013
Messages
16,553
Think it is to do with date formats

You are entering parameters (review start and end) but not declaring their type (as datetime)

A workaround is to explicitly convert the parameters to dates

you can either modify your where clause to

Code:
 WHERE [COLOR=red]CDate([/COLOR][End of Period][COLOR=red])[/COLOR]>=tblFamilies.AllocationDate AND [COLOR=red]CDate([/COLOR][Start of Period][COLOR=red])[/COLOR]<=nz(tblFamilies.ClosureDate,#31/12/2999#)
or declare the parameter types - can be done in the query builder or add this line (including the semi colon) to the top of your
Code:
 PARAMETERS [End of Period] DateTime, [Start of Period] DateTime;
 

dal1503

Registered User.
Local time
Today, 00:15
Joined
Apr 14, 2016
Messages
34
Think it is to do with date formats

You are entering parameters (review start and end) but not declaring their type (as datetime)

A workaround is to explicitly convert the parameters to dates

you can either modify your where clause to

Code:
 WHERE [COLOR=red]CDate([/COLOR][End of Period][COLOR=red])[/COLOR]>=tblFamilies.AllocationDate AND [COLOR=red]CDate([/COLOR][Start of Period][COLOR=red])[/COLOR]<=nz(tblFamilies.ClosureDate,#31/12/2999#)
or declare the parameter types - can be done in the query builder or add this line (including the semi colon) to the top of your
Code:
 PARAMETERS [End of Period] DateTime, [Start of Period] DateTime;

Your first option seems to work perfectly and saved me quite a few lines of code, so thank you :D

Just out of curiosity in case I need to use it in the future, the code in your second option - is this just added at the very top of the code in SQL view?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:15
Joined
Feb 19, 2013
Messages
16,553
yes just at the top - if you use the query builder on the ribbon you will see an option for parameters - complete there and the code will be added automatically.

benefit of the second option is that you are telling access what datatype - whereas the first option is converting each value. So technically the first option is doing more work and will be slower than the second option, but probably only apparent with large datasets.

It is generally better to predeclare parameters, and I think in later versions of access (2013+) it is a requirement.
 

dal1503

Registered User.
Local time
Today, 00:15
Joined
Apr 14, 2016
Messages
34
yes just at the top - if you use the query builder on the ribbon you will see an option for parameters - complete there and the code will be added automatically.

benefit of the second option is that you are telling access what datatype - whereas the first option is converting each value. So technically the first option is doing more work and will be slower than the second option, but probably only apparent with large datasets.

It is generally better to predeclare parameters, and I think in later versions of access (2013+) it is a requirement.


I see. Thanks very much for your help and advice.
 

Users who are viewing this thread

Top Bottom