# Thread: Dates + Rates

1. Registered User
Join Date
Sep 2003
Location
Morro Bay
Posts
16

## Unanswered: Dates + Rates

I was wondering if someone could help me with some calculations.....

I have a table that has a field on it for rates and a field each for check in and check out dates.

I also have a table that lists the rates provided

Example:

Description________________Rate
First Floor WeekDay ________100.00
First Floor WeekEnd_________110.00
Second Floor WeekDay______120.00

Ect........... Ect........ Ect.......

Im wondering if there is a way to have a query that will, based on the check in and check out dates of one table, determin how many week days Sun - Thurs and how many week end days Fri - Sat that a guest is staying and calculate the apropriate rate.............or if there is an easier way to do this everything I try i just keep getting stuck.

plz help.......and thanks

2. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
does soemthing along these lines fly?

dim intWEdays as integer
dim intWKdays as integer
dim loopDate as date
dim billThis as double

for loopDate = checkInDate to checkOutDate step 1
if weekday(loopdate, vbsunday) > 5 then
intWEdays = intWEdays + 1
else
intWKdays = intWKdays + 1
endif
loop
billThis = intWEdays*WErate + intWKdays*WKrate

maybe you need to adjust the time portion of checkIn/OutDate

izy

3. Registered User
Join Date
Sep 2003
Location
Morro Bay
Posts
16

## Ill check it out

I have to do some work thismorning but I should be able to get some time to work on the database a little later and I will try this as soon as I can and let you know....

Thanks for the help Izy.

Richard

4. Registered User
Join Date
Sep 2003
Location
Morro Bay
Posts
16

## works great

Izy

The code you sent to me works great, however I changed the table layout of my rates, its now a table called Floor with a subdatasheet from a table called Rates (see attached jpg file)

now what Im trying to do is come up with a code that picks the rate out of this table based on the floor that is selected from a dropdown list (named roomnumber) on a form.

I tried to play with it a bit just to get it to change the numbers (not pull them from the table) with code like this:

[vb]
if roomnumber.value = 101 - 110 then
WErate = 100
WDrate = 85
elseif roomnumber.value = 201 - 210 then
WErate = 110
WDrate = 95
elseif roomnumber.value = 301 - 305 then
WErate = 120
WDrate = 105
end if

/some other code

billThis = intWEdays * WErate + intWKdays * WDrate
[/vb]

Any ideas on how i can get this to make the rates change according to floor and pull them from another table?

thanks for everything.....

Richard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•