473,325 Members | 2,342 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Date/time problem

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
);
Nov 13 '05 #1
1 1797
E. Liepins wrote:
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
);


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()

Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jay | last post by:
I previously posted this question under Visual Basic newsgroup, but was advised to re-post here. I'm hoping someone can help me solve an issue I'm having with VB.Net and Access 2000. Here's...
7
by: vnl | last post by:
I'm trying to run a SQL query but can't find any records when trying to select a certain date. Here's the sql: SELECT field 1, field2, date_and_time, FROM table1 WHERE date_and_time =...
1
by: Raghu | last post by:
Hello... I am running into a problem while running a query..can some1 help.. this is the query : ************** SELECT * from Table S where S.dtDate1 BETWEEN...
18
by: Robin Lawrie | last post by:
Hi again, another problem! I've moved from an Access database to SQL server and am now having trouble inserting dates and times into seperate fields. I'm using ASP and the code below to get the...
2
by: Riegn Man | last post by:
I have a problem with access and our time clocks. We have time clocks that put out a .log file with the badge swipes for everybody. There is one .log file for each day. I am pulling that data...
12
by: Assimalyst | last post by:
Hi, I have a working script that converts a dd/mm/yyyy text box date entry to yyyy/mm/dd and compares it to the current date, giving an error through an asp.net custom validator, it is as...
44
by: user | last post by:
Hi, Let's say I have 2 dates in the b/m format: Date 1 and date 2 How do I check whether Date2 is later than Date 1? Date1. 21-Nov-2006 09:00:00 PM
6
by: Geoff Cox | last post by:
Hello, at the moment I can add the combined date and time into MySQL using php $dt1 = date("Y-m-d H:i:s"); is it possible to add the date and time separately? I thought it might be
10
by: WebCM | last post by:
There is a function: http://paste.ubuntu.com/21865 It needs GMT date in YYYY-MM-DD HH:MM:SS format - in SQL: datetime. If date is the same as today, the function returns "Today". There is one...
16
by: W. eWatson | last post by:
Are there some date and time comparison functions that would compare, say, Is 10/05/05 later than 09/22/02? (or 02/09/22 format, yy/mm/dd) Is 02/11/07 the same as 02/11/07? Is 14:05:18 after...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.