| re: Date/time problem
E. Liepins wrote:
[color=blue]
> I am working on a vehicle database. We track when a vehicle is
> borrowed and when it is returned. We also track the number of
> kilometres travelled on a particular trip. There are several tables:
> tblLeaders - lists the people, tblVehicle, tblresults - where the info
> is stored for trips, tblVehicleType- we charge different rates for
> different types of vehicles.
>
> We have 2 main forms frmSignout and frmReturn. My problem occurs when
> I want to sign out the same vehicle more than once on the same date.
> The example would be that John signs out the vehicle at 10 a.m. and
> returns it at 11 a.m. Mary then wants to use the vehicle at 2:00
> until 4:00 the same day. The vehicle doesn't show up in the available
> vehicles list. I'm using a General Date field for all of my dates.
>
> The query used is as follows:
>
> SELECT V.*
> FROM tblVehicle AS V
> WHERE v.vehicleid NOT IN (
> SELECT DISTINCT R.VEHICLEID
> FROM tblResults R
> WHERE R.DateOut BETWEEN dateout.value and date_anticip_return.value
> OR R.Date_Anticip_Return BETWEEN dateout.value and
> date_anticip_return.value
> OR (R.Dateout <= dateout.value AND R.date_anticip_return >=
> date_anticip_return.value)
> or R.DateRtn = 1/1/2004
> );[/color]
If your date field is like Now(), which has both date and time instead
of a date field with no time, you need to make an adjustment most likely
to compare between date and times.
Dates without time, =Date(), have a 00:00 time. So if
DateOut/DateReturn have no time, you are asking for records returned
between the from date at midnight and to date at midnight. Thus if the
dates are the same, your range is 0 seconds...had to be midnight when it
was returned.
It's hard to provide a solution since we don't no what values you are
comparing.
Now if dateout/return are date+time you should be OK. You should at
least have a time in the return time in the return. Ex:
Where DateTimeReturned Between Date() And Now()
| 
