Building a query/form for invoicing. Need help with setting up costs to charge appropriately

[MattRGM]

I'm really not sure how to word what I am looking for into a title.
I am building a database for my parents, they have just bought a campground. I found a good template to start with and have been doing a lot of my own alterations to it. It's still a work in progress of course, you will notice it still has "rooms" rather than "lots". I have managed to enable it to be able to check availability, make bookings, add customers etc. I'm currently working on the invoicing set up.

The problem I am having right now is that, since this is a campground, there is "on" season and "off" season rates. At first I thought it would be simple enough, if the person arrives in the on season then to charge the on season rate. A simple equation. However, I'm not sure what to do about if a person spends a couple of weeks, say one falls within the on season and the second falls in the off season. Is it possible to set it so that it will charge the appropriate rate for the appropriate percentage of the stay? Right now when booking, to make the invoice form simpler I have it so the user manually enters in days/weeks etc. of the stay and the invoice will use that. I don't want to have to make it more complicated by adding to enter on season days stayed and off season days stayed and all that.

I'm still relatively new with access, I just began learning it this year and I'm not very good with using SQL and entering code. I've attached a zipped file of my database for anyone who would be willing to look and give me some advice on how would be best to proceed. What I am working on will do them for now since it is new and there isn't many rentals, but I want to be ready for when it picks up and have something that will be easy for them to use if I am not around. My parents are not very good with computers and so I am basically trying to keep this "idiot proof" as they say when it comes to entering the data.
And so, if anyone has any advice on how to improve on what exists, I would be very thankful for that as well.





Hotel.zip

[ranman256]

I would have a tRates table.
[rate]
[seasonType] = "on" or "Off"
[site]
etc...

I would have a tConfig table (i always do) that tells whats what.
tConfig.Seasontype = "ON"

Then your queries would only pull rates based:
where tConfig.Seasontype = tRates.SeasonType

[SMC]

Addressing specifically only the in/out of season rates question.

There are 6 possible scenarios:
DS = date start; DE = date end; SS = season start; SE = season end

1. DS >= SS and DE < SE
2. DS >= SS and DS < SE and DE >= SE
3. DS < SS and DE >= SS and DE < SE
4. DS < SS and DE >= SE
5. DE < SS
6. DS >= SE

So I just made a series of If statements in a form to solve this problem.
I didn't do a whole lot of testing to make sure everything is 100% correct, post back here if you have problems.

Remember that you can export tables, queries, forms, etc... from one database to another and re-link them if necessary.

Database11.accdb

[MattRGM]

Hi, Sorry it took me so long getting back to you.

I just got the chance to try to take a look at the file you attached but when I try to look at it, it just says that it's an unrecognized database format. I've tried opening it just on its own, compacting it and then unzipping it and even importing it or linking it and get the same result each time.

Thank you for putting that effort into it though, I really appreciate that. I have been working on making a series of if statements for the problem but my brain didn't seem to want to comprehend all of the different scenerios

[SMC]

Oh crap I completely forgot my files are encrypted> Here I will just upload some pictures that you can look at.

(Form)

(RoomRates)

(Season)

Hopefully you can open these images in new tabs. My computer is being funky right now. Anyways, This is the basic set up. The form has three inputs, date start, date end, and type of camping (cabin, tent, or trailer).
The RoomRates table is a reference table that has On and Off rates for all three camping styles just listed.

The fourth box in the form is a computational box. I will paste the code I have written there; The code is written on the drop down menu for selecting the camping style. When the user selects Cabin, Tent, or Trailer the Price box is coded to update with the algorithm.

Private Sub Combo5_AfterUpdate()
          
    If [DateStart] >= DLookup("OnSeasonStart", "Season") And [DateEnd] < DLookup("OnSeasonEnd", "Season") Then
        [Price] = DLookup([RoomType] & "On", "RoomRates") * DateDiff("d", [DateStart], [DateEnd])
    End If
             
    If [DateStart] >= DLookup("OnSeasonStart", "Season") And [DateStart] < DLookup("OnSeasonEnd", "Season") And [DateEnd] >= DLookup("OnSeasonEnd", "Season") Then
        [Price] = DLookup([RoomType] & "On", "RoomRates") * DateDiff("d", [DateStart], DLookup("OnSeasonEnd", "Season")) + DLookup([RoomType] & "Off", "RoomRates") * DateDiff("d", DLookup("OnSeasonEnd", "Season"), [DateEnd])
    End If
    
    If [DateStart] < DLookup("OnSeasonStart", "Season") And [DateEnd] >= DLookup("OnSeasonStart", "Season") And [DateEnd] < DLookup("OnSeasonEnd", "Season") Then
        [Price] = DLookup([RoomType] & "Off", "RoomRates") * DateDiff("d", [DateStart], DLookup("OnSeasonStart", "Season")) + DLookup([RoomType] & "On", "RoomRates") * DateDiff("d", DLookup("OnSeasonStart", "Season"), [DateEnd])
    End If
    If [DateStart] < DLookup("OnSeasonStart", "Season") And [DateEnd] >= DLookup("OnSeasonEnd", "Season") Then
        [Price] = DLookup([RoomType] & "Off", "RoomRates") * DateDiff("d", [DateStart], DLookup("OnSeasonStart", "Season")) + DLookup([RoomType] & "On", "RoomRates") * DateDiff("d", DLookup("OnSeasonStart", "Season"), DLookup("OnSeasonEnd", "Season")) + DLookup([RoomType] & "Off", "RoomRates") * DateDiff("d", DLookup("OnSeasonEnd", "Season"), [DateEnd])
    End If
        
        
    If [DateEnd] < DLookup("OnSeasonStart", "Season") Then
        [Price] = DLookup([RoomType] & "Off", "RoomRates") * DateDiff("d", [DateStart], [DateEnd])
    End If
        
    If [DateStart] >= DLookup("OnSeasonEnd", "Season") Then
        [Price] = DLookup([RoomType] & "Off", "RoomRates") * DateDiff("d", [DateStart], [DateEnd])
    End If
    
End Sub

Hopefully you can figure out the names of all my boxes and stuff, I tried to be as obvious as I could.
"OnSeasonStart" and "OnSeasonEnd" are the dates in the Season table which indicate the start and stop of the season.

[SMC]

Keep in mind this probably isn't the only solution to this question and it most likely isn't the best answer, its just what I came up with, I'm not a very experienced coder.. but I try