Query that may include NULLS (????)

[rickmason]

Hi gang, Here is my delima and I cannot find anyone who knows the answer so you are my last hope.

My DB consist of the following tables:
Table1: LName FName DateofHire TermDate
Table2: Unit StartDate EndDate


This DB tracks what unit nurses have worked on since date of hire via a form with subform. Here is what the data might look like:
Employee DOH Term
Jane Doe 01/01/01

UNIT Start End
ESD 01/01/01 05/03/01
CCU 05/04/01

As you can see, Her second record on the subform has a blank end date due to she is still working in CCU.
The Admin of the DB may want to see who worked in the ESD Unit in the month of April 01, The query should pull Jane doe because April 01 fell between her ESD start and End date. IF I want to see who worked in CCU during the month of 07/01/01, the query should also pick up Jane Doe because she is still active in CCU. I have set up a parameter query using the Between function along with others, and cannot seem to come up with a solution that will pick up any scnerio whether there is a Null or not. Can someone please help with this???? Also, Please be very basic with any descriptions and or examples as I not an expert in Access. Looking forward to hearing from someone.

 

[KenReay]

Hi

In your query, instead of using the 'raw' enddate use MyEndDate:Nz(EndDAte,"12/31/2999&quot

so we are saying she ends her stint in (say) CCU at some point in the future , but a suitable BETWEEN condition

will still pick her out eg

SELECT ... WHERE #04/01/2003" BETWEEN StartDAte AND Nz(EndDAte,"12/31/2999&quot

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[rickmason]

Ken, Sorry for the simplistic question but, Where would the Nz statement go? In the criteria of the Query?? Is this how the query should look:

Unit Start End
Criteria [Unit] <=[Start] Nz(End,&quot;12/31/2999&quot

Thanks again Ken

 

[KenReay]

Hi

Yes, that is I think so, not sure what notation you are using, I had to guess at how you had structured your query, but I think you can see the point the NZ() function cheats by subsituting a value for a null value, and by choosing a date a long time in teh future we are sying effectively the person is still there in that department

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TonyJollans]

Hi Rick,

This seems a bit confusing. In your first post you say you have set up a query using Between - I imagine this to have been something along these lines ...

WHERE #07/01/01# BETWEEN StartDate AND EndDate

Ken's suggestion was good - to allow for a null end date you should use an NZ function - and he gave an example of its usage ...

WHERE #07/01/01# BETWEEN StartDate AND Nz(EndDate,#12/31/2999#)

In your reply you seem to suggest you are using the Query Builder and give a very confusing example which doesn't seem to relate to your first post. To put the above SQL in the Query Builder do not put any criteria on your dates, but add another column ..

Field: #07/01/01# (or whatever date you want to check)
Show: No (i.e. do NOT tick the box)
Criteria: BETWEEN StartDate AND Nz(EndDate,#12/31/2999#)

Enjoy,
Tony

 

[lespaul]

What I'd like to know is how the two tables are related?

Table1: LName FName DateofHire TermDate
Table2: Unit StartDate EndDate

Am I missing something here? How can you tell from these two tables which nurse worked in what Unit?

I would expect:
tblEmployee(EmpID, Name, DOH, TermDate, etc)
tblUnits(UnitID, UnitName)
tblNurseUnits(EmpId, UnitID, StartDate, EndDate)

From the two tables (and fields) you listed above there's no way to write a query to determine which employees are working in which units.


Leslie

 

[rickmason]

I am sorry for the confusion guys, The &quot;Unit&quot;, &quot;StartDate&quot;, and &quot;EndDate&quot; need to be setup in a parameter query to let the user select the data they want to query. The big hurdle here is my experience with this type of search. I get the feeling from the post that this is probably not a complicated task to achieve but I just need some extra details as to Excatly how the query would be set up. Again everyone I appreciate the assistance and apologise I need more basic instruction as to setting up the query. Lespaul, I am using an ID Number to link the two tables.

 

[TonyJollans]

Hi Rick,

From the top ..

You have two tables .. Table1 (people) and Table2 (where and when they worked - which might have a Null EndDate).
You have a Form / Subform via which you maintain these details.

You want a separate query based on these tables which takes user input of Unit, Start Date and End Date and which will return all the people who worked in that unit between those dates - originally you implied just one user input date but from your last post it seems there are two.

So - create a form with three textboxes (or maybe comboboxes) - one for each input field - and a button to run a query. Call the Form, say, InputForm and call the textboxes, say, InputUnit, InputStartDate and InputEndDate. Put some basic validation on your form so that, for example, StartDate and EndDate are both always entered and End Date is greater than (or equal to) StartDAte. Use the wizard to create the button and make it run the query.

Now create a Query based on your two tables and join them on your ID number. Add whatever fields you want in the query builder and then add criteria as follows:

Column: Table2.Unit
Criteria: =[Forms]![InputForm]![InputUnit]

Column: Table2:StartDate
Criteria: <=[Forms]![InputForm]![InputStartDAte]

Column: Table2:EndDate
Criteria: Is Null Or >=[Forms]![InputForm]![InputEndDAte]

I've just typed this in cold so I could have made a typo or two but hopefully it will put you on the right track.

Enjoy,
Tony

 

[KenReay]

Hi

TonyJollans, have you got the >= the wrong way round?, I think this will exclude the dates in the range, not include them ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TonyJollans]

Hi Ken,

I think the operators are correct but the operands are wrong

What about ..

Column: Table2:StartDate
Criteria: <=[Forms]![InputForm]![InputEndDAte]

Column: Table2:EndDate
Criteria: Is Null Or >=[Forms]![InputForm]![InputStartDAte]

Enjoy,
Tony

 

[rickmason]

Tony, If IS NULL or >=[Forms]![InputForm]![InputStartDAte]
is used, Won't it pull ALL Nulls in the table even if it is not in the date range the user selects? Rick

 

[TonyJollans]

Hi Rick,

Only those records which satisfy the other criteria - in particular those with a start date before the input end date (which I think are the ones you want).

Enjoy,
Tony