• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Problem in a filter with two dates using Between condition

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
TCCIRM
Asked:
TCCIRM
  • 9
  • 4
  • 4
  • +1
3 Solutions
 
raremindCommented:
Are they date fields? I cant under stand why you are using cdate?
0
 
Dale FyeCommented:
What country are you in and what are your Windows regional settings for date?
0
 
HainKurtSr. System AnalystCommented:
submit the code for sql and related area...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
TCCIRMAuthor Commented:
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
 
TCCIRMAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
TCCIRMAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
TCCIRMAuthor Commented:
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
 
Dale FyeCommented:
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
 
TCCIRMAuthor Commented:
There are records for each day a room is booked.

0
 
HainKurtSr. System AnalystCommented:
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
 
Dale FyeCommented:
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
 
TCCIRMAuthor Commented:
I will try the queries without calling functions.


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


0
 
TCCIRMAuthor Commented:
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
 
TCCIRMAuthor Commented:
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
 
TCCIRMAuthor Commented:
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
 
Dale FyeCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now