Solved

Problem in a filter with two dates using  Between condition

Posted on 2011-09-13
18
338 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
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:HainKurt
ID: 36532177
submit the code for sql and related area...
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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:HainKurt
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:HainKurt
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:HainKurt
HainKurt 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

773 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