Setting price per time period

[mikejs2000]

Hi,
I am writting a database that is to be used as an access system to a night
club.
All is ok accept that there is a requirement to have different entry fee at
different times of the night e.g.

19:00 to 21:00 = £4.00 - 21:00 to 23:00 = £6.00 - 21:00 to 03:00 = £10.00

Ok all goes well with comparing the present time with the periods above e.g.

If Presenttime < 21:00 then...

the problem comes when It gets to 23:59:59, ok explination, the last period
runs from the end of the first day to the begining of the second day, for the
less than statment I have provided two periods the first is 21:00 to 23:59:59
and the next is the next logical day 00:00 to 03:00. all works ok problem
when an action happens between 23:59:59 and 00:00 I get an error. Ok I could
possibly get around this by capturing the error but hey, I may be looking at
this completely the wrong way and someone out there has a much simpler
solution.

By the way the periods are held in a table as they need to be associated to
a specific day e.g. Monday period 1. 2 etc, the system first gets the
present day name then querys out the periods for that day and this is what is
used above.

Well, as usual thanks in advance for all your help, and sorry this is so long.

Mike J. Soames

 

[Al Camp]

Mike,
I want to take a shot at this one...
First, I think there's a problem with your pricing table, since Monday
(period 3) prices overlap into Tuesday.
Have you considered those transactions that occur after midnight Monday
should be treated as Tuesday's income.
That period 3 should be 21:00:01 to 23:59:59
Could you try this pricing...
Monday Period 1 00:00:01 to 03:00:00 = 10.00
Monday Period 2 19:00:00 to 21:00:00 = 4.00
Monday Period 3 21:00:00 to 23:59:59 = 6.00
Tuesday Period 1 00:00:00 to 03:00:00 = 10.00
Tuesday Period 2 19:00:00 to 21:00:00 = 4.00 etc.. etc...

Even if you added a SaleDate field to help you with your "after midnight"
sales, reassembling the data by "Monday" would. I think, be a bit awkward...
if not difficult.
This method would be a snap to set up reports on Monday's sales, last
week's sales, month... etc...