UDF to count days and convert them to month/week/day.

[MattRGM]

Recently, my parents have bought a campground and I agreed to attempt to build a database which they can useto keep track of openings, reservations and I want to be able to now calculate the price for the customer. I know I can easily create an "invoice" using the forms.
Originally, I thought this would be easy and it actually wasn't too difficult, I had everything set up to go. Then I hit a snag. The prices are by days, weeks, months and season and get slightly cheaper for each tier you go up. Charging for a week is cheaper than charging the daily rate seven times, makes sense right? it makes it difficult for me because, I'll use the example of nine days, for a stay like this you would charge for one week and two days. I can't just convert the days stayed into a number like 1.3 weeks and multiply it by the weekly rate because it would be undercharging the customer by about $30 and this number would go up as the stay got longer. I know I could do something like an "IIf" statement but in order to cover all the variables, that would be a long equation with lots of spots where a mistake could be made.



Through doing research, and asking here, I figured that it would be best to develop a UDF which would take the number of days stayed and convert it to something which would count them and turn it into a number like "1 week, 2 days". From there I will have to figure out how to make it so that the program knows which sections to multiply by which rates to build the price. I have taken a course in Access and have a huge book relating to the subject but I'm not quite sure where to begin.

I can easily go through and make it so that these are numbers which the user enters themselves and will have no problem doing that if I need to. I just wanted to see if anyone had advice on how to go about making that idea work first, to cut out some manual steps if necessary.

[orange]

Can you please show us your tables and relationships (do a screen capture of your relationships window) as a jpg? I recommend you identify and verify your proposed business rules. These are critical to designing your database.

Here's a free data model regarding hotel reservation to help you get started. Here are the rules/facts about the business.

I'm suggesting these because of your post count. I really think you should work through this tutorial to get familiar with rules, tables, attributes, relationships and Normalization.

Good luck with your project.