[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Problem in a filter with two dates using  Between condition

Posted on 2011-09-13
18
Medium Priority
?
344 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 48

Expert Comment

by:Dale Fye
ID: 36532091
What country are you in and what are your Windows regional settings for date?
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 36532177
submit the code for sql and related area...
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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 60

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 60

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 48

Accepted Solution

by:
Dale Fye earned 1000 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 60

Assisted Solution

by:HainKurt
HainKurt earned 1000 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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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 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 …

656 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