Find unmatched query and looping

[adambedford]

Im designing a database booking system for properties.

I have a find unmatched query that works with a form and allows you to enter a date and return results that do not have a booking entry for that day.

However, how do I get it to take duration of rental into account? i.e. return properties that are not booked for that day AND that are available for the number of weeks specified.

There is a "booking start date" and a "duration" (rather than a start and end date).

Also, how would I get the query to take other criteria into account, such as number of bedrooms and location.

Any help would be greatly appreciated, I've been trying to do this for quite a while!!

Thanks,

Adam

 

[pbaldy]

Personally I would probably store end date instead of duration. Either way, I think you need the end date, so you can calculate it in a query if you stay with your current design. Then you compare your test start date against the end date in the table/query, and your test end date against the start date in the table/query. That gets you any overlapping records.

 

[adambedford]

Hi,

Thanks for that.

If i had a start date and an end date, is there any way of validating the dates entered in the end date?

Properties are only rented on a weekly basis, starting from saturday, so thats why I thought of a duration field rather than end date.

If I do have to stick with a "duration", could the query be done? or is it really not worth it?

Thanks

 

[pbaldy]

I was saying that I'd probably store the end date. I might still have the user enter a duration on a form, from which I would calculate the end date. You can also test the end date entered to make sure it's a Saturday (or whatever). If you stick with duration, the query can still be done, it just has to be based on a query that calculates the end date instead of directly on the table. There are probably other ways of doing it as well, I'm just most familiar with having start/end dates, since that's what I've done.

 

[jardiamj]

Hello Adambedford! I'm sorry that English is not my first language and I'm finding hard to understand what you are trying to do. I might be able to help you with the query that you are trying to build if I could understand it...lol. If you can post one example that shows what you are trying to do, I will try to figure it out.
In despite the fact that I'm not a programmer I have done good stuff with Ms Access and Visual Basic.. lol.
I will check out if you post the example. Cheers!

 

[adambedford]

Hi jardiamj!

View attachment MyDatabase.zip

I have attached the database I am trying to get working.
So far I have got a Find Unmatched query to return record not "Booked" on the day, but dont know how to get the system to take into account the length of the booking.

I also need to have other criteria such as "Location" and "Bedrooms" and I'm not quite sure how to achieve this.

The form that works is "frmSearchDates", and the form that contains all the criteria including length of booking is "frmSearchProperty".

Many thanks.

 

[jardiamj]

Hello adambedford!
Take a look at the button I added to the form frmSearchProperty. I created I new query that selects all the booking that have been done and calculate the final date for every booking by adding the number of weeks to every booking date, I called it allBookings.
Then I build a Dynamical query called qryDynamicalBooking, not such a good name hu!!. you can change it if you want.
So, I build this query by using VBA. To build this query I create first a subquery that Select all the places wich booking date are between the booking date and final date of the search, and the final date is also between the booking date and the final date.
Then in qryDynamicalBooking I include all the villages that are not include in the subquery I said before, you also can add more criteria to that query like beds, Region, Town, pool, etc...
One thing that gave my problems is that you have the field v_Beds as a text when it's really a number. So I think you should change it. To make my query to work I had to put the value between quotation marks ('') when it should be and integer. Any way I don't know why you declared it as a text.
I build the query allowing you to leave the Region, town and bedrooms blank.
To test it just enter the values Date From, weeks, Region, Town, bedrooms in the form frmSearchProperty and click Search.
If you have any question about it just ask, I'm always willing to help.

Atte;
J.A.M.J.

 

[adambedford]

Hi jardiamj!

Thank you excellent example!

I have one question about the query qryDynamicalBooking:

SELECT *
FROM tblVillas
WHERE (((tblVillas.[v_Name]) Not In (SELECT v_Name FROM allBookings WHERE ((b_Date BETWEEN #02/04/2009# AND #09/04/2009#) OR b_FinalDate BETWEEN #02/04/2009# AND #09/04/2009#))));

Why are there static dates in there? For example, #02/04/2009#? Should it not look for the information from the field in the form?

Thanks.

Adam!

 

[jardiamj]

Look at the code in the button a created in the form called search, the values are being passed via VBA to the query. If you change the values Date From and Weeks in the frmSearchProperty the values in the Qry will change because it's being building by code.
That's the way I like to do this things because it gives you more options to manipulate the Query.
If you see the code in the button I created, there is a variable called strWhere and it's passing the criteria to the query; so can build it as you want and allow to leave text boxes blank if you don't want to specify this criteria.
If you want I can post the code here, comented out for you to have a better idea.
I hope it will help. Cheers!

 

[adambedford]

Thank you,

I had just realised what the code does and was about to reply!

One other question...If I want to add more criteria, do I just do it in the VBA code or is there some SQL I need to change aswell?
Cheers

 

[jardiamj]

I just finished commenting the code out, I'm posting it here so it would be useful for someone else. The answer for your question will be at the end:

'---Variable declarations --- some variables have been leave without a type so they are variant as default
'---because by being variant thay can accept a NULL value if you leave the TextBox empty, other wise
'---you will have to trap the error if you leave any box empty.
Dim InitialDate As Date, Weeks As Integer, FinalDate As Date
Dim BedroomsNo, PoolType, selRegion, selTown
Dim strSubSQL As String, qryName As String, strWhere As String
Dim strSQL As String, qdf As DAO.QueryDef

qryName = "qryDynamicalBooking" '--> Name of the query that is build Dynamically

'---Passing the values from the Form to the variable I have declared-------------------------
InicialDate = DateFrom.Value
Weeks = Duration.Value
FinalDate = DateAdd("ww", Weeks, InicialDate) '---This date is calculated adding the weeks to the InicialDate
BedroomsNo = Bedrooms.Value
PoolType = Pool.Value
selRegion = Region.Value
selTown = Town.Value

'---Build the subQuery that show the Booking places--- Note that InicialDate and FinalDate are
'---from the values typed in the frmSearchProperty From (Date and Weeks)
strSubSQL = "SELECT v_Name " & _
        "FROM allBookings " & _
        "WHERE ((b_Date BETWEEN #" & InicialDate & "# AND #" & FinalDate & "#) " & _
        "OR b_FinalDate BETWEEN #" & InicialDate & "# AND #" & FinalDate & "#)"

'----strWhere is the Criteria for the Qry we want, and at first it takes all the Villages that haven't
'----been booked
strWhere = "WHERE v_Name not in(" & strSubSQL & ")"
'----if you select a Region it will add the criteria to the WHERE clause
If selRegion <> Empty Then
strWhere = strWhere & " AND v_Region = '" & selRegion & "'"
End If
'---if you select a Town if will add the criteria to the WHERE clause
If selTown <> Empty Then
strWhere = strWhere & " AND v_Town = '" & selTown & "'"
End If
'---if you select a number of beds it will add the criterio to the WHERE clause
If BedroomsNo <> Empty Then
strWhere = strWhere & " AND v_Beds = '" & BedroomsNo & "'"
End If

'---here we build the Main Qry and it takes the srtWhere clause (Criteria) we have built in the process
strSQL = "SELECT * " & _
        "FROM tblVillas " & _
        strWhere

'---Set the qry and run it----------
Set qdf = CurrentDb.QueryDefs(qryName)
qdf.SQL = strSQL
DoCmd.OpenQuery (qryName)

'---Empty the Object we have created----------
Set qdf = Nothing

Now about your question. You just have to add your criteria to the strWhere variable like I'm doing with Regions, Town and Bedrooms. Notice that I first trap if the variables have been set, and if so I add the criteria to the srtWhere variable that will be pass to the Qry, so if you leave the TextBox empty it will not pass the criteria. I'm assuming that you always will have a Date From and the Duration so I'm not trapping those values; but if you want to do so, it can also be done. Look at this part of the code for that:

'----strWhere is the Criteria for the Qry we want, and at first it takes all the Villages that haven't
'----been booked
strWhere = "WHERE v_Name not in(" & strSubSQL & ")"
'----if you select a Region it will add the criteria to the WHERE clause
If selRegion <> Empty Then
strWhere = strWhere & " AND v_Region = '" & selRegion & "'"
End If
'---if you select a Town if will add the criteria to the WHERE clause
If selTown <> Empty Then
strWhere = strWhere & " AND v_Town = '" & selTown & "'"
End If
'---if you select a number of beds it will add the criterio to the WHERE clause
If BedroomsNo <> Empty Then
strWhere = strWhere & " AND v_Beds = '" & BedroomsNo & "'"
End If

 

[adambedford]

Hi,

I have one last question...Is there a way to get the prices to work aswell?

View attachment MyDatabase.zip

I have attached a new version of the database which includes tblPrices.

There are three price ranges, depending on the time of year:
Low price is October to February
Mid price is March to May
High price is June to September.

On frmSearchProperty there are field for minimum and maximum prices and I wondered whether it would be possible to calculate which price range to work from depending on the time of year? And then return properties priced within the minimum and maximum price.

Sorry if I'm not explaining myself very well.

Thanks!

 

[jardiamj]

Ok Adam!, so I think that other code worked just fine. And, what I have learned so far is that everything is possible my friend, even if the so called experts say some times its not... lol.
Just let me take a look at it. I may post it later because I have to get some sleep tonight; I have had so much work this week.
Keep trying any ideas you have. I will post back after look at you db.
Cheers!

 

[jardiamj]

Hello again Adam!
I think I got exactly what you need in the attached db. I have changed some things:
- I realize this morning that the criteria to get the bookings between the dates should be like this:
WHERE ((b_Date <= #7/14/2009#) AND b_FinalDate >= #6/30/2009#))

-I also changed the name of a textBox I was not taking in consideration before which I think is to specify the name of a Village. You named it "Name" and Name in access is a object property so you should avoid use this sort of names for you objects. I changed the name to PropertyName.

-In the SELECT clause for the Dynamic Query that is being build by VBA code I added a INNER JOIN to tblPrices. So if the VillaName is not in this table it will not appear in your results.

-And this is the code to get the column to use for price depending on the month of the year:

intMonth = DatePart("m", InitialDate)

'------------Evaluate wich column use for Price criteria------------------
Select Case intMonth    ' Evaluate Month.
Case 3 To 5             ' if the Month is between March and May
    priceColumn = "tblPrices.p_MidPrice"
Case 6 To 9             ' if the Month is between June and September
    priceColumn = "tblPrices.p_HighPrice"
Case Else               ' if not the two before the month should be between October and February
    priceColumn = "tblPrices.p_LowPrice"
End Select

'-----------Evaluate if you have specify Prices and pass the value to the query----------------
If valPriceMin <> Empty And valPriceMax <> Empty Then
    strWhere = strWhere & " AND (" & priceColumn & " BETWEEN " & valPriceMin & " AND " & valPriceMax & ")"
Else
    If valPriceMin <> Empty Then
        strWhere = strWhere & " AND " & priceColumn & " >= " & valPriceMin
    End If
    
    If valPriveMax <> Empty Then
        strWhere = strWhere & " AND " & priceColumn & " <= " & valPriceMax
    End If
End If

Finally just some advices:
-As I said in one of the other posts you should change the Data Type for the fields v_Beds, v_Sleeps and v_Baths in tblVillas from Text to integer because what they have stored are integers.
-I wouldn't use the v_Name in tblVillas as my primary key, because the names can be quite long, if it's a primary key they need to be unique and if some one for some reason decides to change the name of the Villa in the db that will be all screwed up and will give you a real headache.
-I can not think in something else I saw in you db right now....lol..

As a conclusion:
-By this example I hope you have learned how easy is to build queries Dynamically by using VBA, and the flexibility that it gives you.

Have a nice day;
Cheers!

 

[adambedford]

Hi jardiamj!

Thank you so much for your help! I really appreciate it. Everything is working perfectly now

I have now changed the bedrooms, sleeps and baths field to be integers...I dont know why I had them as text!

I will also add an ID field to tblVillas so I dont have any problems.

But once again, thank you for your help, I don't know what I would have done without it!!

Best wishes,
Adam

 

[jardiamj]

Hello Adam!
You are very welcome. I'm glad I could help you.
You know that if you have changed the v_Beds field to be Integer you can remove the quotation marks around BedroomsNo from this part of the code:

strWhere = strWhere & " AND tblVillas.v_Beds = '" & BedroomsNo & "'"

So it becomes:

strWhere = strWhere & " AND tblVillas.v_Beds = " & BedroomsNo

And yeah!, add and ID to tblVillas and change the WHERE and INNER JOIN statements to work with the ID instead of the v_Name.

For any other question, I'm always willing to help if I have time.
Have a good day. Cheers!

 

[pbaldy]

Glad you got a solution. You already caught the problem I was going to point out with the date criteria, but I'll also note that generally speaking, joins are more efficient than subqueries. In this case, a "frustrated outer join" would probably be more efficient than the subquery. You may not notice the difference until/unless you build up a lot of data, so you may just want to file that away for if/when performance becomes an issue.

 

[jardiamj]

Hello Pbaldy!
I don't know so much about performance efficiency. And as always there is more than one solution to the same problem, I know how to build the query as you said, it just seemed easier for me to use a subquery.
I will see where I can find information to read about efficiency.
Thanks for pointing this out.
Cheers!