[LiDoNg_9_0]

hi,i have a project which is room scheduling. i'm having a problem on how to make my query to be able to select the available rooms for the givin time start and time end. ill be happy if you could teach me about it. thank you!

[CharlieMay]

Use a Where clause in your select statement

SELECT {fields} FROM {table} WHERE RoomBooked = 'open' AND RoomDate BETWEEN #startdate# AND #enddate# 

I can't be more specific as you posted no code or any current SQL statements you are trying.

[LiDoNg_9_0]

thank you for the reply. im new to sql statements thats why i didn't provide any codes yet. your reply was a big help to me... arigato!!!

[LiDoNg_9_0]

this is my tblTime table using access database
timeID - Primary
TimeStart - date/time
TimeEnd - date/time

my query is like this

Select timeID FROM tblTime WHERE ((timeID BETWEEN timeStart='7:00:00 am' AND timeEnd='10:00:00 am'))

i want to display the timeID which is between from the time start and time end.. this is what a room scheduling was.

[CharlieMay]

OK, in Access, I think you have to enclose the date/time fields with # instead of '

You also have a problem in the statement. See changes below

Select timeID FROM tblTime WHERE ((timeStart >= #"7:00:00 am# AND timeEnd <=#10:00:00 am#))

This post has been edited by CharlieMay: 11 February 2010 - 06:10 AM

[LiDoNg_9_0]

My system is room scheduling. My plan is this, before adding record to the database, i will check 1st if there's a conflict with the given time and day. So 1st, I should get the room number of the conflict schedules so that i will not include it to the list of available rooms in that given time and day.


//tblSched
SchedID AutoNumber
Room Text
TimeStart Date/Time Format(Short Time)
TimeEnd Date/Time Format(Short Time)
Day Text
/////

////Records
SchedId Room TimeStart TimeEnd Day
1 E101 7:00 10:00 MonWedFri
2 E102 11:00 13:00 MonWedFri
/////

Problem:
I want to display the room number with the givin time start and time end and the day

// This is the 1st query
Select Room FROM tblSched WHERE ((TimeStart >= # 7 :00 # AND TimeEnd <=#10:00 #)) AND (Day Like '%Mon%')

// this the result of 1st query
E101

// This is the 2nd query
SELECT Room FROM tblSched WHERE ((TimeStart >= # 8 :00 #) AND (TimeEnd <= # 9 :00 #)) AND (Day Like '%Mon%')
About this query, this result to a conflict of schedules. What should i do with this?

////Error in SQL
Error in WHERE clause near '#'
Unable to parse query text


i am using the data environment in vb6 to build the query

please help me with this. . . .

[CharlieMay]

For an MS Access database I think you have to use the asterisk for Likes
WHERE Day LIKE '*MON*'

Also...

Quote

// This is the 1st query
Select Room FROM tblSched WHERE ((TimeStart >= # 7 :00 # AND TimeEnd <=#10:00 #)) AND (Day Like '%Mon%')

// this the result of 1st query
E101

// This is the 2nd query
SELECT Room FROM tblSched WHERE ((TimeStart >= # 8 :00 #) AND (TimeEnd <= # 9 :00 #)) AND (Day Like '%Mon%')
About this query, this result to a conflict of schedules. What should i do with this?

If you book a room on Monday from 7:AM - 10:AM
This Query

SELECT Room FROM tblSched WHERE ((TimeStart >= # 8 :00 #) AND (TimeEnd <= # 9 :00 #)) AND (Day Like '%Mon%')

Will not find a conflict as 7:Am is not greater than or equal to 8:AM so it will not be found as a conflict for that same room

This post has been edited by CharlieMay: 20 February 2010 - 09:45 AM

[LiDoNg_9_0]

ok thanks you. i'l try it.

[LiDoNg_9_0]

i think thats the point.. i want to find the conflict schedule in the given time and day. So that i will not include the room number in the available rooms for that day and time range. is it ok?

[CharlieMay]

OK I guess off the top of my head without actually messing with it, I think you will have to check each with the two times entered.

So

lets say you have txtStartTime.Text with 7:00 AM and txtEndTime.Text with 9:00 AM for Room E101 on Monday (Mon)

In your database you have an entry as

Room:E101 TimeStart: 8AM TimeEnd 11AM Day:MONWEDFRI

to find that with the above times I would try the following:

"SELECT Room FROM tblSched WHERE TimeStart BETWEEN #" & txtStartTime.Text & "# AND #" & txtEndTime.Text & "# AND TimeEnd BETWEEN #" & txtStartTime.Text & "# AND #" & txtEndTime.Text & "# AND Day LIKE '*MON*'"

This way if the starttime or endtime has a value that falls within your current times you want to book, it will show that room as unavailable.

[LiDoNg_9_0]

is there any function in vb that counts the number of records? like in php the mysql_num_rows function.

[LiDoNg_9_0]

i did try this query you give

SELECT Room FROM tblSched WHERE TimeStart BETWEEN # 7:00 # AND # 10:00 # AND TimeEnd BETWEEN # 7:00 # AND # 10:00 # AND Day LIKE '*MON*'"

And the result to that is E101
But, when i try this one,

SELECT Room FROM tblSched WHERE TimeStart BETWEEN # 8:00 # AND # 9:00 # AND TimeEnd BETWEEN # 8:00 # AND # 9:00 # AND Day LIKE '*MON*'"

it result to nothing, this schedule is conflict with the first one. But why didn't it produce the room number of the class schedule 7:00 to 10:00 monday @ e101.

[CharlieMay]

Because the data for E101 that you gave above is booked from 7 to 10 neither of those 2 numbers fall between 8 and 9.

[LiDoNg_9_0]

so, what should i do? i try using >= and <= comparison, but it still doesn't work. is there any other way to solve my problem? i want to get the record that fall from that giving times. like 7:00 to 10:00, since 8:00 to 9:00 is fall to be part of the range between 7 to 10.
oh men, i think i'm having a headaches with this.

[CharlieMay]

You will probably need to pull in what is booked and loop through the records to see if it conflicts with what you're trying to book.

You might be able to OR if StartTime between TimeStart and TimeEnd to check if the allotted slot is withing a larger booking.

SELECT Room FROM tblSched WHERE (TimeStart BETWEEN # 8:00 # AND # 9:00 # AND TimeEnd BETWEEN # 8:00 # AND # 9:00 #) OR (TimeStart BETWEEN #8:00# AND #9:00# OR TimeEnd BETWEEN #8:00# AND #9:00#) AND Day LIKE '*MON*'

So this way you are checking for both times to conflict or the start time or the end time.

You might run this through the Query Builder in Access for proper () placement.