Access date Query

[Robeen]

I created a query on tblBooking - with this SQL:

 
SELECT tblBooking.Booking_ID, tblBooking.Guest_ID, tblBooking.Plot_ID, tblBooking.[Paid?], tblBooking.StartDate, tblBooking.EndDate, tblBooking.TotalCost, IIf(Date() Between [StartDate] And [EndDate],"Booked","Available") AS BookedToday
FROM tblBooking;

Let me know if this works for you.

[Brocco]

yeah today

[Robeen]

see if you can plug that SQL from my previous post into a query on tblBooking & run it.

[Brocco]

there all going to be available anyway cause i've no bookings on todays date.
lets say I'll take the 15th of November '11. Can i edit this to bring all others back in the query except for the 10th - 18th of November booking ?

[Robeen]

Try this - instead of Today - it is checking for 7 days ago:

 
SELECT tblBooking.Booking_ID, tblBooking.Guest_ID, tblBooking.Plot_ID, tblBooking.[Paid?], tblBooking.StartDate, tblBooking.EndDate, tblBooking.TotalCost, IIf(DateAdd("d",Date(),-7) Between [StartDate] And [EndDate],"Booked","Available") AS BookedOnNov15
FROM tblBooking;

Let me know if that works.

[Brocco]

When I enter that code it still brings back every Booking...

fml

[Brocco]

it does however say booked next to two of the Bookings

[Robeen]

Yes- that is what I did with the Extra field.
You can put "Booked" in the Criteria for that last field and then you'll only see the Booked Plots

[Brocco]

the next step would be to hide the those two Bookings and keep the ones which are not booked

[Brocco]

I need to see the ones which aren't booked :S

[Robeen]

put "Available" in the Criteria for the last field.
Later - you can uncheck this field in the query if you don't want to see it.

[Brocco]

ok that worked, I'll copy that to my Proper database and hopefully that'll work, I'll let you know

[Robeen]

All the best!

[Brocco]

You Sir/Madam are a Genius !!!

Thanks alot for your help over the last few hours, would still be failing myself without ya!