Solved

Problem in a filter with two dates using  Between condition

Posted on 2011-09-13
18
335 Views
Last Modified: 2012-05-12
Hello,

I am encountering a similar situation with filtering a table using the Between condition on two dates.

This is a hotel/trip management application with Access frontend and backend. Reservations are made in advance. By selecting the reservation, a list of available rooms are to be shown for the time period of the reservation. This was done by querying a table of the BookedRooms.
The query was working fine, suddenly the available rooms wouldn't display for certain dates and ranges. (for example 9/12/2011 and 12/2/2011).  At first we had a suspicion it was the Fiscal Year causing a problem. When the date range was across the fiscal year (Oct. 1st), the problem occured. However, the Select query worked with the dates in that range hardcoded into VB. Passing along the dates properly from the form to the query seems to be the problem. I attempted to format the date in the VB functions before passing them to the query.

Public Function GetStartDate() As Date
GetStartDate=CDate(Format(ArriveDate, "yyyy-mm-dd"))
End Function

Public Function GetEndDate() As Date
GetEndDate=CDate(Format(DepartDate, "yyyy-mm-dd"))
End Function

Open in new window


I checked the result of the cdate (msgbox) and it still remained in the 9/12/2011 format.


Viewing the BookedRooms table on the backend had a strange quirk also. Opening the table in datasheet view is ok.
When clicking on the Date column to filter it, things got interesting.  Selecting the Date filter Between  for the range 9/13/2011 to 10/12/2011 gives an error message. Enter a valid date!
9/13/2011 to 10/8/2011 works fine and then changing the range works.

The bad ranges work after the initial range is successful.  Clearing the filter and entering a bad range first will get the error message.

So I don't know what is going on with this Access app and database.
I hope somebody has an idea of where to proceed next.
My thanks in advance


0
Comment
Question by:TCCIRM
  • 9
  • 4
  • 4
  • +1
18 Comments
 
LVL 4

Expert Comment

by:raremind
Comment Utility
Are they date fields? I cant under stand why you are using cdate?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
What country are you in and what are your Windows regional settings for date?
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
submit the code for sql and related area...
0
 

Author Comment

by:TCCIRM
Comment Utility
Hello,
Thanks for the quick response. Please excuse the delay I forgot the logon info and had to find it again.

I am in the United States and my regional settings are set to
Short Date: 9/14/2011.
Long Date: September 14, 2011

The fields are Short Dates (Date/Time)

I'll dig up the sql and post in a bit.

0
 

Author Comment

by:TCCIRM
Comment Utility
here is the query:
SELECT BookedRooms.build, BookedRooms.room, BookedRooms.Date, BookedRooms.reservationno, BookedRooms.guestno
FROM BookedRooms
WHERE (( (BookedRooms.Date) >= GetStartDate() And 
(BookedRooms.Date)  < GetEndDate() ));

Open in new window

Just remembered this is the base query for a left join see below:
SELECT Rooms.build, Rooms.room, Rooms.category
FROM Rooms LEFT JOIN qryBookedRooms ON (Rooms.room = qryBookedRooms.room) AND (Rooms.build = qryBookedRooms.build)
WHERE (((Rooms.category)=GetVIPstatus()) AND ((qryBookedRooms.build) Is Null) AND ((qryBookedRooms.room) Is Null) AND ((Rooms.status)<>"Out Of Service"))
ORDER BY Rooms.build, Rooms.room;

Open in new window

note: GetVIPstatus is for the group category VIP's only stay in certain rooms.

0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
so GetStartDate & GetEndDate are access functions
where are these variables are coming from: ArriveDate & DepartDate
why do you format them first then convert to date? what kind of values are you storing in these variables? what are their types? hoiw do you define and how do you set them?
0
 

Author Comment

by:TCCIRM
Comment Utility
the ArriveDate and DepartDate are global variables whose values come from the referring form for the reservation.
Selecting the reservation on the form populates these variables and pops up a new window that is supposed to list the available rooms for assignment (this is the problem area).

on the cdate i had tried to return them as strings at one point hardcoding the values "#9/12/2011#"
i think that was leftover from those attempts. removed the cdate and still no joy.

I've gotten more info from a user. the erro message 3159 Not a valid bookmark.
I get this same error when trying execute the left join query too.
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
are  ArriveDate and DepartDate defined as date initially? if yes use those ones in your query or just return those variables without doing anything

Public Function GetStartDate() As Date
  GetStartDate=ArriveDate
End Function

Public Function GetEndDate() As Date
  GetEndDate=DepartDate
End Function

also instead of returning global variables, what happens if you return Today and Today +- 5 for example

Public Function GetStartDate() As Date
  GetStartDate=Today()
End Function

Public Function GetEndDate() As Date
  GetEndDate=Today()+5
End Function

does it work?
0
 

Author Comment

by:TCCIRM
Comment Utility
the ArriveDate and DepartDate are defined as untyped (variant?)

I tried returning Now (Today wasn't recognized)
Public Function GetStartDate() as Date
GetStartDate=Now
End Function

Public Function GetEndDate() as Date
GetEndDate=Now + 5
End Function

That retured records for the small qryBookedRooms and the left join query doesn't give the error any more (not a valid bookmark), but it doesn't return any records either.  There are available rooms, but this query makes it look like there are NOT any rooms available.

I tried a couple more times with different dates (adding to and subtracting from now) and it started giving the error again.  

Could it be that Access is thinking too hard?
The frontend is 45mb and the backend is 75mb.
I was told my predecessor did some process to make it smaller.  (archive?)

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
Comment Utility
What is the data structure of your table BookedRooms?

If a room is booked for multiple days, do you have a single record for that booking, or one for each day?

0
 

Author Comment

by:TCCIRM
Comment Utility
There are records for each day a room is booked.

0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 250 total points
Comment Utility
what happens if you use now() in your quet without calling functions? will you get the same errors?

SELECT BookedRooms.build, BookedRooms.room, BookedRooms.Date, BookedRooms.reservationno, BookedRooms.guestno
FROM BookedRooms
WHERE (BookedRooms.Date >= Now) And (BookedRooms.Date  < Now+n);

where n is a positive integer...
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
and what you want to do is to identify rooms that are available for the entire period between the startdate and the enddate, is that correct?
0
 

Author Comment

by:TCCIRM
Comment Utility
I will try the queries without calling functions.


We want to identify rooms that are available for the period between startdate and enddate.


0
 

Author Comment

by:TCCIRM
Comment Utility
The queries work without calling functions.
I used Now and Now+n for StartDate and EndDate in qryBookedRoom
I used a hardcoded value for the GetVIPstatus for the left join query.
so what options do I have?
0
 

Assisted Solution

by:TCCIRM
TCCIRM earned 0 total points
Comment Utility
when I used the dates 9/12/2011 to 12/2/2011
I got the error Not a Valid Bookmark.

so I figured out (thanks to you guys) it's not the query and it had to be the dates.

Found a corrupt record in the table BookedRooms. When sorted by date tt falls on 10/11/2011.
All I see of course is #Error in all the fields.
Cannot delete it keep getting Not a valid bookmark.
How do I go about fixing this???

Should I post this as a different question?

Thanks
0
 

Author Closing Comment

by:TCCIRM
Comment Utility
The solutions were only partial because the led me to discover there was nothing wrong with the code, there was corrupt data. The experts recommendations allowed me to breakdown the code and then build it back up.  This let me see that only certain sets of data were a problem and I could narrow it down.  The fact that each booked room had a separate record for each date booked got me thinking about the the data.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
The other issue with such a wide span of dates 12 Sept - 2 Dec is that all your rooms would most likely have at least one booking during that period, and would therefore return no rooms available for that period.

This room booking problem is a challenge if you actually book specific rooms in advance, because you will necessarily have some large gaps, and some not so large gaps, especially if you try to use each room evenly.  One method to deal with this is to occassionally, run an algorithym which will "shuffle"  the room assignments to minimize the unused space.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now