Trying to create an If Statement to look at a date field...

[SilverFang]

I'm creating a hotel database, which is still in its early stages. This hotel's room rates change depending on the time of the year, namely through June-November, and a different rate between December-May.



I have a field called "Season", which I want to display "Off-Peak" or "Super Peak" depending on the date selected in the "Check-in" field. The Season field has a source table, likewise called "Season" (please inform me if I should give it a different name), which has "Off-Peak" in the first row, and "Super Peak" in the second.

I've been trying to enter this (and tweaked versions of it, when it wasn't working) into the Season field's control source:
__________________________________________________ _______
If Month([Check-in date])>=6 AND Month([Check-in date])<12 Then
=Row(2)

ElseIf Month([Check-in date])=12 OR Month([Check-in date])<6 Then
=Row(3)

End If
__________________________________________________ _______

I've also tried adding "Else null" beneath the ElseIf part, and played with the syntax a little in other ways, all to no avail. Access will either tell me I've "entered an operand without an operator", or that I've "entered too many closing parentheses".

I'm hoping someone can help me identify what I'm doing wrong, and what I can do to achieve the result I want.

Thanks in advance.

[June7]

Looks like spreadsheetitis syndrome. Cannot reference table records directly that way.

How many seasons are there? If only 2 why bother with table? Calculate this value in textbox.

=IIf(Month([Check-in date])>=6 AND Month([Check-in date])<12, "Off-Peak", "Super Peak")

Name table Seasons or tblSeasons. Using a prefix that identifies object type is common convention.

[Micron]

I think at least one of those errors is due to a break down in your logic. Carefully read your statement. You should be able to decipher that you are saying
If THIS AND THAT Then Equals SOMETHING

You aren't saying what equals that something.

While this might not be applicable to you, it has been my experience that if my booking spans a time frame that has higher rates, the higher rate applies to that portion of the stay. The inverse has also always been true. If your rate changes will remain constant even though I book the day before a rate change, then bonus for me! Otherwise, your table ought to have start and end dates for rate changes. This way, you'd alter the rates for those nights that fall into their respective rates.

No doubt you would want every night's stay to be a record, thus every record for a my stay would have a nightly rate that is determined by checking which rate range the booking date falls in. Again, speaking from my experience in booking hotels, the rates usually rose on Thursday or Friday and dropped on Sunday or Monday. If your db will only be used by you and you will never operate this way, then by all means, do it by season. If you change your mind later, or are intending to market this db, then I'd advise you to rethink your strategy. To fix the db to follow a fluctuating business model later would probably mean you'd be farther ahead to start over as opposed to fixing what you have.

[SilverFang]

June7: There's a different rate for holiday weeks as well. My intention was to add that in later once I find out which exact dates cover these holidays, feeling I could add it without too much problem once I got the first part done. I just need to find out the correct way to write it. Would it be possible to use the code you provided to cater for three values?

Micron: The form I'm putting these on isn't the final version. You could say I'm using it as practice in preparation for the final one, so I know what techniques to use for it. Yes, the rates for every night booked will be different like you described, but I hadn't gotten that far yet. Any help with achieving this would be appreciated though, seeing as I feel I'll probably struggle with it when I get to it anyhow.

[June7]

Three values isn't too many for an expression but your criteria for determining if date is holiday week might be rather complex. Holiday dates tend to shift. A table may be necessary and use DLookup function to search for matching record.

Possibly should save RateID or the actual rate into each record. The quandary is how to grab that value. User select from combobox or run code to find based on other criteria input by user (dates)?

Hotel booking db has been subject of numerous threads. Some even have files attached. Have you searched forum?

[Micron]

I think you're putting the cart before the horse, so to speak. What would be the point of planning as you build, only to have to trash it all and start over? Database design is like erecting buildings. If you don't have the foundation for 10 floors, you can't get there and would have to redesign and start over. If you're going to learn by doing, at least head in the direction you want to go. Otherwise, you learn, but you don't learn what you need to learn. I get the feeling that you would benefit from researching the basics; especially normalization. Having said that, you'll need some sort of rates table that you can associate prices with rate periods, which could be calendar days, or holiday periods, or seasonal, or special events such as sporting events. Maybe take a look here under hotels and accommodations as a starting point. Let us know if you want links for basics.