Question Use of a calculated field within (ultimately) a report (1 Viewer)

[brumster]

Hi all,

I'm not quite sure how to skin this cat; there are several possible approached mulling over in my head which mean it's hard to narrow it down to a particular area of the forum or a search (I've done some searches on critical keywords but having browsed a few of the umpteen hits, I'm not getting anywhere!).

Here is the issue.

I have a record with a "last reviewed date" on it, plus some numeric fields that basically relate how important the record is (they represent inspection records for health and safety). The higher the priority (a field calculated on the basis of risk and likelyhood), the sooner the next inspection date is due.

The due date for inspection is wholly calculable based on the last inspection date, and the priority factor, therefore I didn't want to represent it as an actual field on the table. This way there is never any honus on the user to enter it (and potential to get it wrong).

Calculating it in a form is pretty simple - I have a function in a module that I can call to calculate the review date. The form view works fine. The function takes a Date and Integer in as parameters (last inspection date, and the priority value) and then returns the result as a Date object too.

The challenge I have is with reports - I want the users to be able to print off a report which represents all the records that are due for review within a certain period (say, the next week or the next month).

So initially I approached this with a simple query, and I planned on basing the report on that query. Initially I had thought something like this would be the approach :-

SELECT tabHSE.id, tabHSE.title, tabHSE.location, tabHSE.inspectionDate, getReviewDate(tabHSE.inspectionDate,riskFactor*likelyhoodFactor) AS calcReviewDate, tabHSE.type, tabHSE.status, tabHSE.riskFactor, tabHSE.LikelyhoodFactor
FROM tabHSE
WHERE calcReviewDate<dateadd("d",7,date());

Running the query just prompts for calcReviewDate to be entered. Without the where clause, the query runs fine, so the function can be called and works okay, populating the field with no problem. I'm sure if we can solve this basic issue, the rest is plain sailing. Anyway, bit confused, I figured for now I'd just repeat the function call, so tried :-

SELECT tabHSE.id, tabHSE.title, tabHSE.location, tabHSE.inspectionDate, getReviewDate(tabHSE.inspectionDate,riskFactor*likelyhoodFactor) AS calcReviewDate, tabHSE.type, tabHSE.status, tabHSE.riskFactor, tabHSE.LikelyhoodFactor
FROM tabHSE
WHERE getReviewDate(tabHSE.inspectionDate,riskFactor*likelyhoodFactor)<dateadd("d",7,date());

...but that results in a Data Type Mismatch in criteria expression. I tried wrapping with hashes but there was no joy there, so further investigation on Google suggested that probably function calls to my getReviewDate() function were not welcome in SQL WHERE clauses.

I next tried to programatically set the recordset on the report itself, but then further googling (I'm boiling this down for you, hopefully to keep it short and sweet!) implied that Reports can't be tied to recordsets, only tables or queries. Since I can't get queries working, the only other approach I can think of is to create a temporary table with the data in it, filled via some module code on a button click to launch the report, and then base the report on that table. I don't particularly like this approach, so just wondered if there was any way I could get my query working how I wanted it to.

Essentially, if I can get my query working with the column "calcReviewDate" then I'm home dry. If this can't be done, it's either an approach done within the report itself by code, or a temporary table.

Anyone done anything similar and can advise?

It's Access 2007, if it helps. I'm not formally trained in the product, but I've mucked around with it over the years - but never faced a challenge like this before

 

[pbaldy]

The first would not work, because the WHERE clause is evaluated before the SELECT clause, thus the alias is not yet known. I would expect the second to work, and I did a brief test to confirm you can use a function in the criteria. My first thought given the error was that the function is not returning a date. What does the function look like?

 

[brumster]

Hi Paul,

Function getReviewDate(lastInspectionDate As Date, priority As Integer) As Date


' Rules for calculating the review date based on the last inspection date and the priority
' if pri = 0 then a last inspection date has not been entered, so let's get it done within the next 7 days
' pri > 16  : review date +7 days
' pri >12 & <=16 : review date +3 months
' pri >9 & <=12 : review date +6 months
' pri <=9 : review date +12 months

Dim retval As Date


If priority <= 0 Or IsNull(lastInspectionDate) Then
    ' Add 7 days to current date
    retval = DateAdd("d", 7, Date)
ElseIf priority > 16 Then
    ' Add 7 days to last inspection date
    retval = DateAdd("d", 7, lastInspectionDate)
ElseIf priority > 12 And priority <= 16 Then
    ' Add 3 months to last inspection date
    retval = DateAdd("m", 3, lastInspectionDate)
ElseIf priority > 9 And priority <= 12 Then
    ' Add 6 months to last inspection date
    retval = DateAdd("m", 6, lastInspectionDate)
Else
    ' Must be less than or equal to 9, so add 12 months to last inspection date
    retval = DateAdd("yyyy", 1, lastInspectionDate)
End If

getReviewDate = retval

End Function

I thought similar, hence I took off the where clause to just see what was being returned, and it showed dates for me. Mind you, maybe a VBA "Date" object and a queries' concept of "Date" are different things?

Thankyou for your time.

Cheers,
Dan

 

[pbaldy]

I did a really brief test with a function that returned a date comparing to a DateAdd() function in a query criteria, and it worked as expected. Is this a db you can post? Or a sample db that has the same problem?

 

[brumster]

I did a little bit of tidying up and tried it as follows :-

SELECT tabHSE.id, tabHSE.title, tabHSE.location, tabHSE.inspectionDate, getReviewDate(tabHSE.inspectionDate,riskFactor*likelyhoodFactor) AS calcReviewDate, tabHSE.type, tabHSE.status, tabHSE.riskFactor, tabHSE.LikelyhoodFactor
FROM tabHSE
WHERE (((getReviewDate([tabHSE].[inspectionDate],[tabHSE].[riskFactor]*[tabHSE].[LikelyhoodFactor]))<DateAdd("d",7,Date())));

...and that works fine! Presumably, then, you have to be very careful to fully specify the table and field values within the square brackets within the function calls?

<slaps head>

As with all these things, it's always something simple, isn't it so sorry to waste your time, but if it wasn't for your questions I wouldn't have stumbled across it!

 

[pbaldy]

Excellent! Glad you got it sorted out, and welcome to the site by the way.

 

[brumster]

Thanks I will try and return the favour and contribute something back to others in the future!