Price calculations between dates

[Elephant]

I am trying to calculate the price of a hotel stay, for example, between 01 April and 06 April, when the price of the hotel room is 50 USD on 01 and 02 April, but 75 USD on 03, 04, and 05 April.

I want to go to a Form, input the dates of stay (eg, 01-06 April), the hotel name, and then have the total price display automatically. The calculation therefore needs to take into account the different prices for the different dates of stay ...


Any ideas where to start?!

 

[shay]

Hi

I understand what you want to do but how is the pricing info store?

shay

 

[Elephant]

Hello Shay,

Currently I have a Products table, which lists the various products their prices, and the dates when prices are valid. This table is linked to the Suppliers table, which hold the hotels.

So, I have a seperate ProductID for each seperate room type and date range : eg, Hotel Sunshine Low season Twin room has a price and a Product ID code; Hotel Sunshine High season Twin room has a price and a product ID code.

Usually each hotel has 2 price ranges : High season and Low season, each of which is a range of dates (eg, January-March Mondays-Thursdays, etc.)

Thanks
Steve

 

[shay]

Steve

Can you explain a bit more about how the seasons/dates info is stored? Given any date, how do you decide if it's High or Low season? Attach your file if that's easier.

shay

 

[Elephant]

Hello again Shay,

Currently I have a Form, which gives the general details about a Booking (ie, the customer name, OrderID etc. This "Orders" form then has a sub-form called "Order Details" which lists each part of the booking (eg, hotel name, date of arrival, type of room etc). At the moment I can choose the hotel name + its room type [by "room type" I have called it eg "Hotel Sunshine Low Season Double", and this brings up the price. THEN I put in the dates of arrival and departure, and it calculates the total amount.

However, during any given date period (eg 5th April 4 nights) there can be 2 different hotel prices and my current set-up does not account for that.

What I want, ideally, is to input the hotel name and dates of arrival and the total price comes automatically from there.

My file has some confidential details on it - but if it helps you, I can take out some of those and attach it.

Thanks for your help
Steve

 

[shay]

Steve

Having the software calc the total price from the Hotel name, room type and dates is 'doable' but what I haven't fully understood is the structure of the table which stores dates and prices.

Do you have

HotelId, SeasonStartDate, SeasonEndDate, RoomType and Price

for example?

shay

 

[Elephant]

Currently I have the rates stored in a Products table:

Product ID, Product Name (ie, Double High season), Supplier (ie, Hotel name), Price

I then manually input the dates of arrival and departure and a calculation multiplies number of nights by price to give total.

The dates themselves (other than the actual number of nights of the stay) are not involved directly in the calculation. I want the database to automatically work out the price after I input the dates --- ie, the price will now be somehow linked to the arrival and departure dates, as opposed to the Product Name as it is now ...

 

[shay]

Steve

I think you will have to redesign your product table so that it stores the room type and season start & end dates separately rather than have a product description field.

I've created and attached an example mdb for you which gives a possible solution. The code behind the form looks up the price for each day required and displays the total cost.

I had to spend a bit of time sorting out the usual date problem (ie mm/dd/yy or dd/mm/yy) but if you are not UK based you can probably strip out the calls to the Format function.

hth

shay

 

[Elephant]

Hi Shay,

BIG thank you for that ! It looks like you have it just the way I meant .... only thing is that when I tested it, on pressing the "Cost" button I got an error message :

"Run-time error '94'
Invalid Use of Null"

Is this something in the file or my computer or ...?

 

[shay]

Steve

Not sure why it's not working on your pc. Try putting a breakpoint in cmdCost_click so that you can see where it's falling over.

shay

 

[Elephant]

Not sure what you mean by "breakppoint" (!!must be because I'm in Finland!!) .... When I got the error I pressed "Debug" and got this Visual Basic code - yellow arrow on the left by "Total Cost ..."


Private Sub cmdCost_Click()

Dim TotalCost As Double
Dim ThisDate As String, EndDate As String, i As Integer

ThisDate = "#" & Format(Me.txtFromDate, "mm/dd/yy") & "#"
EndDate = "#" & Format(Me.txtToDate, "mm/dd/yy") & "#"

i = 0
Do While ThisDate <= EndDate
TotalCost = TotalCost + DaysPrice(ThisDate)
i = i + 1
ThisDate = "#" & Format(DateAdd("d", i, Me.txtFromDate), "mm/dd/yy") & "#"
Loop

Me.txtTotalCost = TotalCost

End Sub

 

[Elephant]

Hi again Shay,

If for some reason I can't get the "cost" button working, maybe you could tell me how you made that button ...

Kiitos paljon!
Steve

 

[shay]

Steve

To insert a breakpoint, click in the left-hand margin on the line which begins "ThisDate = ..." The line should then be highlight in brown (well it's brown on my pc!)

Next select press F5, enter your data and click the Cost button. The code will stop at the breakpoint. Press F8 to step through the code a line at a time. You can also scroll over the variables and it will display the current value (this is very useful).

If you think the problem is with the button itself, delete the current one then create another. Call it cmdCost and set the click event to [Event Procedure].

shay

 

[Elephant]

Hi Shay,

I followed your instructions and on pressing F8 the point where it gives the error message is at "End Function" Ln 35, Col1 ...

Hope that helps ...

I'm sure you have made exactly what I need and it will be desperately appreciated!

 

[shay]

Steve

Not sure what else to suggest. I presume you've checked that it's calculating DaysPrice correctly.

I am running Access 97 (SR2) on W95. Not sure if another version and/or windows platform would make a difference.

Sorry but I am about to leave the office and I'm not back til Friday. Hopefully someone else will have a suggestion to help you.

shay

 

[Elephant]

Hello Shay,

Thanks for all your help on Wednesday. I didn't get any replies from anyone else so ... I was wondering if perhaps I could have go at making the control button myself, if you could advise me? I have very limited knowledge of VB .... Or, is there a way to get the same result by using a new cell with a formula/calculation ... or is the requirement to complex for that?

I tried your zip file out on different computers using windows 98 and XP, but both times got error (different!) message when pressing the "cost" botton ...

Thanks
Steve

 

[shay]

Morning Steve

To create a new command button, open the form in Design view and make sure you can see the Toolbox (select Toolbox from the View menu).

Scroll along to the command button and click it. Next, move across to the form and draw a button. Delete the old button and name the new one cmdCost. Click the buttons properties Event tab, and select [Event Procedure] for the On Click event.

The new button will run the old code when clicked.

Another thought, try repairing the db (Tools, Database Utilities, Repair Database) to see if that helps.

shay

 

[Elephant]

Morning Shay,

I tried a new button and a database repair - same errors come up.

Further to something you mentioned Wednesday : "I presume you've checked that it's calculating DaysPrice correctly. " --- not sure actually how to do this - I don't see any calculations on the form itself so assume its in the code - how do I check it?

I was trying to figure out a way to do the calculation on the form with formulas, rather than the command button - I was thinking that maybe somehow there was a calculation that would work out from the dates how many days were the High Season price, and how many were the Low season price, and then a calcualtion to add the 2 prices together ....

Thanks again
Steve

 

[shay]

Steve

Just reread the thread - I'd lost sight of the problem! Your problem is that some variable does not contain valid data.

Insert the line

TotalCost = 0

after the line

i = 0.

Insert a breakpoint on the line

ThisDate = ...

and run the code again. On each line position the cursor over each variable name and its value will appear. This should help you to track down the problem.

shay

 

[Elephant]

Hi Shay,

Added the extra line of code, but got same result when testing the form ...

I'm not really sure what to look for when putting the cursor over the variables ...

I can't figure out, either, why it would the form would work fine on your computer but not on mine ...

Not sure what to do next ...