Need help with a query

[Roonaldo]

Hi,

I have created a database which keeps track of a companies boats that it owns amongst other things. This company has 6 boats which are available or hire. Im trying to do a query which would tell me which boats will be available on a certain date.
I have 3 tables in order to help me.
I have a booking table which contains information such as the start date of the hire, the duration of the hire.
Then there is a boat hire table which contains a field with the name of the boat and a primary key field called Hire ID
The third table is called Boat details which contains the name of the boat
The boat hire table which contains the hire id field is linked to a hire id field (foreign key) in the booking table
And the primary key field in the boat details table (Boat name) is linked to the boat name in the Boat hire table.
I hope this is clear!

Can anyone tell me how i could go about finding the boats that are available on a certain date?

Many thanx

Ronald

 

[BryanT]

Hello Roonaldo

Try something like this in the WHERE clause of your SELECT statement

WHERE ((DateDiff("d",[HireStartDate],Date())>0)AND (DateDiff("d",[HireEndDate],Date())>0))

The section "((DateDiff("d",[HireStartDate],Date())>=0)" will give you hires that started prior to or on the date you want, while the
"(DateDiff("d",[HireEndDate],Date())>0))" section will return those hires completed before the date you require.Therefore the combination should return boats available on that day.


Bryan

 

[Roonaldo]

Cheers, thanks for replyin, i will have a go with that

Ronald

 

[BryanT]

Glad to be of some help.

Bryan