Price calculations between dates

[shay]

Steve

We really need to check the content of the variables. If holding the cursor over variable names while the code is running doesn't display the values, you'll have to use another method. You could use "Debug and Add Watch" but it's probably simpler to insert some Msgbox statements.

Change Function DaysPrice:


MySQL = "([RoomType etc
Msgbox MySQL

DaysPrice = Dlookup etc
Msgbox DaysPrice


I assume you've not changed any table or field names?

shay

 

[Elephant]

Hopefully I have all the possible variables here :

Where TotalCost is always TotalCost=0
Over the ThisDate and EndDate it shows correct dates
over "i" shows i=0
Nothing shows over "DaysPrice" - should it?
Me.txtTotalCost it shows Me.txtTotalCost=Null

Does that help?

 

[shay]

Is it going round the Do While loop? Does it ever call Function DaysPrice?

 

[Elephant]

I've no idea!

How can I find out ? - sorry, I have zero knowledge of this code ...

 

[shay]

Steve

If you step through the code using the F8 key, you will see if it ever gets to the line

TotalCost = TotalCost + DaysPrice(ThisDate).

If it never gets there, then the code isn't calling Function DaysPrice and this would explain why me.txtTotalCost is null.

Shay

 

[Elephant]

I tried the f8 and same happened as previous.

ie, When I got the error I pressed "Debug" and got this Visual Basic code - yellow arrow on the left by "Total Cost ..."

... and on pressing f8 it moves through the code highlighting parts in yellow until on pressing F8 at "End Function" Ln 35, Col1 ... then the error message appears.

I'm so sorry I'm so dumb about this ....

 

[shay]

Steve

We must be nearly there!

Let's try again. You need to put a breakpoint in the code so that execution halts BEFORE the error occurs. So, click in the left-hand margin on the line

ThisDate = "#" ...

Go back to the form. Enter your data and click the button. The code window should open at the breakpoint. Press F8 to step through the code.

Does it ever get to the line

TotalCost = TotalCost + DaysPrice(ThisDate).

If yes, is TotalCost increasing every time the loop is executed? If not, what dates did you enter?

shay

 

[Elephant]

Thanks for your patience!

It does get to TotalCost = TotalCost + DaysPrice(ThisDate), yes, but the TotalCost is always 0.

I put in variuous dates such as March, June and September 2003.

 

[shay]

Steve

After the

TotalCost = TotalCost + DaysPrice(ThisDate)

line it should jump to Function DaysPrice. Is it working out a sensible value for DaysPrice? (Again hold cursor over the variable name or use a Msgbox command.)

shay

 

[Elephant]

It does jump there as you.

DaysPrice = Empty

 

[shay]

OK! Can you give me the content of MySQL - one example will do.

 

[Elephant]

You mean like this ?

---

Public Function DaysPrice(ThisDate As String)

Dim MySQL As String

'This functions looks up the price for this hotel, room type and date.

MySQL = "([RoomType] = '" & Me.cmbRoomType & "') and " & _
"([HotelID] = " & Me.cmbHotelName & ") and " & _
"(" & ThisDate & " Between [SeasonStartDate] And [SeasonEndDate])"

DaysPrice = DLookup("[Price]", "HotelPrices tbl", MySQL)

 

[shay]

Hi Steve

I need to know the VALUE of MySQL.

Put in the line

MsgBox MySQL

after

MySQL = "([RoomType = ...

and let me know what you get.

The end must be in sight .....

shay

 

[Elephant]

MySQL = "([RoomType]='Double') and ([HotelID]=1) and (#09.23.03# Between [SeasonStartDate] And [SeasonEndDate])

This what you needed?

 

[shay]

That looks ok to me. The only difference between that and what I get here is that on my pc ThisDate is displayed as #09/23/03#.

Let's try to work out if the problem really is with the date. Change MySQL to

MySQL = "([RoomType] = '" & Me.cmbRoomType & "') and " & _
"([HotelID] = " & Me.cmbHotelName & ") "

and see if you still get the error.

shay

 

[Elephant]

Now I don't get the error! I get a value in the total cost box!!

Initial look though, the value isn't correct when a stay is over 2 price bands ... do you still need to adjust the date code or ...?

THANKS for your patience and effort !!!

 

[shay]

Steve

The total won't be correct because we've just removed the bit of code which tells the function which seasonal cost to choose.

What we need to do now is try to work out why we get an error with the date bit of MySQL.

Open table HotelPrices tbl in design mode. Click on the SeasonStartDate row, and at the bottom of the screen change the Format from 'Medium Date' to 'd\.m\.yyyy' (which I think is the Finnish default date pattern).

Do the same for SeasonEndDate. Save the table then view it in Datasheet view. The dates should appear as '1.10.2002' and '31.5.2003', etc.

Put the MySQL line back to what it was

MySQL = "([RoomType] = '" & Me.cmbRoomType & "') and " & _
"([HotelID] = " & Me.cmbHotelName & ") and " & _
"(" & ThisDate & " Between [SeasonStartDate] And [SeasonEndDate])"

and try again.

shay

ps Leaving the office in about 15 minutes!

 

[shay]

Steve

Think I may know what the problem is. In the last bit of the SQL statement I am comparing ThisDate (which is a string) with SeasonStartDate (a Date). I've got a different version of Access at home so I'll try it there and get back to you on Monday.

Have a good weekend

shay

 

[shay]

Morning Steve

I'm afraid I forgot to try to code on my PC at home. I've had another look and can't work out why you get error messages when it works perfectly for me.

I suggest you create another thread, include the code and ask for comments. This thread's been going on for so long that I doubt anyone else will want to join in.

I'm convinced the problem is to do with differing date formats. In the UK we usually use dd/mm/yy but in Access we have to reformat dates to mm/dd/yy to get the date functions to work.

I hope someone comes up with a solution as this is really a simple piece of code.

All the best

shay

 

[Elephant]

Thanks Shay for all your help on this ...

Will start a new thread and see what comes back ... I am not sure if to start with my original question, or the technical issue re the dates ...

Am I right that this whole thing can't be done using a formula rather than code?

Steve