Time and date trouble

[gayano]

HI All,
Im new to access(and im no vb pro too), SOooo Im stuck in a project!! here it goes

I have a two tables
1 Rooms
---------------------(holds room details)
Room no
Room type

2 Reservation
--------------------(Holds reservation)
Room No
Start Date
Start Time
End Date
End Time

from this i like get the reservation detail mostly notreserved details
like: if a room reserved
2007/06/05 12:00 to 2007/06/07 13:00
this room shuold not apear in the serch query results (if i serched for free roms
between 2007/06/05/ any time to 2007/06/06 any time

please bsomebody help

[gayano]

I tried this

SELECT Room No
FROM Rooms
WHERE RoomNo Not In (
Select RoomNo from Reservation
Where [SDate] + [Stime] Between [ssdate] + [sstime] And [dddate] + [ddtime]);

But the thing is it works with the date but it wont calculate time

whats the problem??

[kepston]

I tried this

SELECT Room No
FROM Rooms
WHERE RoomNo Not In (
Select RoomNo from Reservation
Where [SDate] + [Stime] Between [ssdate] + [sstime] And [dddate] + [ddtime]);

But the thing is it works with the date but it wont calculate time

whats the problem??

If you leave the time blank, it will not calculate correctly. Enter [sstime] as 0:00 and [ddtime] as 23:59.
or change your SQL to

Where [SDate] + [Stime] Between [ssdate] +Nz([sstime]) And [dddate] + Nz([ddtime])

Nz() converts Null to zero

[gayano]

If you leave the time blank, it will not calculate correctly. Enter [sstime] as 0:00 and [ddtime] as 23:59.
or change your SQL to

Where [SDate] + [Stime] Between [ssdate] +Nz([sstime]) And [dddate] + Nz([ddtime])

Nz() converts Null to zero

kepston,

Thank you for your quick reply,

Tried Your Code The result is the same, Even if I Input the Time using the parameter input.

Here is my Resrvation Table Look like:

Room No Start Date Start Time End Date Ennd Time
.................................................. .................................................. ..............

123 2007/07/06 10:00 2007/07/06 12:00
1234 2007/07/06 13:00 2007/07/06 20:00



If I use the code i earlier mentioned. ( the parameter inputs are Start Date = 2007/07/06, End Date = 2007/07/06, Start Time 09:00, End Time 11:30)
then result should be ..

Room No
-----------------
1234

Isn't It?? But the result shows both Room No 123 and 1234

Any Help ..................

[MMcCarthy]

Try this ...

SELECT Room No
FROM Rooms
WHERE RoomNo Not In 
(Select RoomNo from Reservation
WHERE ([Start Date] Between [ssdate] And [dddate])
AND ([Start Time] Between [sstime] And [ddtime]))
 

[gayano]

It went OK When I used it on a form,
Thank you Thank you Thank you for the replys's

[MMcCarthy]

It went OK When I used it on a form,
Thank you Thank you Thank you for the replys's

No problem, you're welcome.

[kepston]

I wonder why my solution didn't work for gayano.
I tried it successfully before I posted.

[MMcCarthy]

I wonder why my solution didn't work for gayano.
I tried it successfully before I posted.

I don't think concatenating the two fields would necessarily give a timestamp unless they are formatted.

[kepston]

I don't think concatenating the two fields would necessarily give a timestamp unless they are formatted.

If the data type is DateTime, then surely the + operator will add the underlying serial number for the date to the fractional number for the time, producing a new DateTime serial number. Or is it that Access takes the parameter as a string and has to infer the data type?

[MMcCarthy]

If the data type is DateTime, then surely the + operator will add the underlying serial number for the date to the fractional number for the time, producing a new DateTime serial number. Or is it that Access takes the parameter as a string and has to infer the data type?

It should work but there are issues with the formatting of dates in Access and this kind of concatenation would revert the dates to the default format. Thats why I said you would need to impose a format when concatenating the date and time.

[kepston]

Further examples of how and when:
Literal DateTimes and Their Delimiters (#). Article by NeoPa
VBA Filter by date

Thanks to the wise, and knowledgeable Mary and NeoPa.