Is Access slightly light in the brain dept or me? (1 Viewer)

[Popolou]

Well here it goes. I would be eternally grateful for any insight into where i am going wrong.

Background

I am creating a database for collecting details of commercial properties. The essential data is the Lease start date, Lease length and a review every 5 years into that lease length.

Issue

My trouble is that i have to calculate the lease end date, being a certain number of years after the commencement. Easy yes, but it has to consider the fact that there are reviews on 5 yearly intervals and from this point onwards. So, the database has to calculate the next future review and ignore the previous ones.

Example

Lease start date: 1st August 1990
Lease length: 30 years
Review period: 5 years

So, according to the above, the next review is in 1st August 2005 as the previous reviews (1995 & 2000) have elapsed.

This is the date that i want inserted into a field in the form, when the previous fields (Lease Start Date, Term & Review Period) have been entered.

Efforts to Date

Up till now, i have tried creating a macro with the calculation but i couldnt get all the formulae to work within the expression builder correctly. Therefore, i moved to a module that was called by a macro on an Update/Change/Click event for the input field boxes. This seemed the best possible progress so far.

The calculation for the above is as follows: -

Rent Review Date = (Term - ((Lease Start Date + Term) - Today's Year)) + Lease Start Date

Translating this into a function, i created this: -

--------------------------Start--------------------------
Function RRMacro()
On Error GoTo Err_RRMacro

Dim FullTerm, FullTermStr, RemTermStr, BalanceTermStr, BalanceTerm, NextRrDate
FullTerm = DateAdd("yyyy", [Term], [LeaseStartDate])
FullTermStr = Format(FullTerm, "yyyy")

RemTermStr = FullTermStr - Year(Date)
BalanceTermStr = [Term] - RemTermStr
BalanceTerm = Format(BalanceTermStr, "yyyy")

NextRrDate = BalanceTerm + Year([LeaseStartDate])
[NextReviewDate] = NextRrDate

GoTo Exit_RRMacro

Err_RRMacro:
MsgBox Err.Description
Resume Exit_RRMacro

Exit_RRMacro:
Exit Function

End Function
--------------------------End--------------------------

Observations

So, what i started with was to do a straight forward calculation and provide the result into a field box. The problem i had was the LeaseStartDate (Long Date) was added to the Term (General Number) and this produced odd answers. I soon discovered this was because they were not in the same formats. Access added the term to the date as a number of days and not years. So, i multiplied this by 365, as above.

However, i also realised i needed to convert to strings for the calculation. It started to go wrong from "BalanceTerm=......" where i tried to convert the string back into the date in a year format. Also, with "NextRrDate =......" the resultant calculation became rather messed up.

Basically, it didnt work. The dates it gave where innacurate and this had to be down to the fact that i was working with the years ("yyyy") and cut off the leading day and month.

Result?

So, the question is: how do i carry the calculation as per the formula above using dates throughout? There is something i'm missing but i just cant seem to solve it?

I would be so very grateful for any insight. Apologies for the long winded post.

Thanks again

Popolou

 

[The_Doc_Man]

As to whether it is Access or you that is brain dead (your posted title), I never make snap judgements about fellow Access-fighters. And many times, Access is, indeed, cerebrally deficient. But...

First things first. Look up normalization on the web. A simple-minded Google search on Database Normalization might return a few hits. (A few TENS OF THOUSANDS of hits). Look for any credible university site as a source for the article and have a good read. If the article describes at least the first three forms of normalization, you are probably OK. It could easily go up to five forms, but using them is rare for smaller databases.

OK, for your tables, I sense a normalization issue. You probably want at least two tables to do this; otherwise you could quickly lose possibly important historical data regarding past reviews.

tblProperty
PropID, autonumber (long) or other valid candidate for prime key
PropLoc, text description of property location
other descriptive info as required

tlbPropRevue
PropID, foreign key link to property table
RevueDate, date of last property review
RevueResult, text description of review results
other descriptive info as required

Once you have read up on normalization, you will understand when I say that the property table describes property and the review table describes reviews, and they are like apples and oranges. Which you don't mix. So keep property data with the property table and keep review data with the review table. Kinda repetitious, intended for emphasis.

OK, now you have the potential for a one-to-many relation over PropID with the "one" side being the property and the "many" side being the reviews. THIS is the only way that properties and reviews touch. By having reviews in a separate table, you keep track not only of the current review but also of the historical (superceded) reviews.

Now, look up in the Help files the following items: DateAdd, DateDiff, DateSub, DatePart, Format (with User-Defined Date Formats)

These functions will help you manipulate dates safely. OK, how you work with this structure?

Well, here is a little tidbit for you that will make your life easier and your database less cluttered: Queries work just as well as tables to drive forms or reports. And formulas in queries are perfectly legal. SO, when you want to find out if any properties are up for review, you can write a DateAdd query that compares Now() or Date() to the date of each review on record. You can determine which reviews are overdue, due today, or due within a couple of months just by adjusting the number of months you use in your DateAdd.

You might need to look into making a query that returns properties that have never had a review, or you might take the alternate approach that when the property is originally entered, it is also reviewed for the first time. So that you would NEVER have a property that didn't have at least one review. (The latter approach is easier.)

Playing with raw dates numerically is quite possible if you understand what they are, but using the Datexxxx functions is smoother, more reliable, and a damned sight easier to understand when you have to put the code down for six months to put out some other fire and then have to come back to it.

Finally, an observation. NEVER muck around in macros to do anything like this. Use VBA or put functions in a query. Macros really have very little to offer in the way of error handling and are quite limited in exactly what they can do for you. Further, among all of then methods Access has to skin cats (MEEEEEEOOOOOOOWWWWWRRRR!!!!) , Macros are possibly the LEAST efficient of them all. Macros have their uses, don't get me wrong about that. But their best use is as a sequencer of events handled by other facets of Access, not as a primary actor in a DB modification.

 

[Popolou]

The_Doc_man,

I am much obliged for your time and efforts. I just want to point out that there is alot i hadn't mentionned about the remainder of the database, yet you picked it up rather impressively.

Actually, dont get me wrong. The frontend (and most of the backend) of the Db has already been created and works well. You mentionend a method of removing those entries when aseach has taken place and i implemented this through an update query - something another member has very kindly assisted me with.

Update: Just read up on database normalisation and removed an unneccessary question.

Regarding the VB i posted, I can't determine whether i should change everything into a Date format (and adjust accordingly) or do as i quoted and stick with Strings hoping a fix to a Date format will work? Your opinion?

Actually, splitting the Day & Month as one entry and Year as another may be a quick fix. Certainly not nice, but it would easily work if nothing else.

Thank you again.

Regards

popolou

 

[Rich]

Store your dates as date types, you'll save yourself a great deal of time and hassle in the long run

 

[Popolou]

Yeah, me think thats the best course. Problem is, i dont think i have enough of the skill necessary to get that silly formula licked: -

Rent Review Date = (Term - ((Lease Start Date + Term) - Today's Year)) + Lease Start Date

Ya'know, what a breeze this would have been if it was Excel. For the life of me, i've been on this for the last week and i'm no better off.

Actually i think i'll offer anyone who heps me crack it a free valuation of their resi/comercial premises!

Thanks again.

Regards

Popolou

 

[Rich]

So are you just looking for the next review date based on the last review date and the interval?

 

[Popolou]

I'm after the next review date on supply of the start date, length (in whole years) and review interval. Check out the example i provided in the OP.

The problem is, it has to ignore previous dates and calculate the next according to the review pattern (in the case of the above example, 5-yearly interval) - from today onwards.

To make it easier, the day and month will always stay the same; i am just manipulating the year value to reflect the input values. 5 years from 01/01/2000 will be 01/01/2005. Its as simple as that, yet transfering it into VB is no where near as simple as it is explaining it.

My VB seems alright, but its going crazy when i try and format the string values back into dates. I can understand why, but i just cant reduce the effect so as to produce meaningful results!

Regards

Popolou

 

[Rich]

Interval should be stored as LongIntegers, 5, 10, 15 etc.
Create a totals query, Select Max you review date field, group by propertyID, you now have the last date for each of your inspections.
Create another query with the Totals query and the table containing the interval; add a field to the new query NextDueateAdd("YY",[Interval],[YourMaxedDateField]),

 

[Popolou]

Can this be carried into some sort of VB that updates a field? You see, i have a form that upon entering the data updates the relevant field - in this case, the next review date.

I have enclosed an image with this exactly. The form shows the inputs in the middle, with the outputs to the right, broken down as per the dates we need. The Target Review Date relates to the next review date i've been talking about in the VB. The fields just above and below are easily calculated via a simple macro using the DateAdd function (please ignore the data already entered, its a sample for testing purposes).

Thanks again,

Regards

Popolou

 

[Rich]

You shouldn't store these calculations, they can be calculated at any time via queries or calculated controls on your form, you don't even need vba

 

[Popolou]

Rich,

Can you kindly extend on what you just said?

Update: Just read up on what you said

I am not sure how this works. I tried this via the expression builder (while i still had some hair) as part of my starting efforts but it seemed not sofisticated enough to handle the formula. I will experiment a little more, but i would appreciate any guidance.

Update: Well, i am testing entering the formula in the control source via the EB, but the same issue i encountered first time around is stumping me. Using a simple formula as a test, adding [LeaseStartDate] to [Term] only increases the day and not the year. So, i use [Term]*365 to bring it to a year which seems to work nicely. However, since the number of days will vary from year to year, a period of 25 years later incorporates an increasing error with the calculation.

The problem is clearly the format/type of data - back to my initial problem unfortunately: change all to dates or use strings and format it back. *Sigh*

Regards

Popolou

 

[Rich]

We now have to decide whether your interval is consistent, can it be either yearly, weekly, monthly or even so many days, if that's the case then we're into vba and select case statements, we need more info on what you're actually doing. Are you keeping previous inspection dates for each property for historical purposes?
If one property can have many inspections then you need a separate table to hold them

 

[Popolou]

Hi Rich,

The interval will always be a whole number of years and remains fixed for the whole term length.

Previous dates are not required and the property (building) will have as many inspections as there are review intervals. So, if a term length of 25 years existed on 5-yearly intervals, there would be 4 review dates, with the upcoming one being the Target Review Date.

The Target Reveiw Date (the calculated date in question) is further manipulated via a small SetValue macro which adds/subtracts a number of days to provide the remaining dates (as per the jpeg i attached).

A query runs to select the criteria and the results are sent to Word for a mail merge.

Ultimately, what i said above works fine. The entries update, searched and merged into a standard letter all without issue. Its just that infuriating formula.

Like i said, i am confident the issue remains with the type format of the data. Cleary, adding a Date to a Long Interger is the problem and i cant carry out the calculation from start to end with all fields all set as Dates. It comes out with a nonesense result.

Thanks again for your time and efforts your putting forward.

Regards

Popolou

 

[Rich]

The DateAdd function will do what you want, "yy" adds the number of years you specify, "ww" adds weeks, "m" adds months and "dd" adds days