Solved

Problem in a filter with two dates using  Between condition

Posted on 2011-09-13
18
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 4
  • +1
18 Comments
 
LVL 4

Expert Comment

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

Expert Comment

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

Expert Comment

by:Huseyin KAHRAMAN
ID: 36532177
submit the code for sql and related area...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:TCCIRM
ID: 36535643
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
ID: 36535984
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:Huseyin KAHRAMAN
ID: 36536427
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
ID: 36536860
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:Huseyin KAHRAMAN
ID: 36537358
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
ID: 36538669
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 36538778
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
ID: 36538957
There are records for each day a room is booked.

0
 
LVL 51

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 250 total points
ID: 36538976
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)
ID: 36539058
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
ID: 36542049
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
ID: 36542928
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
ID: 36544106
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
ID: 36565511
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)
ID: 36546447
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 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