Query for car rental database

[Z3HR44N]

Hi there,

I am new to this site so please excuse me if this question is in the wrong area.

My database is for a a car rental business. in one of my forms there are two fields called "Date OUT" and "DATE IN" this is basically when the customer would like the car and when they will be returning the car.

Beneath these two fields there is a drop down list of all the cars the business owns. what i would like to do is to only show certain cars depending on the date selected in "Date OUT"

For example one customer might choose a BMW and they might borrow it on the 18th of January to the 25th of January.
If another customer comes along and says that they want a car on the 23rd of January then that BMW should not come up in the drop down list as it will not be available.

 

[namliam]

This question is far more complex than you make it sound, as there are multiple "rental records", plus when the database is properly designed you will not know the make of the car with the rental rental records.
On top of which you will need to know the end date of the new rental as well, if they want to rent the BMW from Jan 28 till Feb 10th, but there is already a rental out there starting Feb 8th, you will have a problem.

Then it will be an outer join to find the cars that are available in that time period.
To find the cars that are not available:

Select *
from Yourtable
Where DateOut <= NewDateIn
and DateIn >= NewDateOut

I hope this makes some sence to you

 

[Mihail]

Create a query that show all the cars and the two dates.
Add to this query a new column (field) : IsAvailable: fncIsAvailable([DateOUT],[DateIN])
BTW: Do not use names with spaces or special characters.
Put True as Criteria for this field.

Now, use the VBA editor in order to create a new VBA module.
In this module design the function:

Public Function fncIsAvailable(DateOUT As Date, DateIN as Date) As Boolean
Dim RequestedDate As Date
 RequestedDate = Forms!YourFormName.ControlName 'the control where you input the date
 fncIsAvailable = True

 If RequestedDate < DateOUT Then Exit Function
 If RequestedDate > DateIN Then Exit Function

 fncIsAvailable = False
End Function

Open YourFormName form, input a date in ControlName control, move the focus from that control anywhere in the form (by clicking other control) then run the query.

Note that SQL guys might have other solution based (I think) on Not Between approach.
Just I am not comfortable with the Between function.

Good luck !

 

[Mihail]

Now I see the namliam's post.
While it is a lot simple that my approach, is not as simple to modify it in order to contain all the possibilities (see also namliam's post).
But, if you will use an external function you have all the necessary flexibility in order to return True or False to your query.

 

[jdraw]

Here is a free video that shows a technique to use 2 listboxes on a form. You could use this to show Available vehicles perhaps.