Scheduling Guidance needed (1 Viewer)

[SantoR]

i have 5 departments DeptA,DeptB,DeptC,DeptC,DeptD,DeptE
i have one material MAT1
now i have a form where i am setting some task, FROM date to TO date for the MAT1 in DeptA.

after some days other user can also set FROM date to TO date for the MAT1 in DeptB.

similarly other for other departments...

now i want to write logic where i can check whether theFROM date and TO date are free for this MAT1 or not.

example
MAT1------DeptA ------ 12/06/2015 to 15/06/2015
MAT1------DeptB ------ 14/06/2015 to 18/06/2015 ------not allowed
MAT1------DeptB ------ 17/06/2015 to 20/06/2015 -----allowed

 

[namliam]

Assuming this is in one table, sounds like a simple self join.

What problems are you running into?

 

[SantoR]

problems : i dont know how to do it

i have a table where i am storing all the reservations by a material
ie.
material_name
dept_name
from date
to date

eg:
MAT1------DeptA ------ 12/06/2015 to 15/06/2015

but i dont want any user to book a reservation in any department between these dates for that material. for other material no problems.

ie at any point of time a material will be under one dept only.


so i want to write a code where i will pass these four paramter and in return i should get true/false.

please share how to approach to this problem .

 

[namliam]

Do you know how to make a query?
If so add your table 2 times and make a self join, it will show if the periods overlap if you make it properly...

 

[SantoR]

yes i know how to make query..

but my doubt is...i dont want to enter wrong data in this table

i want to check this information at form level i.e before inserting in the table. through vba

 

[namliam]

Which is even easier...
First step is to make the query... Now open the query in design view and change it to SQL view.

Then make something along the lines of

Dim rs as dao.recordset
set rs=currentdb.openrecordset("Select ...")

If not rs.eof then msgbox "it is busy"
set rs = nothing

 

[SantoR]

got it thanks

 

[spikepl]

Interesting - if you think you can make it just using a self join then i'd love to see it and I think many others could benefit. Please share your solution.

 

[SantoR]

i will not use self join but will use where condition....

 

[JLCantara]

Here is the solution to your problem.

I called this query sqlDateIntervals:

PARAMETERS
prmProduct Long, prmDateFrom DateTime, prmDateTo DateTime;
SELECT
DateIntervals.Product, DateIntervals.DateFrom AS PrmDateMin, DateIntervals.DateTo AS PrmDateMax
FROM
DateIntervals
WHERE
(((DateIntervals.Product)=[prmProduct]) AND ((DateIntervals.DateFrom)<=[prmDateFrom]) AND ((DateIntervals.DateTo)>=[prmDateFrom]))
OR
(((DateIntervals.Product)=[prmProduct]) AND ((DateIntervals.DateFrom)<=[prmDateTo]) AND ((DateIntervals.DateTo)>=[prmDateTo]));


Now the vba:

     Dim qdf As QueryDef
    Dim rst As Recordset
    
    Set qdf = CurrentDb.QueryDefs("sqlDateIntervals")
    qdf.Parameters("prmProduct") = 1
    qdf.Parameters("prmDateFrom") = DateFrom
    qdf.Parameters("prmDateTo") = DateTo
    Set rst = qdf.OpenRecordset
    N = rst.RecordCount

If N=0 -> interval is OK
else -> rst contains the record that interfere with the submited interval.

 

[namliam]

Thank cantara, unfortunatly the sql isnt quite right...

Plus I think the OP has it fixed already.

 

[JLCantara]

Thank cantara, unfortunatly the sql isnt quite right...

First you can make the effort of spelling my family name properly.

Second, what you THING is totally irrelevant: did you bother to try it?
I am not sure you even understand what it does and why it is doing it.

Plus I think the OP has it fixed already.

You thing too much: just stick to facts...

 

[namliam]

I will thank you for getting off your high horse

First you can make the effort of spelling my family name properly.

What did I miss spell? Bar the capitol...
If it is the capitol, I have seen far worse than missing the capitol
If it isnt I dont see where I went wrong

Second, what you THING is totally irrelevant: did you bother to try it?

Being somewhat of a "date guru", I dont need tot try your solution to know there is a situation where it will fail.
In particular, I have a period of 20-Jun-2015 thru 25-Jun-2015 that is already taken.
Now I try and make a new booking for 15-Jun-2015 thru 30-Jun-2015, your query will not return said record.... Thus you will get a double booking.
The fix is easy enough, but fact is your sql is not quite right

You thing too much: just stick to facts...

Another fact povided by the OP

got it thanks

Which indicates to me that he has it fixed.

 

[spikepl]

For anyone else reading this thread, the criteria to determine overlap (or not) are here: http://www.baldyweb.com/OverLap.htm