Find bookings for a date

  • Thread starter Thread starter SimonDeLikeable
  • Start date Start date
S

SimonDeLikeable

Hi
I have a table called Bookings, which has a FromDate and ToDate. I want
a query to show what bookings I have on a particular date. For example, who
would I show which Bookings I have for the 26th February?
Thanks
Diarmuid
 
I suggest that it would be better if you leave only "Date" column in your
table.
You should design a form in which there will be 2 fields, named "FromDate"
and "ToDate"...

The query based on that form should look like this:
SELECT [columns in your table] FROM Bookings WHERE Date BETWEEN Forms![name
of designed form]![FromDate] AND Forms![name of designed form]![ToDate];

Then you can run Report Wizard to create a report that will display found
data.
Hoping this will help.
 
I need to leave the two dates in the Bookings table, so unfortunately this
won't work.

Maciek Paras said:
I suggest that it would be better if you leave only "Date" column in your
table.
You should design a form in which there will be 2 fields, named "FromDate"
and "ToDate"...

The query based on that form should look like this:
SELECT [columns in your table] FROM Bookings WHERE Date BETWEEN Forms![name
of designed form]![FromDate] AND Forms![name of designed form]![ToDate];

Then you can run Report Wizard to create a report that will display found
data.
Hoping this will help.

SimonDeLikeable said:
Hi
I have a table called Bookings, which has a FromDate and ToDate. I want
a query to show what bookings I have on a particular date. For example, who
would I show which Bookings I have for the 26th February?
Thanks
Diarmuid
Click to expand...
Click to expand...
 
I guess I misunderstood you :-)
You meant the dates of beginning and ending of a certain booking...

In this case the query should look like this:
SELECT [list of columns] FROM Bookings WHERE (FromDate >= X AND ToDate <=
Y);

A and Y can be date/time values from a form, or can be determined, e.g. if
you'd like to find records matching today, simply type 'Today()' in places
of X and Y


SimonDeLikeable said:
I need to leave the two dates in the Bookings table, so unfortunately this
won't work.

Maciek Paras said:
I suggest that it would be better if you leave only "Date" column in your
table.
You should design a form in which there will be 2 fields, named "FromDate"
and "ToDate"...

The query based on that form should look like this:
SELECT [columns in your table] FROM Bookings WHERE Date BETWEEN Forms![name
of designed form]![FromDate] AND Forms![name of designed form]![ToDate];

Then you can run Report Wizard to create a report that will display found
data.
Hoping this will help.

SimonDeLikeable said:
Hi
I have a table called Bookings, which has a FromDate and ToDate. I want
a query to show what bookings I have on a particular date. For
Click to expand...
example,
who
would I show which Bookings I have for the 26th February?
Thanks
Diarmuid
Click to expand...
Click to expand...
Click to expand...
 
That won't work either!
If the date I want is 26/02/04. I have a Booking with FromDate = 25/02/04
and ToDate = 27/02/04
In this case, 27/02/04 <= 26/02/05 would fail, so it wouldn't appear in the
query.
Diarmuid

Maciek Paras said:
I guess I misunderstood you :-)
You meant the dates of beginning and ending of a certain booking...

In this case the query should look like this:
SELECT [list of columns] FROM Bookings WHERE (FromDate >= X AND ToDate <=
Y);

A and Y can be date/time values from a form, or can be determined, e.g. if
you'd like to find records matching today, simply type 'Today()' in places
of X and Y


SimonDeLikeable said:
I need to leave the two dates in the Bookings table, so unfortunately this
won't work.

Maciek Paras said:
I suggest that it would be better if you leave only "Date" column in your
table.
You should design a form in which there will be 2 fields, named "FromDate"
and "ToDate"...

The query based on that form should look like this:
SELECT [columns in your table] FROM Bookings WHERE Date BETWEEN Forms![name
of designed form]![FromDate] AND Forms![name of designed form]![ToDate];

Then you can run Report Wizard to create a report that will display found
data.
Hoping this will help.

Hi
I have a table called Bookings, which has a FromDate and ToDate. I
want
a query to show what bookings I have on a particular date. For example,
who
would I show which Bookings I have for the 26th February?
Thanks
Diarmuid
Click to expand...
Click to expand...
Click to expand...
 
Sorry, change this to:
SELECT [list of columns] FROM Bookings WHERE (FromDate <= X AND ToDate >=
Y);

I't my fault :-/

Let me know if it worked OK
 
Hi guys!

I think that the solution given by Maciek was right. Let's see 3 examples:
Bookings: 01/01/01 - 01/12/01 FromDate = 01/01/01 < 02/27/04 - True
............................. ToDate = 01/12/01 < 02/27/04 - False (T, F)-->
F
01/01/04 - 01/12/04 FromDate = 01/01/04 < 02/27/04 - True
............................. ToDate = 01/12/04 > 02/27/04 - True (T,T)--> T
01/01/06 - 01/12/06 FromDate = 01/01/06 > 02/27/04 - False
............................ ToDate = 02/27/04 > 02/27/04 - False (F,F)--> F

The query will display only second period of time because both conditins
have TRUE values.
Mr Diarmuidq, please tell us what causes the problem.
Did you try to run this query against the table in Access Query Biulder?
Please, can anybody correct me if I'm wrong?


diarmuidq said:
This won't work either.

Maciek Paras said:
Sorry, change this to:
SELECT [list of columns] FROM Bookings WHERE (FromDate <= X AND ToDate =
Y);

I't my fault :-/

Let me know if it worked OK
Click to expand...
Click to expand...
 
Well, I think I have it solved now. This is the query I ended up using
SELECT Bookings.* FROM Bookings WHERE
((([Forms]![frmBookingMenu]![txtBookDate] Between [Book_FromDate] And
[Book_ToDate])=True));

It seems kind of obvious now, but it was driving me mad! Hopefully the query
makes it clear what I was trying to do.
Thanks Jason and Maciek for the help.
Diarmuid


JasonS said:
Hi guys!

I think that the solution given by Maciek was right. Let's see 3 examples:
Bookings: 01/01/01 - 01/12/01 FromDate = 01/01/01 < 02/27/04 - True
............................ ToDate = 01/12/01 < 02/27/04 - False (T, F)-->
F
01/01/04 - 01/12/04 FromDate = 01/01/04 < 02/27/04 - True
............................ ToDate = 01/12/04 > 02/27/04 - True (T,T)--> T
01/01/06 - 01/12/06 FromDate = 01/01/06 > 02/27/04 - False
........................... ToDate = 02/27/04 > 02/27/04 - False (F,F)--> F

The query will display only second period of time because both conditins
have TRUE values.
Mr Diarmuidq, please tell us what causes the problem.
Did you try to run this query against the table in Access Query Biulder?
Please, can anybody correct me if I'm wrong?


diarmuidq said:
This won't work either.

Maciek Paras said:
Sorry, change this to:
SELECT [list of columns] FROM Bookings WHERE (FromDate <= X AND ToDate =
Y);

I't my fault :-/

Let me know if it worked OK
Click to expand...
Click to expand...
Click to expand...
 
Back
Top