Query Dates between two fields.

[abcman]

Hi All and thanks in advance for the help.

I created a database with the following fields :
PatientName which is a text
DateIn which is a date
DateOut which is also a date

If the patient was in on 01/01/2012 and he was out on 10/01/2012 In the format (DD/MM/YYYY) it is so easy to get the names of people between the exact such dates.

How can I get this list of names for patients between 03/01/2012 and 08/01/2012 As you can see that the patient(s) is still in within this period but the database does not show him because I have to provide the exact date. Can you please please help me on this. Thanks.

 

[pr2-eugin]

Hello abcman, you can try the Date condition for the two fields by using the OR.. something along the lines of..

SELECT * FROM theTableName
WHERE ((DateIn Between [StartDateParameter] And [EndDateParameter]) OR (DateOut Between [StartDateParameter] And [EndDateParameter]))

Not tested, but in theory should work..

 

[abcman]

No I am sorry this did not work out and there was no result. Is there like a criteria I can add on my query that will provide the answer ? Thanks again all for the hand of help.

 

[pr2-eugin]

Oh.. I just tested the above query and I got the expected result..

 

[Brianwarnock]

You might want to read this

http://www.baldyweb.com/OverLap.htm

Brian

 

[abcman]

Oh.. I just tested the above query and I got the expected result..

Hi and thanks. I think that I am doing something wrong and that is why I am not getting the same result. Would I be so rude if I ask you to send me the sample that you have showed here as an attachment ?. I am sure then I will know what is going wrong. Thanks in advance.

 

[Brianwarnock]

You need to read the link I posted, pr2's solution does not cover all the situations.

Brian

 

[pr2-eugin]

Brian is correct, my code will fail for the last condition according to the link.. i.e. if the date range "Begins before and ends after" the given range.. taking my example.. If I give the range 02/02/2013 and 07/02/2013, I would not get the record 1 in the result..

The Query that is posted in Paul's site is more simple than mine TBH..

 

[Brianwarnock]

Paul,
I posted the link rather than the code as I believe the full explanation is worth reading, I found baldy web after I had wrestled with the logic and solved the issue myself, but I benefited from seeing Paul Baldy's diagram.

Brian

 

[pr2-eugin]

Paul,
I posted the link rather than the code as I believe the full explanation is worth reading

i could not disagree more Brian, I thought my solution was the right one until I myself went through the site.. It does help.. abcman, you might as well go through the link, as it is quiet informative and help you understand why my code is not right..

 

[abcman]

Thanks Guys, It is logic for sure. I had a look at your site yesterday on what you have posted. It is not easy for me to transfer that into a code as I am not an expert in VB. Anyway. Thanks Paul and Brian.

 

[pr2-eugin]

abcman, there is no vb involved in the query.. You will be able to create the query from the example given in the link..

 

[Keiath]

Following on from this how would you make this front end user friend, ie: when you click to run the query it ask you a start date then a end date

 

[pr2-eugin]

Hello Keiath, you can have it two ways :
1. Create an Unbound Form that will obtain the input from the user and then, use the Form controls in Query, on button click..
2. Use Parameters in the Query that will prompt for the Input when the Query is run..

The first method is a bit more stable, as you can actually validate the input before sending them to the Query..