Date intervals

  • Thread starter Thread starter Eva
  • Start date Start date
E

Eva

Hi

I have got 2 dates that need to be stored on my access
database. arrival_date and departure_date. these values
are entered by a user who is making a room booking. What
i wanted to know is if there is a function in vb.net that
recognises this as an interval and therefore stops
bookings for the same room being made that falls between
this interval.

eg:

Booking 1 for room 1: arrival_date = 1/1/2000
deputure_date = 10/1/2000

Booking 2 for room 1: arrival_date = 5/1/2000
deputure_date =15/1/2000

the second booking will be allowed in my DB as it unique
although the room is not even available. what can i do to
avoid this?

any help would be grateful.
 
Hi Eva,

Do you literally mean a VB.NET function?

ArrivalDate = #20/12/2003#
LeavingDate = #02/01/2004#

If NewBookingDate >= ArrivalDate _
And NewBookingDate <= LeavingDate Then
MsgBox ("Sorry, that room is taken.")
End If

or do you mean a database query type of test?

Regards,
Fergus
 
* "Eva said:
I have got 2 dates that need to be stored on my access
database. arrival_date and departure_date. these values
are entered by a user who is making a room booking. What
i wanted to know is if there is a function in vb.net that
recognises this as an interval and therefore stops
bookings for the same room being made that falls between
this interval.

eg:

Booking 1 for room 1: arrival_date = 1/1/2000
deputure_date = 10/1/2000

Booking 2 for room 1: arrival_date = 5/1/2000
deputure_date =15/1/2000

the second booking will be allowed in my DB as it unique
although the room is not even available. what can i do to
avoid this?
Click to expand...

Why not simply compare the date with the 2 dates?

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
<http://www.mvps.org/dotnet>

Improve your quoting style:
<http://learn.to/quote>
<http://www.plig.net/nnq/nquote.html>
 
Hi fergus,

I meant a Database query test. I want to ensure that only
the rooms that are fully available for the date interval
specified by the user are retrievd from the database and
displayed to the user. i was worried that when the user
makes a booking and enters the arrival_date and
departure_date, that the booking will be allowed even if
it falls between the interval of another booking for the
same room. I want to avoid this scenario by making sure
that the 2dates entered are thought of as an entire
iinterval rather that individual dates.

Any advise??
 
* "Eva said:
I meant a Database query test. I want to ensure that only
the rooms that are fully available for the date interval
specified by the user are retrievd from the database and
displayed to the user. i was worried that when the user
makes a booking and enters the arrival_date and
departure_date, that the booking will be allowed even if
it falls between the interval of another booking for the
same room. I want to avoid this scenario by making sure
that the 2dates entered are thought of as an entire
iinterval rather that individual dates.
Click to expand...

Notice that a separate group for ADO.NET related question is available:

<
Web interface:

<http://msdn.microsoft.com/newsgroup...roup=microsoft.public.dotnet.framework.adonet>

If you don't get an answer here, you may want to post to the group
mentioned above.
 
Hi Eva,

Maybe Fergus is answering, but he went off to bed, he did made a terific
good example for me.

I can try to help you, but we are doing those things mostly in dataset
tables.
Therefore you would have probably to read in a big table of your database,
that is time consuming.

The best you can do, is to l find a solution using a database SQL query.

Those are for an access database less complete than for a full SQL server.

I think that the best thing you can do is placing this question in the
microsoft.public.dotnet.framework.adonet newsgroup (I saw you did not do
that till now).

There is handled a lot of this kind of question. Do you not succeed there,
than message it again here and tell you was in that newsgroup, although I am
a little bit active there also.

Than we can try how we can solve this problem by reading the part of the
database between those dates as a dataset and when we succeed in that, I am
sure we can solve your problem it in that way, using the thing Fergus did
describe.

But first take a look at the other newsgroup please because direct SQL is I
think better for this problem.

And dont forget to tell that you are using a ms access database and when
they tell you that an SQL database is better think
........................................

Cor
 
Hello Eva

I don't know your table structure but here is a suggested approach

RoomID int key
ResDate datetime
Reserved bit

You need a contiguos group of days lets say 4 days

SELECT RoomID FROM Reservations
WHERE (Reserverd = 0)
AND ( ResDate Between '1/1/2003' AND '1/4/2003')
HAVING Count >3


Such a query would give you all the rooms open during that period with 4
days avail.

You can modify this idea to suit your own structure

Ibrahim Malluf
 
Back
Top