Module/Query help if ...then...else (1 Viewer)

[tgoodwin]

I am new at Access but have some prior database experience. I am very unfamiliar with Macros and Modules. Pat Hartman gave me a suggestion on the Query forum but I need additional help. I have a Microsoft
Access 97 data base in which I am trying to calculate the occupancy rate of each room of an apartment project.

My thinking was to determine the number of days rented during the report period and divide by the number of days in the report period.

QryOccupancy has the following fields
[DateSold] Date Rented

[NumberofDays] Length of Rental

[EndDate] Calculated field DateAdd(“d”,
[NumberofDays], [DateSold]-1

ReportBegin] Report Beginning Date (Query input)

ReportEnd] Report Ending Date (Query input)

[RptDuration] DateDiff(“d”,[NumberofDays],[DateSold]-1

Pat Hartman suggested creating a function but I can’t get it to work. It doesn’t seem to recognize the field names even when the query name is used - e.g. [QryOccupancy]![DateSold] or [QryOccupancy] . [DateSold]. Do you somehow need to import the query fields into the Module?

Do you you use a DIM statement
dim OccupancyDays as integer?

My If ... then ... else code follows:

If [DateSold] >= [ReportBegin] and [DateSold] <= [ReportEnd] and ([EndDate] >= ReportBegin] and ([EndDate] <= ReportEnd]

then
[OccupancyDays] = [Number of Days]
else if
[DateSold] >= [ReportBegin] and [DateSold] <=[ReportEnd] and([EndDate] >[ReportEnd]

then

[OccupancyDays] = [NumberofDays] - DateDiff(“d”, [EndDate],[ReportEnd]])

else if

[DateSold] <[ReportBegin] and [EndDate] >=[ReportBegin] and ([EndDate] <=[ReportEnd]

then

[OccupancyDays] = [NumberofDays] - DateDiff(“d”,[ReportEnd], [DateSold])

else if

[DateSold] <[ReportBegin] and [EndDate] >[ReportEnd]

then

[OccupancyDays] = [NumberofDays] - (DateDiff(“dd”, [EndDate], [RptEndDate]) +
DateDiff(“dd”, [RptEndDate], [DateSold])
End if

1. The first if statement covers when
the entire rental period is within the report period

2.The second if statement covers when
the rental begin date is within the report period and the rental end date is after the
report end date.

3.The third if statement covers when
the rental begin date is before the report begin date and the rental end date is
within the report period

4.The fourth if statement covers when
The rental begin date is before the report begin date and the rental end date is after
the report end date

I need lots of detailed assistance. Thanking you in advance.

Tom Goodwin

 

[Pat Hartman]

Tom,
I'm in kind of a rush right now so I'm just going to give you a general idea rather than figure out the code.

When you call the function from the query, you need to pass all of the relevant field. For example:
Select [DateSold], [ReportBegin], etc., YourFunction([DateSold], [ReportBegin],etc.) as OccupancyDays
From YourTable
Where ....

Then -

Public Function YourFunction(DateSold as Date,ReportBegin as Date, etc)
calculations
YourFunction = result of occupancy calculation
End Function